Re: Unexplainable slow down...

On Thu, 14 Mar 2002, Ron Snyder wrote:

> [snyder(at)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(at)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(at)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(at)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...)