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. query 1: http://pastebin.com/32QxbNqW query 1 postgres 9.3 nestloop enabled: http://explain.depesz.com/s/6WX query 1 postgres 8.4: http://explain.depesz.com/s/Q7V query 1 postgres 9.3 nestloop disabled: http://explain.depesz.com/s/w1n query 1 postgres 9.3 changed "having min(ts_recepcion) =" for "where ts_recepcion = " http://explain.depesz.com/s/H5V query 2: http://pastebin.com/JmfPcRg8 query 2 postgres 9.3 nestloop enabled: http://explain.depesz.com/s/EY7 query 2 postgres 8.4: http://explain.depesz.com/s/Xc4 query 2 postgres 9.3 nestloop disabled: http://explain.depesz.com/s/oO6O query 2 postgres 9.3 changed "between" to "equal" for date filter: http://explain.depesz.com/s/cP2H As you can see in this links the problem disappears when I disable nestloop, another thing I discovered making different combinations of changes is that it seems to be related with date/timestamp fields, small changes to the queries fix the problem without disabling nestloop. For example in query 1 changing this: WHERE cab.id_almacen_destino = 109 GROUP BY mo.modelo_id HAVING MIN(cab.time_stamp_recepcion)::date = (current_date - interval '30 days')::date to this: WHERE cab.id_almacen_destino = 109 AND cab.time_stamp_recepcion::date = (current_date - interval '30 days')::date GROUP BY mo.modelo_id in the first subquery fixed the execution time problem, I know the result is not the same, the second change is a better example: In query2 changing this: WHERE fecha BETWEEN '2014-05-19' AND '2014-05-19' to this: WHERE fecha = '2014-05-19' fixes the problem, as you can see in the different explains. This changes are not needed to make PostgreSQL 8.4 take the correct plan but they are in 9.2/9.3, I haven't tried 9.1 or 9.0 yet. Merlin advised me to create a small test case, the thing is that the tables involved can be pretty large. The best way to create a good test case would be to use generate_series or something alike to try to replicate this problem from zero without any dump, no? 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
