Re: Slow HashAggregate/cache access

On Wed, Aug 5, 2015 at 11:41 AM, Alexandre de Arruda Paes <
adaldeia(at)gmail(dot)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)
>
>
> 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;
>
>

​I think I know where issue is.
The PostgreSQL planner unable pass join conditions into subquery with
aggregate functions (it's well known limitation).

For sample to calculate this part:
​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)
PostgreSQL forced to calculate full aggregate subquery, instead of pass
JOIN conditions into it.

I suggest rewrite query to the following form:
SELECT T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0)
AS fr13TotQtd,T1.fr13codpr,T1.fr13categ,
(SELECT SUM(fr13VrBx) FROM FR13T3 AS T5 WHERE T5.fr01codemp = T1.fr01codemp
AND T5.fr02codigo = T1.fr02codigo AND T5.fr13dtlanc = T1.fr13dtlanc) AS
fr13TotBx,
(SELECT SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot FROM FR13T1 AS T4 WHERE T4.fr01codemp =
T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND T4.fr13dtlanc =
T1.fr13dtlanc) 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
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;

And re-test performance again.

​

--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/>

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."