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