Re: Performance Optimization for Dummies 2 - the SQL

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" <sthomas(at)leapfrogonline(dot)com> wrote in message
news:200610161728(dot)43193(dot)sthomas(at)leapfrogonline(dot)com(dot)(dot)(dot)
> 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
>