X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id DC9E39F9B14
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 22 May 2006 19:33:50 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 45780-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 22 May 2006 19:33:40 -0300 (ADT)
X-Greylist: delayed 00:22:28.381832 by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 098369FA2BC
	for <pgsql-performance@postgresql.org>;
	Mon, 22 May 2006 19:33:41 -0300 (ADT)
Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10])
	by svr4.postgresql.org (Postfix) with ESMTP id 633665B58C4
	for <pgsql-performance@postgresql.org>;
	Mon, 22 May 2006 22:11:11 +0000 (GMT)
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: slow query using sub select
Date: Mon, 22 May 2006 18:11:07 -0400
Message-ID: <47668A1334CDBF46927C1A0DFEB223D340EED2@mail.optiosoftware.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: slow query using sub select
thread-index: AcZ97KAanKWDbSdZSf+p4rSohMfwuA==
From: "Tim Jones" <TJones@optio.com>
To: <pgsql-performance@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200605/424
X-Sequence-Number: 19211

Hi,
  I am having a problem with a sub select query being kinda slow.  The
query is as follows:
=20
select batterycode, batterydescription, observationdate from Battery t1
where patientidentifier=3D611802158 and observationdate =3D (select
max(observationdate) from Battery t2 where =
t2.batterycode=3Dt1.batterycode
and patientidentifier=3D611802158) order by batterydescription.
=20
explain analyze:
   =20

'Sort (cost=3D1697.16..1697.16 rows=3D1 width=3D31) (actual
time=3D910.721..910.729 rows=3D22 loops=3D1)'
' Sort Key: batterydescription'
' -> Index Scan using ix_battery_patient on battery t1
(cost=3D0.00..1697.15 rows=3D1 width=3D31) (actual =
time=3D241.836..910.580
rows=3D22 loops=3D1)'
' Index Cond: (patientidentifier =3D 611802158)'
' Filter: (observationdate =3D (subplan))'
' SubPlan'
' -> Aggregate (cost=3D26.25..26.26 rows=3D1 width=3D8) (actual
time=3D9.666..9.667 rows=3D1 loops=3D94)'
' -> Bitmap Heap Scan on battery t2 (cost=3D22.23..26.25 rows=3D1 =
width=3D8)
(actual time=3D9.606..9.620 rows=3D7 loops=3D94)'
' Recheck Cond: ((patientidentifier =3D 611802158) AND
((batterycode)::text =3D ($0)::text))'
' -> BitmapAnd (cost=3D22.23..22.23 rows=3D1 width=3D0) (actual
time=3D9.596..9.596 rows=3D0 loops=3D94)'
' -> Bitmap Index Scan on ix_battery_patient (cost=3D0.00..2.20 =
rows=3D58
width=3D0) (actual time=3D0.039..0.039 rows=3D94 loops=3D94)'
' Index Cond: (patientidentifier =3D 611802158)'
' -> Bitmap Index Scan on ix_battery_code (cost=3D0.00..19.78 =
rows=3D2794
width=3D0) (actual time=3D9.514..9.514 rows=3D27323 loops=3D94)'
' Index Cond: ((batterycode)::text =3D ($0)::text)'
'Total runtime: 910.897 ms'

Basically I am just trying to display the batterycode with its most
recent date.  Is there a better way to do this query ?

thanks
=20
=20
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555