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

Поиск
Список
Период
Сортировка
От Sanyo Moura
Тема Query with high planning time at version 11.1 compared versions 10.5and 11.0
Дата
Msg-id CAO698qZnrxoZu7MEtfiJmpmUtz3AVYFVnwzR+pqjF=rmKBTgpw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Query with high planning time at version 11.1 compared versions10.5 and 11.0  (Jeff Janes <jeff.janes@gmail.com>)
Re: Query with high planning time at version 11.1 compared versions10.5 and 11.0  (David Rowley <david.rowley@2ndquadrant.com>)
Список 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;


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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: dsa_allocate() faliure
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0