Date: Mon, 17 Jan 2011 11:52:27 -0500 (CST)
From: "Ing. Marcos Ortiz Valmaseda" <mlortiz@uci.cu>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-performance@postgresql.org
Message-ID: <955263366.48014741295283147090.JavaMail.root@ucimail4.uci.cu>
In-Reply-To: <359477366.48012291295282927070.JavaMail.root@ucimail4.uci.cu>
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
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Query is :
SELECT distinct m.id,coalesce(m.givenname,''),
       coalesce(m.midname,''),
       m.surname from marinerstates ms,vessels vsl,mariner m=20
WHERE  m.id=3Dms.marinerid and ms.vslid=3Dvsl.id=20
AND    ms.state=3D'Active' and coalesce(ms.endtime,now())::date >=3D '2006-=
07-15'
AND    ms.starttime::date <=3D '2007-01-11'  AND
       m.marinertype=3D'Mariner'  and m.id=20
NOT IN (SELECT distinct mold.id
        FROM marinerstates msold,
        vessels vslold,
        mariner mold =20
        WHERE mold.id=3Dmsold.marinerid=20
        AND  msold.vslid=3Dvslold.id
        AND msold.state=3D'Active'=20
        AND coalesce(msold.endtime,now())::date >=3D '2006-07-15'=20
        AND msold.starttime::date <=3D '2007-01-11'=20
        AND EXISTS (SELECT 1=20
                    FROM marinerstates msold2=20
                    WHERE msold2.marinerid=3Dmsold.marinerid
                    AND msold2.state=3D'Active'=20
                    AND msold2.id <> msold.id=20
                    AND msold2.starttime<msold.starttime
                    AND (msold.starttime-msold2.endtime)<=3D'18 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
FBSD_DEV : query : 240.419 ms
LINUX_PROD : query : 219.568 ms
FBSD_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'  and m.marinertype=3D'Mariner'  and NO=
T EXISTS=20
   (SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner=
 mold  where mold.id=3Dmsold.marinerid and msold.vslid=3Dvslold.id and=20
   msold.state=3D'Active' and coalesce(msold.endtime,now())::date >=3D '200=
6-07-15' and msold.starttime::date <=3D '2007-01-11' and=20
   exists (select 1 from marinerstates msold2 where msold2.marinerid=3Dmsol=
d.marinerid and msold2.state=3D'Active' and msold2.id <> msold.id and=20
              msold2.starttime<msold.starttime AND (msold.starttime-msold2.=
endtime)<=3D'18 months') =20
   and mold.marinertype=3D'Mariner' AND mold.id=3Dm.id)=20
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');
gives:

FBSD_DEV : query : 154.000 ms
LINUX_PROD : query : 153.408 ms
FBSD_TEST : query :  137.000 ms
LINUX_TEST : query : 404.000 ms


Well, on the Release Notes on the PostgreSQL-8.4 Documentation, the develop=
ers recommend to use NOT EXISTS=20
instead NOT IN, because the first clause has a better performance. So, you =
can use it on that way.

Other questions?
- Do you have a partial index on marinerstates.marinerid where this conditi=
on is accomplished?
- Do you have a index on mariner.id?
- Can you provide a explain of these queries on the PostgreSQL-9.0 machines=
?

Regards


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