Re: Slow HashAggregate/cache access

Hi Andreas,

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

postgres(at)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(at)visena(dot)com>:

> På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk <
> maxim(dot)boguk(at)gmail(dot)com>:
>
> [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"?
>
> http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-where-pushdown-with-window-function/
>
> Are you able to try 9.5 and post the results?
>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>