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,marinerm  
> 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,marinerm  
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.marineridand  
                  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