I'm using pg 7.3.4 to do a select involving a join on 2 tables.
The query is taking 15 secs which seems extreme to me considering
the indices that exist on the two tables. EXPLAIN ANALYZE shows
that the indices aren't being used. I've done VACUUM ANALYZE on the
db with no change in results. Shouldn't the indices be used?
Below is what I believe to be the relevant information. I haven't
included the definitions of the tables involved in the foreign
key definititions because I don't think they matter.
Any help will be greatly appreciated.
CREATE TABLE shotpoint (
shot_line_num FLOAT4, \
shotpoint FLOAT4,
x FLOAT4,
y FLOAT4,
template_id INT4,
num_chans INT4)
CREATE TABLE shot_record (
shot_line_num FLOAT4,
shotpoint FLOAT4,
index INT2,
dev INT4,
dev_offset INT8,
bin INT4,
shot_time INT8,
record_length INT4,
nav_x FLOAT4,
nav_y FLOAT4,
num_rus INT4,
status INT4 DEFAULT 0,
reel_num INT4,
file_num INT4,
nav_status INT2,
nav_shot_line FLOAT4,
nav_shotpoint FLOAT4,
nav_depth FLOAT4,
sample_skew INT4,
trace_count INT4,
PRIMARY KEY (shot_line_num, shotpoint, index))
ALTER TABLE shotpoint ADD CONSTRAINT shot_line_fk
FOREIGN KEY (shot_line_num)
REFERENCES shot_line(shot_line_num)
CREATE UNIQUE INDEX shotpoint_idx
ON shotpoint(shot_line_num, shotpoint)
ALTER TABLE shot_record ADD CONSTRAINT shot_record_shotpoint_index_fk
FOREIGN KEY (shot_line_num, shotpoint)
REFERENCES shotpoint(shot_line_num, shotpoint)
EXPLAIN ANALYZE SELECT r.shot_line_num, r.shotpoint, index,
shot_time,
record_length, dev,
dev_offset, num_rus, bin, template_id, trace_count
FROM shot_record r, shotpoint p
WHERE p.shot_line_num = r.shot_line_num
AND p.shotpoint = r.shotpoint;
Merge Join (cost=49902.60..52412.21 rows=100221 width=58) (actual
time=12814.28..15000.65 rows=100425 loops=1)
Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint
= "inner".shotpoint))
-> Sort (cost=13460.90..13711.97 rows=100425 width=46) (actual
time=3856.94..4157.01 rows=100425 loops=1)
Sort Key: r.shot_line_num, r.shotpoint
-> Seq Scan on shot_record r (cost=0.00..2663.25 rows=100425 width=46)
(actual time=18.00..1089.00 rows=100425 loops=1)
-> Sort (cost=36441.70..37166.96 rows=290106 width=12) (actual
time=8957.19..9224.09 rows=100749 loops=1)
Sort Key: p.shot_line_num, p.shotpoint
-> Seq Scan on shotpoint p (cost=0.00..5035.06 rows=290106 width=12)
(actual time=7.55..2440.06 rows=290106 loops=1)
Total runtime: 15212.05 msec
***********************************************************************
Medora Schauer
Sr. Software Engineer
Fairfield Industries
14100 Southwest Freeway
Suite 600
Sugar Land, Tx 77478-3469
USA
[EMAIL PROTECTED]
phone: 281-275-7664
fax : 281-275-7551
***********************************************************************
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
