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
I tried setting statistics to 10000 on albaran_entrada_cabecera.time_stamp_recepcion (query 1) and ticket_cabecera.fecha (query 2), query 2 is fixed after analyze with the new statistics target (with 5000 as target is fixed too) but query 1 doesn't improve. 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