Sorry, I didn'tpoint it out because an earlier post included the query with documentation - that post got lost... or at least *I* can't see it.
The other half of the union renders the facilities that DO have addresses, and because of the performance problem (which I have finally sorted out by creating indexes which are more explicit - my oversight, really!) The original query was a slightly more complex outer join, which I then decomposed to an explicit union with two halves - one half handling the explicit "facility_address_id is null" portion, the other half handling the "is not null" portion (implicitly because of the normal join between facility and facility_address). I hadn't considered the "not exists" option - it's obvious when you look at the sub-query by itself, but didn't strike me before I broke it out of the union and you mentioned it. I was just under th eimpression that getting this sub-query to work would have produced the most clear, straightforward ANALYZE results. Carlo "Shaun Thomas" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Monday 16 October 2006 16:37, Carlo Stonebanks wrote: > >> The facility_address_id is null statement is necessary, as this is a >> sub-query from a union clause and I want to optimise the query with >> the original logic intact. The value is not hard coded to true but >> rather to null. > > Heh, you neglect to mention that this query is discovering faculty who > do *not* have an address entry, which makes the "is null" a major > necessity. With that, how did a "not exists (blabla faculty_address > blabla)" subquery to get the same effect treat you? How about an "IN > (blabla LIMIT 1)" ? > > -- > > Shaun Thomas > Database Administrator > > Leapfrog Online > 807 Greenwood Street > Evanston, IL 60201 > Tel. 847-440-8253 > Fax. 847-570-5750 > www.leapfrogonline.com > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
