Re: Slow HashAggregate/cache access

Поиск
Список
Период
Сортировка
От Andreas Joseph Krogh
Тема Re: Slow HashAggregate/cache access
Дата
Msg-id VisenaEmail.bc.56541caf34bc5ecd.14f027894da@tc7-visena
обсуждение исходный текст
Ответ на Re: Slow HashAggregate/cache access  (Alexandre de Arruda Paes <adaldeia@gmail.com>)
Ответы Re: Slow HashAggregate/cache access  (David Rowley <david.rowley@2ndquadrant.com>)
Список pgsql-performance
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.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: Alexandre de Arruda Paes
Дата:
Сообщение: Re: Slow HashAggregate/cache access
Следующее
От: David Rowley
Дата:
Сообщение: Re: Slow HashAggregate/cache access