From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: Postgresql 9.0.2 explain analyze very slow (10x),
	compared to actual query time
Date: Fri, 14 Jan 2011 15:53:38 +0200
User-Agent: KMail/1.9.7
References: <201101111753.22602.achill@matrix.gatewaynet.com>
	<27365.1294850720@sss.pgh.pa.us>
	<201101131613.23428.achill@matrix.gatewaynet.com>
In-Reply-To: <201101131613.23428.achill@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-7"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201101141553.38236.achill@matrix.gatewaynet.com>

=D3=F4=E9=F2 Thursday 13 January 2011 16:13:23 =EF/=E7 Achilleas Mantzios =
=DD=E3=F1=E1=F8=E5:
>=20
> Next step, if i am lucky tonight, i'll set up a new linux box with 9.0.2 =
and load=20
> the same small (coming from FBSD_DEV) database.
>=20
> So my problem was twofold : a) EXPLAIN ANALYZE performance, b) the partic=
ular slow query.
>=20
> It seems there is only b) to solve. (see the previous email)
>=20
> A lot of thanx (but stay tuned ;)

Linux behaves the same way. I setup postgersql-9.0.2 in a fairly new debian=
 (Debian GNU/Linux 5.0)=20
(2x AMD athlon @2.2GZ, 4GB Mem) (lets call this LINUX_TEST)
with the exact same small db as currently in FBSD_TEST test (derived from F=
BSD_DEV)
and the times i get for this particular query (known from the previous post=
s) are bad=20
(and of course much worse than the faster FBSD_TEST, which is normal since =
LINUX_TEST
is a slower/older machine)
The plans are identical for the two 9.0.2 boxes. (FBSD_TEST, LINUX_TEST)
In the LINUX_TEST i have left only postgres running to decrease the uncontr=
olled consumption of resources
and reduce the probability of swapping (i.e. gave all machine to postgresql)

The query is (i rewrite it here)
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 m.=
id not in=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 '20=
06-07-15' and msold.starttime::date <=3D '2007-01-11' and=20
    exists (select 1 from marinerstates msold2 where msold2.marinerid=3Dmso=
ld.marinerid and msold2.state=3D'Active' and msold2.id <> msold.id and=20
               msold2.starttime<msold.starttime AND (msold.starttime-msold2=
=2Eendtime)<=3D'18 months')
    and mold.marinertype=3D'Mariner' )=20
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');

LINUX_TEST : query : Time: 6685.910 ms , EXPLAIN ANALYZE : Total runtime: 1=
11169.959 ms
=46BSD_TEST : query : Time: 2284,918 ms , EXPLAIN ANALYZE : Total runtime: =
10432.405 ms

And for the NOT EXISTS version:
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,'');

LINUX_TEST : query : Time: 771.730 ms , EXPLAIN ANALYZE : Total runtime: 12=
48.320 ms
=46BSD_TEST : query : Time: 139,568 ms , EXPLAIN ANALYZE : Total runtime: 1=
73.524 ms

So, it seems that the original "not in" version of the query suffers in pos=
tgresql 9.0.2=20

=2D-=20
Achilleas Mantzios