From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Tim Jones" <TJones(at)optio(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow query using sub select |
Date: | 2006-05-22 23:07:24 |
Message-ID: | 15852.1148339244@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"Tim Jones" <TJones(at)optio(dot)com> writes:
> 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.
Yeah, this is essentially impossible for the planner to optimize,
because it doesn't see any way to de-correlate the subselect, so it does
it over again for every row. You might find it works better if you cast
the thing as a SELECT DISTINCT ON problem (look at the "weather report"
example in the SELECT reference page).
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Steinar H. Gunderson | 2006-05-22 23:33:33 | Re: Query performance |
Previous Message | Tim Jones | 2006-05-22 22:11:07 | slow query using sub select |