Re: Odd problem with planner choosing seq scan

Colin McGuigan <cmcguigan(at)earthcomber(dot)com> writes:
> -> Subquery Scan s (cost=0.00..21.93 rows=1 width=8)
> Filter: ((userid = 123456) AND (locationid IS NULL))
> -> Limit (cost=0.00..15.30 rows=530 width=102)
> -> Seq Scan on staff (cost=0.00..15.30 rows=530 width=102)

There does seem to be a bug here, but not the one you think: the rows=1
estimate for the subquery node seems a bit silly given that it knows
there are 530 rows in the underlying query. I'm not sure how bright the
code is about finding stats for variables emitted by a subquery, but
even with totally default estimates it should not come up with a
selectivity of 1/500 for the filter. Unfortunately, fixing that is
likely to bias it further away from the plan you want ...

> Furthermore, I can repeat this experiment over and over, so I know that
> its not caching.

You mean it *is* caching.

> I'd really prefer this query run in < 1 second rather than > 45, but I'd
> really like to do that without having hacks like adding in pointless
> LIMIT clauses.

The right way to do it is to adjust the planner cost parameters.
The standard values of those are set on the assumption of
tables-much-bigger-than-memory, a situation in which the planner's
preferred plan probably would be the best. What you are testing here
is most likely a situation in which the whole of both tables fits in
RAM. If that pretty much describes your production situation too,
then you should decrease seq_page_cost and random_page_cost. I find
setting them both to 0.1 produces estimates that are more nearly in
line with true costs for all-in-RAM situations.

(Pre-8.2, there's no seq_page_cost, so instead set random_page_cost
to 1 and inflate all the cpu_xxx cost constants by 10.)

regards, tom lane