>
> 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.31rows=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.64rows=100749 loops=1)
Total runtime: 6086.32 msec
So why did were the indices not used before when they yield a better plan?