From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Performance Optimization for Dummies 2 - the SQL
Date: Mon, 16 Oct 2006 17:37:41 -0400
Message-ID: <eh0u34$cou$1@news.hub.org> (raw)
References: <eft782$5a1$1@news.hub.org>
<b42b73150610042326v7fdeed17rf6b96e52a681a6db@mail.gmail.com>
<eg2cds$29en$1@news.hub.org>
<b42b73150610050630t76e9eedeh45a382729d35f2f6@mail.gmail.com>
<eg611q$l0m$1@news.hub.org>
<1160153623.6181.13.camel@state.g2switchworks.com>
<b42b73150610061153i3d11d7a4r23e1d7c5317feb9f@mail.gmail.com>
<eg6f8l$2det$1@news.hub.org>
<b42b73150610090649y74c4dc99g176366921f784e7b@mail.gmail.com>
<egua6n$1fv4$1@news.hub.org>
<b42b73150610161407l55e66854g94b31de6dd511f16@mail.gmail.com>
> 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"" <mmoncure@gmail.com> wrote in message
news:b42b73150610161407l55e66854g94b31de6dd511f16@mail.gmail.com...
> On 10/15/06, Carlo Stonebanks <stonec.register@sympatico.ca> 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
>
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: <eh0u34$cou$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