Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 5FBD19FB279 for ; Tue, 17 Oct 2006 03:39:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 60349-05 for ; Tue, 17 Oct 2006 03:39:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id B705B9FB1E2 for ; Tue, 17 Oct 2006 03:39:33 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 022F430E0A; Tue, 17 Oct 2006 08:39:32 +0200 (MET DST) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Performance Optimization for Dummies 2 - the SQL Date: Tue, 17 Oct 2006 02:39:24 -0400 Organization: Hub.Org Networking Services Lines: 55 Message-ID: References: <200610161728.43193.sthomas@leapfrogonline.com> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2869 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.058 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/310 X-Sequence-Number: 21439 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" 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 >