Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 023591337B54 for ; Wed, 19 Jan 2011 05:10:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 89130-03 for ; Wed, 19 Jan 2011 09:10:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smadev.internal.net (host3.dynacom.ondsl.gr [62.103.35.211]) by mail.postgresql.org (Postfix) with ESMTP id CE1E91337B6E for ; Wed, 19 Jan 2011 05:10:07 -0400 (AST) Received: from smadev.internal.net (localhost.internal.net [127.0.0.1]) by smadev.internal.net (8.14.2/8.14.2) with ESMTP id p0J9A5LF092867 for ; Wed, 19 Jan 2011 11:10:05 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Received: from localhost (localhost [[UNIX: localhost]]) by smadev.internal.net (8.14.2/8.14.2/Submit) id p0J9A5Yp092866 for pgsql-performance@postgresql.org; Wed, 19 Jan 2011 11:10:05 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) From: Achilleas Mantzios Organization: Dynacom Tankers Mgmt To: pgsql-performance@postgresql.org Subject: Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2 User-Agent: KMail/1.9.7 References: <955263366.48014741295283147090.JavaMail.root@ucimail4.uci.cu> <201101181006.03790.achill@matrix.gatewaynet.com> <4D35A30D.4030404@vmsinfo.com> In-Reply-To: <4D35A30D.4030404@vmsinfo.com> MIME-Version: 1.0 Content-Disposition: inline Date: Wed, 19 Jan 2011 11:10:05 +0200 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Message-Id: <201101191110.05268.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.451 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_BRBL_LASTEXT=1.449 X-Spam-Level: X-Archive-Number: 201101/160 X-Sequence-Number: 41947 =CE=A3=CF=84=CE=B9=CF=82 Tuesday 18 January 2011 16:26:21 =CE=BF/=CE=B7 Mla= den Gogala =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: > This leads me to the conclusion that the queries differ significantly.=20 > 8.3.3 mentions NOT hashed plan, I don't see it in 9.02 and the filtering= =20 > conditions look differently. Are you sure that the plans are from the=20 > same query? =46irst the num of rows in the two portions are different so you might be c= omparing apples and oranges here. Anyway, i will repost the EXPLAIN plans by copying pasting the query, witho= ut the analyze part. 8.3.13 Unique (cost=3D633677.56..633700.48 rows=3D1834 width=3D23) -> Sort (cost=3D633677.56..633682.14 rows=3D1834 width=3D23) Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)= ), (COALESCE(m.midname, ''::character varying)), m.id -> Hash Join (cost=3D630601.65..633578.15 rows=3D1834 width=3D23) Hash Cond: (ms.vslid =3D vsl.id) -> Hash Join (cost=3D630580.33..633530.01 rows=3D2261 widt= h=3D27) Hash Cond: (ms.marinerid =3D m.id) -> Seq Scan on marinerstates ms (cost=3D0.00..2875.3= 2 rows=3D4599 width=3D8) Filter: (((state)::text =3D 'Active'::text) AND = ((starttime)::date <=3D '2007-01-11'::date) AND ((COALESCE(endtime, now()))= ::date >=3D '2006-07-15'::date)) -> Hash (cost=3D630491.54..630491.54 rows=3D7103 wid= th=3D23) -> Index Scan using mariner_pkey on mariner m = (cost=3D628776.89..630491.54 rows=3D7103 width=3D23) Filter: ((NOT (hashed subplan)) AND ((mari= nertype)::text =3D 'Mariner'::text)) SubPlan -> Unique (cost=3D0.00..628772.30 rows= =3D1834 width=3D4) -> Nested Loop (cost=3D0.00..628= 767.72 rows=3D1834 width=3D4) -> Nested Loop (cost=3D0.0= 0..627027.98 rows=3D1865 width=3D4) -> Index Scan using m= arinerstates_marinerid on marinerstates msold (cost=3D0.00..626316.07 rows= =3D2299 width=3D8) Filter: (((state= )::text =3D 'Active'::text) AND ((starttime)::date <=3D '2007-01-11'::date)= AND ((COALESCE(endtime, now()))::date >=3D '2006-07-15'::date) AND (subpla= n)) SubPlan -> Bitmap Hea= p Scan on marinerstates msold2 (cost=3D4.28..12.11 rows=3D1 width=3D0) Recheck = Cond: ((marinerid =3D $0) AND (starttime < $2)) Filter: = ((id <> $1) AND ((state)::text =3D 'Active'::text) AND (($2 - endtime) <=3D= '1 year 6 mons'::interval)) -> Bitm= ap Index Scan on marinerstates_marinerid_starttime (cost=3D0.00..4.28 rows= =3D2 width=3D0) In= dex Cond: ((marinerid =3D $0) AND (starttime < $2)) -> Index Scan using v= essels_pkey on vessels vslold (cost=3D0.00..0.30 rows=3D1 width=3D4) Index Cond: (vsl= old.id =3D msold.vslid) -> Index Scan using mariner= _pkey on mariner mold (cost=3D0.00..0.92 rows=3D1 width=3D4) Index Cond: (mold.id = =3D msold.marinerid) Filter: ((mold.mariner= type)::text =3D 'Mariner'::text) -> Hash (cost=3D17.81..17.81 rows=3D281 width=3D4) -> Seq Scan on vessels vsl (cost=3D0.00..17.81 rows= =3D281 width=3D4) (31 rows) 9.0.2 Unique (cost=3D11525.09..11571.55 rows=3D3717 width=3D23) -> Sort (cost=3D11525.09..11534.38 rows=3D3717 width=3D23) Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)= ), (COALESCE(m.midname, ''::character varying)), m.id -> Hash Join (cost=3D8281.98..11304.67 rows=3D3717 width=3D23) Hash Cond: (ms.marinerid =3D m.id) -> Hash Join (cost=3D20.12..2963.83 rows=3D3717 width=3D4) Hash Cond: (ms.vslid =3D vsl.id) -> Seq Scan on marinerstates ms (cost=3D0.00..2889.3= 2 rows=3D4590 width=3D8) Filter: (((state)::text =3D 'Active'::text) AND = ((starttime)::date <=3D '2007-01-11'::date) AND ((COALESCE(endtime, now()))= ::date >=3D '2006-07-15'::date)) -> Hash (cost=3D16.72..16.72 rows=3D272 width=3D4) -> Seq Scan on vessels vsl (cost=3D0.00..16.72= rows=3D272 width=3D4) -> Hash (cost=3D8172.57..8172.57 rows=3D7143 width=3D23) -> Seq Scan on mariner m (cost=3D7614.86..8172.57 ro= ws=3D7143 width=3D23) Filter: ((NOT (hashed SubPlan 1)) AND ((marinert= ype)::text =3D 'Mariner'::text)) SubPlan 1 -> Unique (cost=3D2768.00..7614.86 rows=3D1 = width=3D4) -> Nested Loop (cost=3D2768.00..7614.8= 6 rows=3D1 width=3D4) Join Filter: (msold.marinerid =3D = mold.id) -> Index Scan using mariner_pkey = on mariner mold (cost=3D0.00..1728.60 rows=3D14286 width=3D4) Filter: ((marinertype)::text= =3D 'Mariner'::text) -> Materialize (cost=3D2768.00..= 5671.97 rows=3D1 width=3D8) -> Nested Loop (cost=3D276= 8.00..5671.96 rows=3D1 width=3D8) -> Hash Semi Join (c= ost=3D2768.00..5671.67 rows=3D1 width=3D12) Hash Cond: (msol= d.marinerid =3D msold2.marinerid) Join Filter: ((m= sold2.id <> msold.id) AND (msold2.starttime < msold.starttime) AND ((msold.= starttime - msold2.endtime) <=3D '1 year 6 mons'::interval)) -> Seq Scan on = marinerstates msold (cost=3D0.00..2889.32 rows=3D4590 width=3D20) Filter: ((= (state)::text =3D 'Active'::text) AND ((starttime)::date <=3D '2007-01-11':= :date) AND ((COALESCE(endtime, now()))::date >=3D '2006-07-15'::date)) -> Hash (cost= =3D2251.66..2251.66 rows=3D41307 width=3D24) -> Seq Sc= an on marinerstates msold2 (cost=3D0.00..2251.66 rows=3D41307 width=3D24) Filt= er: ((state)::text =3D 'Active'::text) -> Index Scan using v= essels_pkey on vessels vslold (cost=3D0.00..0.28 rows=3D1 width=3D4) Index Cond: (vsl= old.id =3D msold.vslid) (32 rows) >=20 > --=20 > Mladen Gogala=20 > Sr. Oracle DBA > 1500 Broadway > New York, NY 10036 > (212) 329-5251 > www.vmsinfo.com=20 >=20 >=20 =2D-=20 Achilleas Mantzios