Re: Slow HashAggregate/cache access

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Andreas,

Same plan in 9.5, but the execution time was greater than 9.3 (maybe need some tunning):

postgres@hw-prox01-fac:~/PG95$ /usr/PG95/bin/psql copro95 -p 5444
psql (9.5alpha1)
Type "help" for help.

copro95=# explain (analyze,buffers) SELECT T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0) AS fr13TotQtd,T1.fr13codpr,T1.fr13categ,COALESCE( T5.fr13TotBx, 0) AS fr13TotBx,COALESCE( T4.fr13VrTot, 0) AS fr13VrTot,T2.fr09cod, T3.fr09desc, T1.fr02codigo,T1.fr01codemp FROM((((FR13T T1 LEFT JOIN FR02T T2 ON T2.fr01codemp = T1.fr01codemp AND T2.fr02codigo = T1.fr02codigo)LEFT JOIN FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod = T2.fr09cod) LEFT JOIN (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo, fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND T4.fr13dtlanc = T1.fr13dtlanc) 
LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo, fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND T5.fr13dtlanc = T1.fr13dtlanc) 
WHERE (T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc >= '01/05/2014') AND (T1.fr02codigo >= '0' and T1.fr02codigo <= '9999999999') AND (T1.fr13dtlanc <= '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr, T1.fr13dtlanc;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=30535.97..33949.17 rows=1 width=130) (actual time=623.008..1029.130 rows=2 loops=1)
   Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo = t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
   Rows Removed by Join Filter: 368
   Buffers: shared hit=21362
   ->  Nested Loop Left Join  (cost=30529.83..33941.87 rows=1 width=98) (actual time=622.761..1028.782 rows=2 loops=1)
         Join Filter: (t3.fr01codemp = t1.fr01codemp)
         Buffers: shared hit=21360
         ->  Nested Loop Left Join  (cost=30529.70..33941.71 rows=1 width=87) (actual time=622.709..1028.699 rows=2 loops=1)
               Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND (fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
               Rows Removed by Join Filter: 500202
               Buffers: shared hit=21356
               ->  Nested Loop Left Join  (cost=0.70..2087.56 rows=1 width=23) (actual time=1.021..2.630 rows=2 loops=1)
                     Buffers: shared hit=181
                     ->  Index Scan using ufr13t2 on fr13t t1  (cost=0.42..2083.24 rows=1 width=19) (actual time=0.996..2.576 rows=2 loops=1)
                           Index Cond: ((fr01codemp = '1'::smallint) AND (fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date))
                           Filter: ((fr02codigo >= '0'::numeric) AND (fr02codigo <= '9999999999'::numeric) AND (fr13codpr = 60732))
                           Rows Removed by Filter: 5621
                           Buffers: shared hit=175
                     ->  Index Scan using fr02t_pkey on fr02t t2  (cost=0.28..4.30 rows=1 width=12) (actual time=0.013..0.016 rows=1 loops=2)
                           Index Cond: ((fr01codemp = t1.fr01codemp) AND (fr01codemp = '1'::smallint) AND (fr02codigo = t1.fr02codigo))
                           Buffers: shared hit=6
               ->  HashAggregate  (cost=30529.00..30996.70 rows=31180 width=21) (actual time=286.123..457.848 rows=250102 loops=2)
                     Group Key: fr13t1.fr01codemp, fr13t1.fr02codigo, fr13t1.fr13dtlanc
                     Buffers: shared hit=21175
                     ->  Seq Scan on fr13t1  (cost=0.00..25072.50 rows=311800 width=21) (actual time=0.007..115.766 rows=311800 loops=1)
                           Filter: (fr01codemp = '1'::smallint)
                           Buffers: shared hit=21175
         ->  Index Scan using fr09t_pkey on fr09t t3  (cost=0.14..0.16 rows=1 width=15) (actual time=0.026..0.027 rows=1 loops=2)
               Index Cond: ((fr01codemp = '1'::smallint) AND (fr09cod = t2.fr09cod))
               Buffers: shared hit=4
   ->  HashAggregate  (cost=6.14..6.50 rows=29 width=17) (actual time=0.082..0.128 rows=184 loops=2)
         Group Key: fr13t3.fr01codemp, fr13t3.fr02codigo, fr13t3.fr13dtlanc
         Buffers: shared hit=2
         ->  Seq Scan on fr13t3  (cost=0.00..4.30 rows=184 width=17) (actual time=0.011..0.033 rows=184 loops=1)
               Filter: (fr01codemp = '1'::smallint)
               Buffers: shared hit=2
 Planning time: 2.394 ms
 Execution time: 1038.785 ms
(38 rows)

copro95=# 


2015-08-05 16:55 GMT-03:00 Andreas Joseph Krogh <andreas@xxxxxxxxxx>:
På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk <maxim.boguk@xxxxxxxxx>:
[snip]
 
​I think I know where issue is.
The PostgreSQL planner unable pass join conditions into subquery with aggregate functions (it's well known limitation).
[snip]
 
I'm curious; will 9.5 help here as it has "WHERE clause pushdown in subqueries with window functions"?
 
Are you able to try 9.5 and post the results?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux