On 05/06/14 13:32, Linos 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. > > 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. > >
Hi, to put a little more of data on the table, on 9.1 I can reproduce the query 1 problem but not the query 2 problem. 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
