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:
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=1371.548..1728.058 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=95 read=21267
-> Nested Loop Left Join (cost=30529.83..33796.84 rows=1 width=98) (actual time=1345.565..1701.990 rows=2 loops=1)
Join Filter: (t3.fr01codemp = t1.fr01codemp)
Buffers: shared hit=95 read=21265
-> Nested Loop Left Join (cost=30529.70..33796.67 rows=1 width=87) (actual time=1340.393..1696.793 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=93 read=21263
-> Nested Loop Left Join (cost=0.70..2098.42 rows=1 width=23) (actual time=36.424..66.841 rows=2 loops=1)
Buffers: shared hit=93 read=88
-> Index Scan using ufr13t2 on fr13t t1 (cost=0.42..2094.11 rows=1 width=19) (actual time=27.518..57.910 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=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
