Skip site navigation (1) Skip section navigation (2)

Re: slow select

From: "Medora Schauer" <mschauer(at)fairfield(dot)com>
To: "Josh Berkus" <josh(at)agliodbs(dot)com>,"postgresql" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow select
Date: 2003-10-22 20:56:33
Message-ID: 906E2C446A276048A1BE283F17BCB12CDB422A@encounter.fairind.fairfield.com (view raw or flat)
Thread:
Lists: pgsql-performance

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



Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-10-22 21:03:12
Subject: Re: slow select
Previous:From: Vivek KheraDate: 2003-10-22 18:46:26
Subject: Re: vacuum locking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group