On 05/06/14 16:40, Merlin Moncure wrote: > 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 > >
What I don't understand is why the statistics have this bad information, all my tests are done on a database just restored and analyzed. Can I do something to improve the quality of my database statistics and let the planner do better choices? Maybe increase the statistics target of the columns involved? Regards, Miguel Angel. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
