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.
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 ?
thanks
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555