Thread: Slow HashAggregate/cache access

Hi,

First, sorry to compare Post with other database system, but I know nothing about Oracle...

This customer have an application made with a framework thats generates the SQL statements (so, We can't make any query optimizations) .

We did the following tests:

1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA disk,Core i5)

2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, SAS disks)

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

 

På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk <maxim.boguk@gmail.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/

 

On Wed, Aug 5, 2015 at 11:41 AM, Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:

Hi,

First, sorry to compare Post with other database system, but I know nothing about Oracle...

This customer have an application made with a framework thats generates the SQL statements (so, We can't make any query optimizations) .

We did the following tests:

1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA disk,Core i5)

2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, SAS disks)

​I think I know where issue is.

The PostgreSQL planner unable pass join conditions into subquery with aggregate functions (it's well known limitation).

The query in question is likely performing badly because of this:

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

Just how selective is fr01codemp = '1'::smallint ? Is there an index on that column ?

Notice the seq scan on t1 instead of the index scan on t1_a_idx.

A way around this is to manually push the predicate down into the subquery:

explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a <= 1 group by a) s inner join t2 on t2.a = s.a where t2.a <= 1;

                                  QUERY PLAN

-------------------------------------------------------------------------------

 Nested Loop  (cost=0.42..21.98 rows=1 width=12)

   Join Filter: (t1.a = t2.a)

   ->  GroupAggregate  (cost=0.42..4.46 rows=1 width=8)

         Group Key: t1.a

         ->  Index Scan using t1_a_idx on t1  (cost=0.42..4.44 rows=1 width=8)

               Index Cond: (a <= 1)

   ->  Seq Scan on t2  (cost=0.00..17.50 rows=1 width=4)

         Filter: (a <= 1)

(8 rows) 

På torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arruda Paes <adaldeia@gmail.com>:

 

Notice the seq scan on t1 instead of the index scan on t1_a_idx.

 

A way around this is to manually push the predicate down into the subquery:

 

explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a <= 1 group by a) s inner join t2 on t2.a = s.a where t2.a <= 1;

                                  QUERY PLAN

-------------------------------------------------------------------------------

 Nested Loop  (cost=0.42..21.98 rows=1 width=12)

   Join Filter: (t1.a = t2.a)

   ->  GroupAggregate  (cost=0.42..4.46 rows=1 width=8)

         Group Key: t1.a

         ->  Index Scan using t1_a_idx on t1  (cost=0.42..4.44 rows=1 width=8)

               Index Cond: (a <= 1)

   ->  Seq Scan on t2  (cost=0.00..17.50 rows=1 width=4)

         Filter: (a <= 1)

(8 rows) 

 

 

 

Hi David,

 

You are right. If the subquery includes the same filters of the main select (of the existing fields, sure), the times down to the floor (50 ms in the first execution and *18* ms by cache. Superb! ):

 

(...)  (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo, fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS NUMERIC(18,10))) AS fr13VrTot

FROM FR13T1 WHERE (fr01codemp = '1' and fr13dtlanc >= '01/05/2014') AND (fr02codigo >= '0' and fr02codigo <= '9999999999') AND (fr13dtlanc <= '31/05/2014') GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND T4.fr13dtlanc = T1.fr13dtlanc)

(...)

 

                                                                                                       QUERY PLAN                                                                                                        

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Nested Loop Left Join  (cost=5770.32..7894.70 rows=1 width=130) (actual time=13.715..18.366 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=5920

   ->  Nested Loop Left Join  (cost=5764.18..7887.47 rows=1 width=98) (actual time=13.529..18.108 rows=2 loops=1)

         Join Filter: (t3.fr01codemp = t1.fr01codemp)

         Buffers: shared hit=5918

         ->  Nested Loop Left Join  (cost=5764.04..7887.30 rows=1 width=87) (actual time=13.519..18.094 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: 11144

               Buffers: shared hit=5914

               ->  Nested Loop Left Join  (cost=0.70..2098.42 rows=1 width=23) (actual time=0.796..2.071 rows=2 loops=1)

                     Buffers: shared hit=181

                     ->  Index Scan using ufr13t2 on fr13t t1  (cost=0.42..2094.11 rows=1 width=19) (actual time=0.787..2.054 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.005..0.005 rows=1 loops=2)

                           Index Cond: ((fr01codemp = t1.fr01codemp) AND (fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))

                           Buffers: shared hit=6

               ->  HashAggregate  (cost=5763.34..5770.15 rows=681 width=21) (actual time=5.576..6.787 rows=5573 loops=2)

                     Buffers: shared hit=5733

                     ->  Index Scan using ufr13t15 on fr13t1  (cost=0.42..5644.31 rows=6802 width=21) (actual time=0.020..3.371 rows=7053 loops=1)

                           Index Cond: ((fr01codemp = 1::smallint) AND (fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date) AND (fr02codigo >= 0::numeric) AND (fr02codigo <= 9999999999::numeric))

                           Buffers: shared hit=5733

         ->  Index Scan using fr09t_pkey on fr09t t3  (cost=0.14..0.16 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=2)

               Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod = t2.fr09cod))

               Buffers: shared hit=4

   ->  HashAggregate  (cost=6.14..6.43 rows=29 width=17) (actual time=0.056..0.086 rows=184 loops=2)

         Buffers: shared hit=2

         ->  Seq Scan on fr13t3  (cost=0.00..4.30 rows=184 width=17) (actual time=0.003..0.027 rows=184 loops=1)

               Filter: (fr01codemp = 1::smallint)

               Buffers: shared hit=2

 Total runtime: 18.528 ms

(35 rows)

 

 

Tomorrow I will try to do the same with the other slow query, reporting here.

 

It will be interesting to see how Oracle and SQL-Server perform with the re-written query too.

Thanks.