Re: performance regression in 9.2/9.3
От | Linos |
---|---|
Тема | Re: performance regression in 9.2/9.3 |
Дата | |
Msg-id | 53906D6F.2010009@linos.es обсуждение исходный текст |
Ответ на | performance regression in 9.2/9.3 (Linos <info@linos.es>) |
Список | pgsql-hackers |
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.9080001@linos.es > > Considering this seems to be a problem with the planner I thought that maybe would be a better idea to post this problemhere. > > To summarize the original thread I upgraded a medium (17Gb) database from PostgreSQL 8.4 to 9.3 and many of the queriesmy application uses started performing a lot slower, Merlin advised me to try disabling nestloop, this helped outfor 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 differentcombinations of changes is that it seems to be related with date/timestamp fields, small changes to the queriesfix 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 betterexample: > > 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 or9.0 yet. > > Merlin advised me to create a small test case, the thing is that the tables involved can be pretty large. The best wayto create a good test case would be to use generate_series or something alike to try to replicate this problem from zerowithout 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.
В списке pgsql-hackers по дате отправления:
Следующее
От: Heikki LinnakangasДата:
Сообщение: Re: Could not finish anti-wraparound VACUUM when stop limit is reached