Re: slow select

>
> 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?