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 Re: Query with high planning time at version 11.1 compared versions10.5 and 11.0 Re: Query with high planning time at version 11.1 compared versions10.5 and 11.0 | 
| Список | 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
В списке pgsql-performance по дате отправления: