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.
