On Fri, Mar 16, 2012 at 9:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> 2. We assume that if values do exist that they have rows uniformly
>> distributed across the whole table like rungs on a ladder.
>
> Well, yeah. That's sometimes wrong, but not always. In the absence
> of evidence to the contrary, I think it's a better assumption than
> most others.
The evidence I showed proves it is x1000 worse. I'm not sure how you
say it is "better than most" or that there is no evidence.
> You are not the first person to have run into this type of problem.
Yes - if I thought it was an isolated problem I would not have brought it up.
Also, I don't bring it up because I need help; the actual problem has
workarounds. But rewriting SQL because of a planner problem is not
something that highlights our strengths.
> If it were easily solved by some minor hack, we would have done that
> long since. The problem is to not throw the baby out with the
> bathwater.
>
> I can see an argument for downgrading the assumed effectiveness of
> restrictions that are applied as qpquals (ie, not indexquals), but
> the LIMIT node coster doesn't have easy access to the information
> needed for that, especially not in situations more complicated than
> a single-table scan.
My wish was to register this as both a common and significant bug,
whatever the solution is.
The bug appears in very simple queries, so we can't hide behind some
doubt as to the exact cause.
Few planning errors are wrong by more than 1000 times; this plan gets
worse every time the client gains new customers, so its not just bad,
its getting worse. Multiple different queries show the same symptoms,
so its common also.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services