Re: Performance Optimization for Dummies 2 - the SQL

> 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(at)gmail(dot)com> wrote in message
news:b42b73150610161407l55e66854g94b31de6dd511f16(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
> On 10/15/06, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)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
>