Στις 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
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
