Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CB11D9FB2B7 for ; Mon, 16 Oct 2006 18:37:53 -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 72316-05 for ; Mon, 16 Oct 2006 18:37:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 957979FB2A5 for ; Mon, 16 Oct 2006 18:37:48 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 828A830AF8; Mon, 16 Oct 2006 23:37:46 +0200 (MET DST) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Performance Optimization for Dummies 2 - the SQL Date: Mon, 16 Oct 2006 17:37:41 -0400 Organization: Hub.Org Networking Services Lines: 83 Message-ID: References: <1160153623.6181.13.camel@state.g2switchworks.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; Response 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.059 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/307 X-Sequence-Number: 21436 > what is the facility_address_id is null all about? remove it since you > hardcode it to true in select. 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. Admittedly, it's redundant but I put it there to make sure that I matched up the columns from the other select in the union clause. > you have a two part part key on facility(country code, postal code), > right? The indexes and constrains are below. If you see redundancy, this was from vain attempts to please the optimiser gods. Carlo ALTER TABLE mdx_core.facility ADD CONSTRAINT facility_pkey PRIMARY KEY(facility_id); CREATE INDEX facility_country_state_city_idx ON mdx_core.facility USING btree (default_country_code, default_state_code, lower(default_city::text)); CREATE INDEX facility_country_state_postal_code_idx ON mdx_core.facility USING btree (default_country_code, default_state_code, default_postal_code); CREATE INDEX facility_facility_country_state_city_idx ON mdx_core.facility USING btree (facility_id, default_country_code, default_state_code, lower(default_city::text)); CREATE INDEX facility_facility_country_state_postal_code_idx ON mdx_core.facility USING btree (facility_id, default_country_code, default_state_code, default_postal_code); ""Merlin Moncure"" wrote in message news:b42b73150610161407l55e66854g94b31de6dd511f16@mail.gmail.com... > On 10/15/06, Carlo Stonebanks wrote: >> that contains full address data >> */ >> select >> f.facility_id, >> null as facility_address_id, >> null as address_id, >> f.facility_type_code, >> f.name, >> null as address, >> f.default_city as city, >> f.default_state_code as state_code, >> f.default_postal_code as postal_code, >> f.default_country_code as country_code, >> null as parsed_unit >> from >> mdx_core.facility as f >> left outer join mdx_core.facility_address as fa >> on fa.facility_id = f.facility_id >> where >> facility_address_id is null >> and f.default_country_code = 'US' >> and (f.default_postal_code = '14224-1945' or f.default_postal_code = >> '14224') > > what is the facility_address_id is null all about? remove it since you > hardcode it to true in select. > > you have a two part part key on facility(country code, postal code), > right? > > merlin > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >