performance regression in 9.2/9.3 - Mailing list pgsql-hackers

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 queries
myapplication uses started performing a lot slower, Merlin advised me to try disabling nestloop, this helped out for
theparticular 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')::dateGROUP 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 way
tocreate a good test case would be to use generate_series or something alike to try to replicate this problem from zero
withoutany dump, no?
 


Regards,
Miguel Angel.