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