Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id CA1D81337B43 for ; Tue, 18 Jan 2011 04:06:14 -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 27897-05 for ; Tue, 18 Jan 2011 08:06:06 +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 6CE661337999 for ; Tue, 18 Jan 2011 04:06:04 -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 p0I864Xn074948 for ; Tue, 18 Jan 2011 10:06:04 +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 p0I863CI074947 for pgsql-performance@postgresql.org; Tue, 18 Jan 2011 10:06:03 +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 Date: Tue, 18 Jan 2011 10:06:03 +0200 User-Agent: KMail/1.9.7 References: <955263366.48014741295283147090.JavaMail.root@ucimail4.uci.cu> In-Reply-To: <955263366.48014741295283147090.JavaMail.root@ucimail4.uci.cu> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <201101181006.03790.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/145 X-Sequence-Number: 41932 Thanx, =CE=A3=CF=84=CE=B9=CF=82 Monday 17 January 2011 18:52:27 =CE=BF/=CE=B7 Ing.= Marcos Ortiz Valmaseda =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: >=20 > Well, on the Release Notes on the PostgreSQL-8.4 Documentation, the devel= opers recommend to use NOT EXISTS=20 > instead NOT IN, because the first clause has a better performance. So, yo= u can use it on that way. >=20 You mean this? (from 8.4 changes) "Create explicit concepts of semi-joins and anti-joins (Tom) This work formalizes our previous ad-hoc treatment of IN (SELECT ...) clauses, and extends it to EXISTS and NOT EXISTS clauses. It should result in significantly better planning of EXISTS and NOT EXISTS queries. In general, logically equivalent IN and EXISTS clauses should now have similar performance, whereas previously IN often won." I haven't found any other recent reference to this issue. And this is far f= rom what you suggest. Here the entry talks about "similar" performance. Also a similar issue was hot back in 7.4 days : "IN / NOT IN subqueries are now much more efficient In previous releases, IN/NOT IN subqueries were joined to the upper query by sequentially scanning the subquery looking for a match. The 7.4 code uses the same sophisticated techniques used by ordinary joins and so is much faster. An IN will now usually be as fast as or faster than an equivalent EXISTS subquery; this reverses the conventional wisdom that applied to previous releases." > Other questions? > - Do you have a partial index on marinerstates.marinerid where this condi= tion is accomplished? No, but i just tried it (on state=3D'Active') with no impact.=20 > - Do you have a index on mariner.id? Yes, It is the primary key. > - Can you provide a explain of these queries on the PostgreSQL-9.0 machin= es? Sure, first i'll post the table definitions and then some stats and then th= e epxlain analyze(s) mariner =3D=3D=3D=3D=3D id | integer | not null default= nextval(('public.mariner_id_seq'::text)::regclass) givenname | character varying(200) | midname | character varying(100) | surname | character varying(200) | not null =2E.. Indexes: "mariner_pkey" PRIMARY KEY, btree (id) "mariner_smauid" UNIQUE, btree (smauid) "mariner_username_key" UNIQUE, btree (username) "mariner_nationalityid" btree (nationalityid) "mariner_parentid" btree (parentid) "mariner_surname" btree (surname) marinerstates =3D=3D=3D=3D=3D=3D=3D=3D id | integer | not null default nextv= al(('public.marinerstates_id_seq'::text)::regclass) marinerid | integer | not null state | character varying(20) | not null vslid | integer | leave_period_days | integer | comment | text | starttime | timestamp with time zone | not null endtime | timestamp with time zone | trid | integer | sal_bw | real | not null default 0.0 sal_ot | real | not null default 0.0 sal_lp | real | not null default 0.0 sal_misc | real | not null default 0.0 rankid | integer | system_vslid | integer | startport | text | endport | text | =2E.... Indexes: "marinerstates_pkey" PRIMARY KEY, btree (id) "marinerstates_mariner_cur_state" UNIQUE, btree (marinerid) WHERE endti= me IS NULL "marinerstates_system_vslid" UNIQUE, btree (marinerid, system_vslid) "marinerstates__system_vslid" btree (system_vslid) "marinerstates_cur_mariners_states" btree (endtime) WHERE endtime IS NU= LL "marinerstates_mariner_past_state" btree (marinerid, starttime, endtime= ) WHERE endtime IS NOT NULL "marinerstates_marinerid" btree (marinerid) "marinerstates_marinerid_starttime" btree (marinerid, starttime) "marinerstates_rankid" btree (rankid) "marinerstates_rankid_cur_mariners" btree (rankid) WHERE endtime IS NULL "marinerstates_rankid_past_state" btree (rankid, starttime, endtime) WH= ERE endtime IS NOT NULL "marinerstates_state" btree (state) "marinerstates_state_cur_mariners" btree (state) WHERE endtime IS NULL "marinerstates_state_past_state" btree (state, starttime, endtime) WHER= E endtime IS NOT NULL "marinerstates_vslid" btree (vslid) "marinerstates_vslid_cur_mariners" btree (vslid) WHERE endtime IS NULL "marinerstates_vslid_past_state" btree (vslid, starttime, endtime) WHER= E endtime IS NOT NULL vessels =3D=3D=3D=3D=3D name | character varying(200) | not null id | integer | not null default nextval(('p= ublic.vessels_id_seq'::text)::regclass) =2E.. Indexes: "vessels_pkey" PRIMARY KEY, btree (id) "vessels_name_key" UNIQUE, btree (name) "idx_name" btree (name) "vessels_flag" btree (flag) "vessels_groupno" btree (groupno) "vessels_vslstatus_idx" btree (vslstatus) dynacom=3D# SELECT count(*) from mariner; count =2D------ 14447 dynacom=3D# SELECT count(*) from marinerstates; count =2D------ 51013 dynacom=3D# SELECT avg(marqry.cnt),stddev(marqry.cnt) FROM (SELECT m.id,cou= nt(ms.id) as cnt from mariner m, marinerstates ms WHERE m.id=3Dms.marinerid= group by m.id) AS marqry; avg | stddev =2D-------------------+-------------------- 3.5665944207508914 | 4.4416879361829170 (vessels do not play any impact in the query, so i'll leave them out) Slow plan in 9.0.2 : = QUERY PLAN = =20 =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D-------------------------------------------------------------------- Unique (cost=3D11525.09..11571.55 rows=3D3717 width=3D23) (actual time=3D= 10462.561..10462.937 rows=3D603 loops=3D1) -> Sort (cost=3D11525.09..11534.38 rows=3D3717 width=3D23) (actual tim= e=3D10462.560..10462.664 rows=3D603 loops=3D1) Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)= ), (COALESCE(m.midname, ''::character varying)), m.id Sort Method: quicksort Memory: 71kB -> Hash Join (cost=3D8281.98..11304.67 rows=3D3717 width=3D23) (= actual time=3D10425.261..10461.621 rows=3D603 loops=3D1) Hash Cond: (ms.marinerid =3D m.id) -> Hash Join (cost=3D20.12..2963.83 rows=3D3717 width=3D4)= (actual time=3D0.228..34.993 rows=3D2625 loops=3D1) Hash Cond: (ms.vslid =3D vsl.id) -> Seq Scan on marinerstates ms (cost=3D0.00..2889.3= 2 rows=3D4590 width=3D8) (actual time=3D0.011..33.494 rows=3D2625 loops=3D1) 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) (= actual time=3D0.207..0.207 rows=3D272 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on vessels vsl (cost=3D0.00..16.72= rows=3D272 width=3D4) (actual time=3D0.004..0.118 rows=3D272 loops=3D1) -> Hash (cost=3D8172.57..8172.57 rows=3D7143 width=3D23) (= actual time=3D10424.994..10424.994 rows=3D12832 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 702kB -> Seq Scan on mariner m (cost=3D7614.86..8172.57 ro= ws=3D7143 width=3D23) (actual time=3D10409.498..10419.971 rows=3D12832 loop= s=3D1) Filter: ((NOT (hashed SubPlan 1)) AND ((marinert= ype)::text =3D 'Mariner'::text)) SubPlan 1 -> Unique (cost=3D2768.00..7614.86 rows=3D1 = width=3D4) (actual time=3D87.495..10408.446 rows=3D1454 loops=3D1) -> Nested Loop (cost=3D2768.00..7614.8= 6 rows=3D1 width=3D4) (actual time=3D87.493..10407.517 rows=3D1835 loops=3D= 1) Join Filter: (msold.marinerid =3D = mold.id) -> Index Scan using mariner_pkey = on mariner mold (cost=3D0.00..1728.60 rows=3D14286 width=3D4) (actual time= =3D0.007..13.931 rows=3D14286 loops=3D1) Filter: ((marinertype)::text= =3D 'Mariner'::text) -> Materialize (cost=3D2768.00..= 5671.97 rows=3D1 width=3D8) (actual time=3D0.003..0.330 rows=3D1876 loops= =3D14286) -> Nested Loop (cost=3D276= 8.00..5671.96 rows=3D1 width=3D8) (actual time=3D39.723..85.401 rows=3D1876= loops=3D1) -> Hash Semi Join (c= ost=3D2768.00..5671.67 rows=3D1 width=3D12) (actual time=3D39.708..81.501 r= ows=3D1876 loops=3D1) 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) (actual = time=3D0.003..33.952 rows=3D2625 loops=3D1) 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) (actual time=3D39.613..39.613 = rows=3D41250 loops=3D1) Buckets: 8= 192 Batches: 1 Memory Usage: 2246kB -> Seq Sc= an on marinerstates msold2 (cost=3D0.00..2251.66 rows=3D41307 width=3D24) = (actual time=3D0.002..24.882 rows=3D41250 loops=3D1) 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) (actu= al time=3D0.001..0.001 rows=3D1 loops=3D1876) Index Cond: (vsl= old.id =3D msold.vslid) Total runtime: 10463.619 ms (37 rows) =46ast plan in 8.3.13 :=20 = QUERY PLAN = =20 =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D--- Unique (cost=3D633677.56..633700.48 rows=3D1834 width=3D23) (actual time= =3D543.684..551.003 rows=3D603 loops=3D1) -> Sort (cost=3D633677.56..633682.14 rows=3D1834 width=3D23) (actual t= ime=3D543.676..546.070 rows=3D603 loops=3D1) Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)= ), (COALESCE(m.midname, ''::character varying)), m.id Sort Method: quicksort Memory: 53kB -> Hash Join (cost=3D630601.65..633578.15 rows=3D1834 width=3D23= ) (actual time=3D439.969..540.573 rows=3D603 loops=3D1) Hash Cond: (ms.vslid =3D vsl.id) -> Hash Join (cost=3D630580.33..633530.01 rows=3D2261 widt= h=3D27) (actual time=3D437.459..532.847 rows=3D603 loops=3D1) Hash Cond: (ms.marinerid =3D m.id) -> Seq Scan on marinerstates ms (cost=3D0.00..2875.3= 2 rows=3D4599 width=3D8) (actual time=3D0.017..80.153 rows=3D2625 loops=3D1) 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) (actual time=3D437.307..437.307 rows=3D12832 loops=3D1) -> Index Scan using mariner_pkey on mariner m = (cost=3D628776.89..630491.54 rows=3D7103 width=3D23) (actual time=3D311.023= =2E.380.168 rows=3D12832 loops=3D1) Filter: ((NOT (hashed subplan)) AND ((mari= nertype)::text =3D 'Mariner'::text)) SubPlan -> Unique (cost=3D0.00..628772.30 rows= =3D1834 width=3D4) (actual time=3D0.129..303.981 rows=3D1454 loops=3D1) -> Nested Loop (cost=3D0.00..628= 767.72 rows=3D1834 width=3D4) (actual time=3D0.120..289.961 rows=3D1835 loo= ps=3D1) -> Nested Loop (cost=3D0.0= 0..627027.98 rows=3D1865 width=3D4) (actual time=3D0.099..237.128 rows=3D18= 76 loops=3D1) -> Index Scan using m= arinerstates_marinerid on marinerstates msold (cost=3D0.00..626316.07 rows= =3D2299 width=3D8) (actual time=3D0.079..186.150 rows=3D1876 loops=3D1) 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) (ac= tual time=3D0.020..0.020 rows=3D1 loops=3D2625) 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) (actual time=3D0.009..0.009 rows=3D6 loops=3D2625) 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) (actu= al time=3D0.006..0.010 rows=3D1 loops=3D1876) 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) (actual time= =3D0.007..0.012 rows=3D1 loops=3D1876) 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) (actual= time=3D2.491..2.491 rows=3D273 loops=3D1) -> Seq Scan on vessels vsl (cost=3D0.00..17.81 rows= =3D281 width=3D4) (actual time=3D0.012..1.306 rows=3D273 loops=3D1) Total runtime: 553.601 ms (33 rows) Is there any other data i could post (pg_stat,...) that would help? thanx a lot. >=20 > Regards >=20 >=20 > Ing. Marcos Lu=C3=ADs Ort=C3=ADz Valmaseda > Linux User # 418229 && PostgreSQL DBA > Centro de Tecnolog=C3=ADas Gesti=C3=B3n de Datos (DATEC) > http://postgresql.uci.cu > http://www.postgresql.org > http://it.toolbox.com/blogs/sql-apprentice >=20 =2D-=20 Achilleas Mantzios