> 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

Reply via email to