Re: performance regression in 9.2/9.3

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 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.