From: Jonathan Blitz <jb@anykey.co.il>
To: 'Tim Jones' <TJones@optio.com>
To: pgsql-performance@postgresql.org
Subject: Re: slow query using sub select
Date: Tue, 23 May 2006 02:33:09 +0200
Message-ID: <04e401c67e00$79648b50$ddf71005@jonathanlaptop> (raw)
In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D340EED2@mail.optiosoftware.com>
> -----Original Message-----
> From: Tim Jones [mailto:TJones@optio.com]
> Sent: Tuesday, May 23, 2006 12:11 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] slow query using sub select
>
> Hi,
> I am having a problem with a sub select query being kinda slow. The
> query is as follows:
>
> select batterycode, batterydescription, observationdate from Battery t1
> where patientidentifier=611802158 and observationdate = (select
> max(observationdate) from Battery t2 where t2.batterycode=t1.batterycode
> and patientidentifier=611802158) order by batterydescription.
How about changing it into a standard join:
select t1.batterycode, t1.batterydescription, t2.observationdate
from Battery t1,
(Select batterycode ,max(observationdate) from Battery t2 where
patientidentifier=611802158 group by batterycode) AS T2
where t1. batterycode = t2. batterycode
Jonathan Blitz
AnyKey Limited
Israel
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.6.1/344 - Release Date: 05/19/2006
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-performance@postgresql.org
Cc: jb@anykey.co.il, TJones@optio.com
Subject: Re: slow query using sub select
In-Reply-To: <04e401c67e00$79648b50$ddf71005@jonathanlaptop>
* 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