On Thu, Jun 5, 2014 at 6:32 AM, Linos <i...@linos.es> wrote: > Hello all, > > This is a continuation of the thread found here: > http://www.postgresql.org/message-id/538f2578.9080...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers