On Wed, Aug 5, 2015 at 11:41 AM, Alexandre de Arruda Paes < adald...@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) > > > 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.bo...@gmail.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."
