From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Performance Optimization for Dummies 2 - the SQL
Date: Tue, 17 Oct 2006 02:39:24 -0400
Message-ID: <eh1tr0$2ops$1@news.hub.org> (raw)
References: <eft782$5a1$1@news.hub.org>
<b42b73150610161407l55e66854g94b31de6dd511f16@mail.gmail.com>
<eh0u34$cou$1@news.hub.org>
<200610161728.43193.sthomas@leapfrogonline.com>
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@leapfrogonline.com> wrote in message
news:200610161728.43193.sthomas@leapfrogonline.com...
> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stonec.register@sympatico.ca
Subject: Re: Performance Optimization for Dummies 2 - the SQL
In-Reply-To: <eh1tr0$2ops$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox