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

Στις Thursday 13 January 2011 14:13:21 ο/η Achilleas Mantzios έγραψε:

> 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,'')

Rewriting the query with NOT EXISTS (a practice quite common pre 8.3, IIRC) as:

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,'');

Is fast.

--
Achilleas Mantzios