From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time
Date: Thu, 13 Jan 2011 14:27:26 +0200
Message-ID: <201101131427.26399.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <201101131413.21757.achill@matrix.gatewaynet.com>
References: <201101111753.22602.achill@matrix.gatewaynet.com>
<27365.1294850720@sss.pgh.pa.us>
<201101131413.21757.achill@matrix.gatewaynet.com>
Óôéò 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time
In-Reply-To: <201101131427.26399.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox