Re: Unexplainable slow down... - Mailing list pgsql-general
From | Stephan Szabo |
---|---|
Subject | Re: Unexplainable slow down... |
Date | |
Msg-id | 20020314145229.B8493-100000@megazone23.bigpanda.com Whole thread Raw |
In response to | Re: Unexplainable slow down... (Ron Snyder <snyder@roguewave.com>) |
Responses |
Closing idle connections
(Steve Lane <slane@fmpro.com>)
|
List | pgsql-general |
On Thu, 14 Mar 2002, Ron Snyder wrote: > [snyder@vault snyder]$ time psql quickview pgsql72 -c "select * from builds > where product='sourcepro_db' and state != 'N' and state != 'W' and finished > >= '03/12/2002' and finished < '03/13/2002' limit 15;" > test.out > > real 1m5.387s > user 0m0.010s > sys 0m0.000s > [snyder@vault snyder]$ time psql quickview pgsql72 -c "set > enable_seqscan=off;select * from builds where product='sourcepro_db' and > state != 'N' and state != 'W' and finished >= '03/12/2002' and finished < > '03/13/2002' limit 15;" > test.out > > real 0m31.689s > user 0m0.000s > sys 0m0.050s > > [snyder@vault snyder]$ psql quickview pgsql72 -c "explain select * from > builds where product='sourcepro_db' and state != 'N' and state != 'W' and > finished >= '03/12/2002' and finished < '03/13/2002' limit 15;" > NOTICE: QUERY PLAN: > > Limit (cost=0.00..14629.38 rows=15 width=427) > -> Seq Scan on builds (cost=0.00..133977.02 rows=137 width=427) > > EXPLAIN > [snyder@vault snyder]$ psql quickview pgsql72 -c "set > enable_seqscan=off;explain select * from builds where product='sourcepro_db' > and state != 'N' and state != 'W' and finished >= '03/12/2002' and finished > < '03/13/2002' limit 15;" > NOTICE: QUERY PLAN: > > Limit (cost=0.00..22801.41 rows=15 width=427) > -> Index Scan using builds_product_state_finished on builds > (cost=0.00..208817.06 rows=137 width=427) > > EXPLAIN > > OK, I'm now more confused. What do I do next to figure out why postgres > isn't choosing the better query? We're running a vacuum analyze every > night-- do I need to tweak the weights so that seq_scan is less likely? Hmm, I'm not sure what the best answer is for this, it's getting beyond my depth. I'd guess that it's possible that it's over estimating the number of reads necessary to do the index scan because the rows are clustered together which would make it over-estimate the index scan cost and/or it could be underestimating the cost of the sequence scan/limit set as well (for example if the rows you want are late in the table it's going to underestimate the final cost I think.) Unfortunately I can't think of a good setting to tweak. You can turn off enable_seqscan for just the one query or chaning random_page_cost in the config, but neither of those are particularly appealing. > Here are the indices (I apologize for the formatting-- is there a different > format you'd prefer?): That was fine... I must have been braindamaged the first time (I didn't follow that builds_product_state_finished meant on those three columns in order...)
pgsql-general by date: