| 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: | Whole Thread | Raw Message | 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 |