Re: performance regression in 9.2/9.3

On Thu, Jun 5, 2014 at 6:32 AM, Linos <info(at)linos(dot)es> wrote:
> Hello all,
>
> This is a continuation of the thread found here:
> http://www.postgresql.org/message-id/538F2578.9080001@linos.es
>
> Considering this seems to be a problem with the planner I thought that maybe would be a better idea to post this problem here.
>
> To summarize the original thread I upgraded a medium (17Gb) database from PostgreSQL 8.4 to 9.3 and many of the queries my application uses started performing a lot slower, Merlin advised me to try disabling nestloop, this helped out for the particular query I was asking about but it is not a solution that I "can/would like" to use in the general case.
>
> I simplified a little bit the original query and I have added another one with same problem.

I believe the basic problem (this is just one example; I've
anecdotally seen this myself) is that changes in the query planner
(which I don't follow and fully understand) in recent versions seem to
be such that the planner makes better decisions in the presence of
good information but in certain cases makes worse choices when dealing
with bad information. Statistics errors tend to accumulate and
magnify in complicated plans, especially when the SQL is not optimally
written.

I have no clue what the right solution is. There's been several
discussions about 'plan risk' and trying to get the server to pick
plans with better worse case behavior in cases where statistics are
demonstrably suspicious. Maybe that would work but ISTM is a huge
research item that won't get solved quickly or even necessarily pan
out in the end. Nevertheless, user supplied test cases demonstrating
performance regressions (bonus if it can be scripted out of
generate_series) are going to be key drivers in finding a solution.

merlin