Query with high planning time at version 11.1 compared versions 10.5 and 11.0

From: Sanyo Moura <sanyo(dot)moura(at)tatic(dot)net>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Date: 2018-11-27 14:16:41
Message-ID: CAO698qZnrxoZu7MEtfiJmpmUtz3AVYFVnwzR+pqjF=rmKBTgpw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Hi,

I'm running performance tests for my application at version 11.1 and
encountered
queries with high planning time compared to the same planning, running at
versions 10.5 and 11.0.

-- Day and store where the highest price variation of a given product
occurred in a given period
explain analyze select l_variacao.fecha, l_variacao.loccd as "Almacen",
l_variacao.pant as "Precio anterior", l_variacao.patual as "Precio atual",
max_variacao.var_max as "Variación máxima (Agua)"
from (select p.fecha, p.loccd, p.plusalesprice patual, da.plusalesprice
pant, abs(p.plusalesprice - da.plusalesprice) as var
from precio p, (select p.fecha, p.plusalesprice, p.loccd
from precio p
where p.fecha between '2017-03-01' and '2017-03-02' and p.pluid = 2) da

where p.fecha between '2017-03-01' and '2017-03-02' and p.pluid = 2
and p.loccd = da.loccd and p.fecha = da.fecha + 1) l_variacao,
(select max(abs(p.plusalesprice - da.plusalesprice)) as var_max
from precio p, (select p.fecha, p.plusalesprice, p.loccd
from precio p
where p.fecha between '2017-03-01' and '2017-03-02' and p.pluid = 2)
da
where p.fecha between '2017-03-01' and '2017-03-02' and p.pluid = 2
and p.loccd = da.loccd and p.fecha = da.fecha + 1) max_variacao
where max_variacao.var_max = l_variacao.var;

Formatted explain: https://explain.depesz.com/s/mUkP

And below are the times generated by EXPLAIN ANALYZE:

10.5
Planning time: 126.080 ms
Execution time: 2.306 ms

11.0
Planning Time: 7.238 ms
Planning Time: 2.638 ms

11.5
Planning Time: 15138.533 ms
Execution Time: 2.310 ms

All 3 EXPLAIN show exactly the same plan, but version 11.1 is consuming
about 15s more to
perform the planning.

Below are some additional OS information:
CPU: 16
RAM: 128GB
Disk: SSD
OS: CentOS Linux release 7.5.1804

Is there any configuration I have to do in 11.1 to achieve the same
planning performance
as in previous versions?

Regards,

Sanyo Capobiango

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2018-11-27 14:21:39 pgsql: Update ssl test certificates and keys
Previous Message Sergei Kornilov 2018-11-27 14:16:20 Re: pgsql: Integrate recovery.conf into postgresql.conf

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2018-11-27 15:00:08 Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Previous Message Thomas Munro 2018-11-27 08:02:29 Re: dsa_allocate() faliure