> > Medora, > > > 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? > > No. You're selecting 100,000 records. For such a large > record dump, a seq > scan is usually faster. > > If you don't believe me, try setting enable_seqscan=false and > see how long the > query takes.
I did as you suggested (set enable_seqscan = false) and the query now takes 6 sec (vs 15 secs before) : Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63 rows=100425 loops=1) Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint = "inner".shotpoint)) -> Index Scan using hsot_record_idx on shot_record r (cost=0.00..123080.11 rows=100425 width=46) (actual time=24.15..2710.31 rows=100425 loops=1) -> Index Scan using shotpoint_idx on shotpoint p (cost=0.00..467924.54 rows=290106 width=12) (actual time=37.38..1379.64 rows=100749 loops=1) Total runtime: 6086.32 msec So why did were the indices not used before when they yield a better plan? ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly