Quick Links

Slow HashAggregate/cache access

From:	Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com>
To:	pgsql-performance(at)postgresql(dot)org
Subject:	Slow HashAggregate/cache access
Date:	2015-08-05 01:41:13
Message-ID:	CAGewt-tbqRW5NLAzKDCvP_ztEN_LMMyGugQ1iVVEzB+p2XpefQ@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-performance

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)

In the first machine, postgresql takes from 20,000 to 40,000 ms to complete
the query and from 1,200 to 2,000 ms in the others runs. Oracle in this
machine takes 2,000ms in the first run and *70ms* using cache.

In the second machine, postgresql takes about 2,000ms in the first run and
about 800ms in the others. 11x slow than Oracle times, in a much more
powefull machine.

Bellow is the 2 explains in the second server:

Filter: ((fr02codigo >= 0::numeric) AND
(fr02codigo <= 9999999999::numeric) AND (fr13codpr = 60732))

Rows Removed by Filter: 5621

Buffers: shared hit=90 read=85

-> Index Scan using fr02t_pkey on fr02t t2
(cost=0.28..4.30 rows=1 width=12) (actual time=4.455..4.458 rows=1 loops=2)
Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
Buffers: shared hit=3 read=3
-> HashAggregate (cost=30529.00..30840.80 rows=31180
width=21) (actual time=630.594..753.406 rows=250102 loops=2)
Buffers: shared read=21175
-> Seq Scan on fr13t1 (cost=0.00..25072.50
rows=311800 width=21) (actual time=6.354..720.037 rows=311800 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared read=21175
-> Index Scan using fr09t_pkey on fr09t t3 (cost=0.14..0.16
rows=1 width=15) (actual time=2.584..2.586 rows=1 loops=2)
Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
t2.fr09cod))
Buffers: shared hit=2 read=2
-> HashAggregate (cost=6.14..6.43 rows=29 width=17) (actual
time=12.906..12.972 rows=184 loops=2)
Buffers: shared read=2
-> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17)
(actual time=25.570..25.624 rows=184 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared read=2
Total runtime: 1733.320 ms
(35 rows)

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;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=30535.97..33804.07 rows=1 width=130) (actual
time=492.669..763.313 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..33796.84 rows=1 width=98)
(actual time=492.462..763.015 rows=2 loops=1)
Join Filter: (t3.fr01codemp = t1.fr01codemp)
Buffers: shared hit=21360
-> Nested Loop Left Join (cost=30529.70..33796.67 rows=1
width=87) (actual time=492.423..762.939 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..2098.42 rows=1
width=23) (actual time=0.855..2.268 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.844..2.229 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.009..0.012 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..30840.80 rows=31180
width=21) (actual time=229.435..325.660 rows=250102 loops=2)
Buffers: shared hit=21175
-> Seq Scan on fr13t1 (cost=0.00..25072.50
rows=311800 width=21) (actual time=0.003..74.088 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.023..0.024 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.065..0.098 rows=184 loops=2)
Buffers: shared hit=2
-> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17)
(actual time=0.006..0.029 rows=184 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared hit=2
Total runtime: 763.536 ms
(35 rows)

Thanks for any help.

Best regards,

Alexandre

Responses

Re: Slow HashAggregate/cache access at 2015-08-05 17:24:48 from Kevin Grittner
Re: Slow HashAggregate/cache access at 2015-08-05 18:25:25 from Maxim Boguk

Browse pgsql-performance by date

	From	Date	Subject
Next Message	Ram N	2015-08-05 03:40:18	Re: Performance issue with NestedLoop query
Previous Message	Qingqing Zhou	2015-07-31 18:37:41	Re: Performance issue with NestedLoop query