From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-performance@postgresql.org
Subject: "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: Mon, 17 Jan 2011 17:35:16 +0200
User-Agent: KMail/1.9.7
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201101171735.16162.achill@matrix.gatewaynet.com>

Hello,
just coming from this thread : http://archives.postgresql.org/pgsql-admin/2=
011-01/msg00050.php
It started as an admin question and turned out to be a performance question.
You may look at it for a history of this issue. I will repost all data here.

Description of the machines involved:

1) Prod machine (thereafter called LINUX_PROD) :
System:=A0Linux Suse 2.6.16.46-0.12-smp, 16 x Intel Xeon(R) X7350 @ 2.93GHz=
, 64GB memory
DB: PostgreSQL 8.3.13, shared_buffers=3D16GB, work_mem=3D512MB, db size=3D9=
4GB
2) Dev machine (therafter called FBSD_DEV) :
System : FreeBSD 6.3, Intel(R) Core(TM)2 Duo CPU @ 2.80GHz, 2GB memory
DB: PostgreSQL 8.3.13, shared_buffers=3D512MB, work_mem=3D1MB, db size=3D76=
GB
3) Test machine (thereafter called FBSD_TEST) :
System: FreeBSD 8.1, 4 x AMD Phenom(tm) 965 @ 3.4 GHz, 8GB memory
DB: PostgreSQL 9.0.2, shared_buffers=3D5GB, work_mem=3D512MB, db size=3D7GB
4) Linux Test machine (thereafter called LINUX_TEST) :
System : Debian GNU/Linux 5.0, 2x AMD athlon @2.2GZ, 4GB Mem
DB: PostgreSQL 9.0.2, shared_buffers=3D2GB, work_mem=3D512MB, db size=3D7GB

(all DBs in the last three systems are identical, originating from FBSD_DEV)
(additiinally no paging or thrashing were observed during the tests)

Query is :
SELECT distinct m.id,coalesce(m.givenname,''),coalesce(m.midname,''),m.surn=
ame from marinerstates ms,vessels vsl,mariner m=20
where m.id=3Dms.marinerid and ms.vslid=3Dvsl.id and ms.state=3D'Active' and=
 coalesce(ms.endtime,now())::date >=3D '2006-07-15' and=20
ms.starttime::date <=3D '2007-01-11'  and m.marinertype=3D'Mariner'  and m.=
id not in=20
(SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner mo=
ld  where mold.id=3Dmsold.marinerid and msold.vslid=3Dvslold.id and=20
msold.state=3D'Active' and coalesce(msold.endtime,now())::date >=3D '2006-0=
7-15' and msold.starttime::date <=3D '2007-01-11' and exists=20
(select 1 from marinerstates msold2 where msold2.marinerid=3Dmsold.marineri=
d and msold2.state=3D'Active' and msold2.id <> msold.id and=20
msold2.starttime<msold.starttime AND (msold.starttime-msold2.endtime)<=3D'1=
8 months')  and mold.marinertype=3D'Mariner' )=20
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');  =20

i get the following execution times: (with \timing)=20
=46BSD_DEV : query : 240.419 ms
LINUX_PROD : query : 219.568 ms
=46BSD_TEST : query :  2285.509 ms
LINUX_TEST : query : 5788.988 ms

Re writing the query in the "NOT EXIST" variation like:

SELECT distinct m.id,coalesce(m.givenname,''),coalesce(m.midname,''),m.surn=
ame from marinerstates ms,vessels vsl,mariner m where=20
m.id=3Dms.marinerid and ms.vslid=3Dvsl.id and ms.state=3D'Active' and coale=
sce(ms.endtime,now())::date >=3D '2006-07-15' and=20
ms.starttime::date <=3D '2007-01-11' =A0and m.marinertype=3D'Mariner' =A0an=
d NOT EXISTS=20
=A0 =A0(SELECT distinct mold.id from marinerstates msold,vessels vslold,mar=
iner mold =A0where mold.id=3Dmsold.marinerid and msold.vslid=3Dvslold.id an=
d=20
=A0 =A0msold.state=3D'Active' and coalesce(msold.endtime,now())::date >=3D =
'2006-07-15' and msold.starttime::date <=3D '2007-01-11' and=20
=A0 =A0exists (select 1 from marinerstates msold2 where msold2.marinerid=3D=
msold.marinerid and msold2.state=3D'Active' and msold2.id <> msold.id and=20
=A0 =A0 =A0 =A0 =A0 =A0 =A0 msold2.starttime<msold.starttime AND (msold.sta=
rttime-msold2.endtime)<=3D'18 months') =A0
=A0 =A0and mold.marinertype=3D'Mariner' AND mold.id=3Dm.id)=20
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');
gives:

=46BSD_DEV : query : 154.000 ms
LINUX_PROD : query : 153.408 ms
=46BSD_TEST : query :  137.000 ms
LINUX_TEST : query : 404.000 ms

I found this query, since i observed that running the calling program was a=
ctually the first case that i=20
encountered FBSD_TEST (while running a bigger database, a recent dump from =
LINUX_PROD) to be actually
slower than LINUX_PROD.
=46rom the whole set of the tests involved, it seems like the "NOT IN" vers=
ion of the query runs slow
in any postgresql 9.0.2 tested.
=2D-=20
Achilleas Mantzios