Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZNI5N-0004xs-DV for pgsql-performance@arkaria.postgresql.org; Thu, 06 Aug 2015 10:07:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZNI5M-0006Wo-U9 for pgsql-performance@arkaria.postgresql.org; Thu, 06 Aug 2015 10:07:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZNI3h-0004eJ-PL for pgsql-performance@postgresql.org; Thu, 06 Aug 2015 10:05:57 +0000 Received: from post.visena.com ([46.226.10.50]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZNI3e-0003hV-6P for pgsql-performance@postgresql.org; Thu, 06 Aug 2015 10:05:57 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=dWZnEUI8Lk5iW6op5IwXrHYU/TbNnlgU9IsS5VK8FDo=; b=Qs+dOoa6BRj4lu7u40WeiOmJ7pwbPCYh1fVNaOWnyOU5I9TJEpmKJA+7bWra4LGUD5gbyGa3e64olVDtNMxqPKNIX85Kp8PsKje1J4Mb2deb/1X48UeSYp8UrjvjIV+zxDR90pgslEb5b/rZ1QBpbinbAlGDTZQ1ktwVrx65qiw=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1ZNI3X-0007bH-1c for pgsql-performance@postgresql.org; Thu, 06 Aug 2015 12:05:52 +0200 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1ZNI3W-0004Sz-VX for pgsql-performance@postgresql.org; Thu, 06 Aug 2015 12:05:47 +0200 Date: Thu, 6 Aug 2015 12:05:46 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-performance@postgresql.org Message-ID: In-Reply-To: Subject: Re: Slow HashAggregate/cache access MIME-Version: 1.0 X-Mailer: Visena Mail 2.0.0-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1, HTML_MESSAGE=0.001) X-Pg-Spam-Score: -2.8 (--) Content-Type: multipart/related; boundary="----=_Part_541_423246900.1438855546878" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_540_271044029.1438855546878 Content-Type: multipart/related; boundary="----=_Part_541_423246900.1438855546878" ------=_Part_541_423246900.1438855546878 Content-Type: multipart/alternative; boundary="----=_Part_542_279959280.1438855546902" ------=_Part_542_279959280.1438855546902 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arruda Pae= s < adaldeia@gmail.com >: =C2=A0 Notice the seq scan on t1 instead of the index scan on t1_a_idx. =C2=A0 A way around this is to manually push the predicate down into the subquery: =C2=A0 explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a <= =3D 1=20 group by a) s inner join t2 on t2.a =3D s.a where t2.a <=3D 1; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN ---------------------------------------------------------------------------= ---- =C2=A0Nested Loop =C2=A0(cost=3D0.42..21.98 rows=3D1 width=3D12) =C2=A0 =C2=A0Join Filter: (t1.a =3D t2.a) =C2=A0 =C2=A0-> =C2=A0GroupAggregate =C2=A0(cost=3D0.42..4.46 rows=3D1 widt= h=3D8) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Group Key: t1.a =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using t1_a_idx on t1 = =C2=A0(cost=3D0.42..4.44 rows=3D1 width=3D8) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (a <=3D = 1) =C2=A0 =C2=A0-> =C2=A0Seq Scan on t2 =C2=A0(cost=3D0.00..17.50 rows=3D1 wid= th=3D4) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (a <=3D 1) (8 rows)=C2=A0 =C2=A0 =C2=A0 =C2=A0 Hi David, =C2=A0 You are right. If the subquery includes the same filters of the main select= =20 (of the existing fields, sure), the times down to the floor (50 ms in the f= irst=20 execution and *18* ms by cache. Superb! ): =C2=A0 (...) =C2=A0(SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,= =20 fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS= =20 NUMERIC(18,10))) AS fr13VrTot FROM FR13T1 WHERE (fr01codemp =3D '1' and fr13dtlanc >=3D '01/05/2014') AND= =20 (fr02codigo >=3D '0' and fr02codigo <=3D '9999999999') AND (fr13dtlanc <=3D= =20 '31/05/2014')GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON T4.fr01cod= emp=20 =3D T1.fr01codemp AND T4.fr02codigo =3D T1.fr02codigo AND T4.fr13dtlanc =3D= =20 T1.fr13dtlanc) (...) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------- =C2=A0Nested Loop Left Join =C2=A0(cost=3D5770.32..7894.70 rows=3D1 width= =3D130) (actual=20 time=3D13.715..18.366 rows=3D2 loops=3D1) =C2=A0 =C2=A0Join Filter: ((fr13t3.fr01codemp =3D t1.fr01codemp) AND (fr13t= 3.fr02codigo =3D=20 t1.fr02codigo) AND (fr13t3.fr13dtlanc =3D t1.fr13dtlanc)) =C2=A0 =C2=A0Rows Removed by Join Filter: 368 =C2=A0 =C2=A0Buffers: shared hit=3D5920 =C2=A0 =C2=A0-> =C2=A0Nested Loop Left Join =C2=A0(cost=3D5764.18..7887.47 = rows=3D1 width=3D98) (actual=20 time=3D13.529..18.108 rows=3D2 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Join Filter: (t3.fr01codemp =3D t1.fr01co= demp) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D5918 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop Left Join =C2=A0(cos= t=3D5764.04..7887.30 rows=3D1 width=3D87)=20 (actual time=3D13.519..18.094 rows=3D2 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Join Filter: ((fr13t= 1.fr01codemp =3D t1.fr01codemp) AND=20 (fr13t1.fr02codigo =3D t1.fr02codigo) AND (fr13t1.fr13dtlanc =3D t1.fr13dtl= anc)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Join= Filter: 11144 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D5914 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop= Left Join =C2=A0(cost=3D0.70..2098.42 rows=3D1 width=3D23)=20 (actual time=3D0.796..2.071 rows=3D2 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D181 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Index Scan using ufr13t2 on fr13t t1=20 =C2=A0(cost=3D0.42..2094.11 rows=3D1 width=3D19) (actual time=3D0.787..2.05= 4 rows=3D2 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((fr01codemp =3D 1::smallint) AND=20 (fr13dtlanc >=3D '2014-05-01'::date) AND (fr13dtlanc <=3D '2014-05-31'::dat= e)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Filter: ((fr02codigo >=3D 0::numeric) AND (fr02codi= go=20 <=3D 9999999999::numeric) AND (fr13codpr =3D 60732)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter: 5621 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D175 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Index Scan using fr02t_pkey on fr02t t2=20 =C2=A0(cost=3D0.28..4.30 rows=3D1 width=3D12) (actual time=3D0.005..0.005 r= ows=3D1 loops=3D2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((fr01codemp =3D t1.fr01codemp) AND=20 (fr01codemp =3D 1::smallint) AND (fr02codigo =3D t1.fr02codigo)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D6 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0HashAggrega= te =C2=A0(cost=3D5763.34..5770.15 rows=3D681 width=3D21)=20 (actual time=3D5.576..6.787 rows=3D5573 loops=3D2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D5733 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Index Scan using ufr13t15 on fr13t1=20 =C2=A0(cost=3D0.42..5644.31 rows=3D6802 width=3D21) (actual time=3D0.020..3= .371 rows=3D7053=20 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((fr01codemp =3D 1::smallint) AND=20 (fr13dtlanc >=3D '2014-05-01'::date) AND (fr13dtlanc <=3D '2014-05-31'::dat= e) AND=20 (fr02codigo >=3D 0::numeric) AND (fr02codigo <=3D 9999999999::numeric)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D5733 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using fr09t_pkey on f= r09t t3 =C2=A0(cost=3D0.14..0.16 rows=3D1=20 width=3D15) (actual time=3D0.005..0.005 rows=3D1 loops=3D2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((fr01co= demp =3D 1::smallint) AND (fr09cod =3D=20 t2.fr09cod)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D4 =C2=A0 =C2=A0-> =C2=A0HashAggregate =C2=A0(cost=3D6.14..6.43 rows=3D29 widt= h=3D17) (actual=20 time=3D0.056..0.086 rows=3D184 loops=3D2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on fr13t3 =C2=A0(cost= =3D0.00..4.30 rows=3D184 width=3D17) (actual=20 time=3D0.003..0.027 rows=3D184 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (fr01codemp = =3D 1::smallint) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D2 =C2=A0Total runtime: 18.528 ms (35 rows) =C2=A0 =C2=A0 Tomorrow I will try to do the same with the other slow query, reporting her= e. =C2=A0 It will be interesting to see how Oracle and SQL-Server perform with the=20 re-written query too. Thanks. =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_542_279959280.1438855546902 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arrud= a Paes <adaldeia@gmail.com>= :
=C2=A0
Notice the seq scan on t1 instead of the index scan on t1_a_idx.
=C2=A0
A way around this is to manually push the predicate down into the subq= uery:
=C2=A0
explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1= .a <=3D 1 group by a) s inner join t2 on t2.a =3D s.a where t2.a <=3D= 1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
----------------------------------------------------------------------= ---------
=C2=A0Nested Loop =C2=A0(cost=3D0.42..21.98 rows=3D1 width=3D12)
=C2=A0 =C2=A0Join Filter: (t1.a =3D t2.a)
=C2=A0 =C2=A0-> =C2=A0GroupAggregate =C2=A0(cost=3D0.42..4.46 rows= =3D1 width=3D8)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Group Key: t1.a
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using t1_a_id= x on t1 =C2=A0(cost=3D0.42..4.44 rows=3D1 width=3D8)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (a = <=3D 1)
=C2=A0 =C2=A0-> =C2=A0Seq Scan on t2 =C2=A0(cost=3D0.00..17.50 rows= =3D1 width=3D4)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (a <=3D 1)
(8 rows)=C2=A0
=C2=A0
=C2=A0
=C2=A0
Hi David,
=C2=A0
You are right. If the subquery includes the same filters of the main s= elect (of the existing fields, sure), the times down to the floor (50 ms in= the first execution and *18* ms by cache. Superb! ):
=C2=A0
(...) =C2=A0(SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codi= go, fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) = AS NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 WHERE (fr01codemp =3D '1' and fr13dtlanc >=3D '01/05= /2014') AND (fr02codigo >=3D '0' and fr02codigo <=3D '9999999999') AN= D (fr13dtlanc <=3D '31/05/2014') GROUP BY fr01codemp, fr02codigo, fr= 13dtlanc ) T4 ON T4.fr01codemp =3D T1.fr01codemp AND T4.fr02codigo =3D T1.f= r02codigo AND T4.fr13dtlanc =3D T1.fr13dtlanc)
(...)
=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QU= ERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0
----------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------
=C2=A0Nested Loop Left Join =C2=A0(cost=3D5770.32..7894.70 rows=3D1 wi= dth=3D130) (actual time=3D13.715..18.366 rows=3D2 loops=3D1)
=C2=A0 =C2=A0Join Filter: ((fr13t3.fr01codemp =3D t1.fr01codemp) AND (= fr13t3.fr02codigo =3D t1.fr02codigo) AND (fr13t3.fr13dtlanc =3D t1.fr13dtla= nc))
=C2=A0 =C2=A0Rows Removed by Join Filter: 368
=C2=A0 =C2=A0Buffers: shared hit=3D5920
=C2=A0 =C2=A0-> =C2=A0Nested Loop Left Join =C2=A0(cost=3D5764.18..= 7887.47 rows=3D1 width=3D98) (actual time=3D13.529..18.108 rows=3D2 loops= =3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Join Filter: (t3.fr01codemp =3D t1.f= r01codemp)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D5918
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop Left Join = =C2=A0(cost=3D5764.04..7887.30 rows=3D1 width=3D87) (actual time=3D13.519..= 18.094 rows=3D2 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Join Filter: ((= fr13t1.fr01codemp =3D t1.fr01codemp) AND (fr13t1.fr02codigo =3D t1.fr02codi= go) AND (fr13t1.fr13dtlanc =3D t1.fr13dtlanc))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by= Join Filter: 11144
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared= hit=3D5914
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nes= ted Loop Left Join =C2=A0(cost=3D0.70..2098.42 rows=3D1 width=3D23) (actual= time=3D0.796..2.071 rows=3D2 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Buffers: shared hit=3D181
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0Index Scan using ufr13t2 on fr13t t1 =C2=A0(cost=3D0.42..= 2094.11 rows=3D1 width=3D19) (actual time=3D0.787..2.054 rows=3D2 loops=3D1= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((fr01codemp =3D 1::smallint) AND (f= r13dtlanc >=3D '2014-05-01'::date) AND (fr13dtlanc <=3D '2014-05-31':= :date))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: ((fr02codigo >=3D 0::numeric) AND (fr= 02codigo <=3D 9999999999::numeric) AND (fr13codpr =3D 60732))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter: 5621
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D175
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0Index Scan using fr02t_pkey on fr02t t2 =C2=A0(cost=3D0.2= 8..4.30 rows=3D1 width=3D12) (actual time=3D0.005..0.005 rows=3D1 loops=3D2= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((fr01codemp =3D t1.fr01codemp) AND = (fr01codemp =3D 1::smallint) AND (fr02codigo =3D t1.fr02codigo))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D6
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Has= hAggregate =C2=A0(cost=3D5763.34..5770.15 rows=3D681 width=3D21) (actual ti= me=3D5.576..6.787 rows=3D5573 loops=3D2)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Buffers: shared hit=3D5733
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0Index Scan using ufr13t15 on fr13t1 =C2=A0(cost=3D0.42..5= 644.31 rows=3D6802 width=3D21) (actual time=3D0.020..3.371 rows=3D7053 loop= s=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((fr01codemp =3D 1::smallint) AND (f= r13dtlanc >=3D '2014-05-01'::date) AND (fr13dtlanc <=3D '2014-05-31':= :date) AND (fr02codigo >=3D 0::numeric) AND (fr02codigo <=3D 99999999= 99::numeric))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D5733
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using fr09t_p= key on fr09t t3 =C2=A0(cost=3D0.14..0.16 rows=3D1 width=3D15) (actual time= =3D0.005..0.005 rows=3D1 loops=3D2)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((f= r01codemp =3D 1::smallint) AND (fr09cod =3D t2.fr09cod))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared= hit=3D4
=C2=A0 =C2=A0-> =C2=A0HashAggregate =C2=A0(cost=3D6.14..6.43 rows= =3D29 width=3D17) (actual time=3D0.056..0.086 rows=3D184 loops=3D2)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D2
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on fr13t3 =C2= =A0(cost=3D0.00..4.30 rows=3D184 width=3D17) (actual time=3D0.003..0.027 ro= ws=3D184 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (fr01co= demp =3D 1::smallint)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared= hit=3D2
=C2=A0Total runtime: 18.528 ms
(35 rows)
=C2=A0
=C2=A0
Tomorrow I will try to do the same with the other slow query, reportin= g here.
=C2=A0
It will be interesting to see how Oracle and SQL-Server perform with t= he re-written query too.
Thanks.
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_542_279959280.1438855546902-- ------=_Part_541_423246900.1438855546878 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_541_423246900.1438855546878-- ------=_Part_540_271044029.1438855546878--