Re: Slow HashAggregate/cache access

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