Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time

Στις Thursday 13 January 2011 16:13:23 ο/η Achilleas Mantzios έγραψε:
>
> Next step, if i am lucky tonight, i'll set up a new linux box with 9.0.2 and load
> the same small (coming from FBSD_DEV) database.
>
> So my problem was twofold : a) EXPLAIN ANALYZE performance, b) the particular slow query.
>
> It seems there is only b) to solve. (see the previous email)
>
> 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)
(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 FBSD_DEV)
and the times i get for this particular query (known from the previous posts) are bad
(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 uncontrolled 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.surname from marinerstates ms,vessels vsl,mariner m where
m.id=ms.marinerid and ms.vslid=vsl.id and ms.state='Active' and coalesce(ms.endtime,now())::date >= '2006-07-15' and
ms.starttime::date <= '2007-01-11' and m.marinertype='Mariner' and m.id not in
(SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner mold where mold.id=msold.marinerid and msold.vslid=vslold.id and
msold.state='Active' and coalesce(msold.endtime,now())::date >= '2006-07-15' and msold.starttime::date <= '2007-01-11' and
exists (select 1 from marinerstates msold2 where msold2.marinerid=msold.marinerid and msold2.state='Active' and msold2.id <> msold.id and
msold2.starttime<msold.starttime AND (msold.starttime-msold2.endtime)<='18 months')
and mold.marinertype='Mariner' )
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');

LINUX_TEST : query : Time: 6685.910 ms , EXPLAIN ANALYZE : Total runtime: 111169.959 ms
FBSD_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.surname from marinerstates ms,vessels vsl,mariner m where
m.id=ms.marinerid and ms.vslid=vsl.id and ms.state='Active' and coalesce(ms.endtime,now())::date >= '2006-07-15' and
ms.starttime::date <= '2007-01-11' and m.marinertype='Mariner' and NOT EXISTS
(SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner mold where mold.id=msold.marinerid and msold.vslid=vslold.id and
msold.state='Active' and coalesce(msold.endtime,now())::date >= '2006-07-15' and msold.starttime::date <= '2007-01-11' and
exists (select 1 from marinerstates msold2 where msold2.marinerid=msold.marinerid and msold2.state='Active' and msold2.id <> msold.id and
msold2.starttime<msold.starttime AND (msold.starttime-msold2.endtime)<='18 months')
and mold.marinertype='Mariner' AND mold.id=m.id)
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');

LINUX_TEST : query : Time: 771.730 ms , EXPLAIN ANALYZE : Total runtime: 1248.320 ms
FBSD_TEST : query : Time: 139,568 ms , EXPLAIN ANALYZE : Total runtime: 173.524 ms

So, it seems that the original "not in" version of the query suffers in postgresql 9.0.2

--
Achilleas Mantzios