Skip site navigation (1) Skip section navigation (2)

slow query using sub select

From: "Tim Jones" <TJones(at)optio(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: slow query using sub select
Date: 2006-05-22 22:11:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
  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.
explain analyze:

'Sort (cost=1697.16..1697.16 rows=1 width=31) (actual
time=910.721..910.729 rows=22 loops=1)'
' Sort Key: batterydescription'
' -> Index Scan using ix_battery_patient on battery t1
(cost=0.00..1697.15 rows=1 width=31) (actual time=241.836..910.580
rows=22 loops=1)'
' Index Cond: (patientidentifier = 611802158)'
' Filter: (observationdate = (subplan))'
' SubPlan'
' -> Aggregate (cost=26.25..26.26 rows=1 width=8) (actual
time=9.666..9.667 rows=1 loops=94)'
' -> Bitmap Heap Scan on battery t2 (cost=22.23..26.25 rows=1 width=8)
(actual time=9.606..9.620 rows=7 loops=94)'
' Recheck Cond: ((patientidentifier = 611802158) AND
((batterycode)::text = ($0)::text))'
' -> BitmapAnd (cost=22.23..22.23 rows=1 width=0) (actual
time=9.596..9.596 rows=0 loops=94)'
' -> Bitmap Index Scan on ix_battery_patient (cost=0.00..2.20 rows=58
width=0) (actual time=0.039..0.039 rows=94 loops=94)'
' Index Cond: (patientidentifier = 611802158)'
' -> Bitmap Index Scan on ix_battery_code (cost=0.00..19.78 rows=2794
width=0) (actual time=9.514..9.514 rows=27323 loops=94)'
' Index Cond: ((batterycode)::text = ($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 ?

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


pgsql-performance by date

Next:From: Tom LaneDate: 2006-05-22 23:07:24
Subject: Re: slow query using sub select
Previous:From: Meetesh KariaDate: 2006-05-22 20:40:11
Subject: Re: Query hanging/not finishing inconsistently

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group