From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow HashAggregate/cache access
Date: Thu, 6 Aug 2015 12:05:46 +0200 (CEST)
Message-ID: <VisenaEmail.bc.56541caf34bc5ecd.14f027894da@tc7-visena> (raw)
In-Reply-To: <CAGewt-sAig4vN9azcYPVCTrLQFA1UBry7KDA1ZZGHG+M6wD+sg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arruda Paes <
adaldeia@gmail.com <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@visena.com
Subject: Re: Slow HashAggregate/cache access
In-Reply-To: <VisenaEmail.bc.56541caf34bc5ecd.14f027894da@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox