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
