Re: Performance Optimization for Dummies 2 - the SQL

От: Carlo Stonebanks
Тема: Re: Performance Optimization for Dummies 2 - the SQL
Дата: ,
Msg-id: eh0u34$cou$1@news.hub.org
(см: обсуждение, исходный текст)
Ответ на: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks")
Ответы: Re: Performance Optimization for Dummies 2 - the SQL  (Shaun Thomas)
Список: pgsql-performance

Скрыть дерево обсуждения

Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Alex Stapleton, )
   Re: Performance Optimization for Dummies 2 - the SQL  (Markus Schaber, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  index growth problem  (Graham Davis, )
   Re: index growth problem  ("Jim C. Nasby", )
    Re: index growth problem  (Graham Davis, )
     Re: index growth problem  ("Jim C. Nasby", )
    Re: index growth problem  (Tom Lane, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
    Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Scott Marlowe, )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
    Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Shaun Thomas, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )

> 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:...
> 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
>




В списке pgsql-performance по дате сообщения:

От: Shaun Thomas
Дата:
Сообщение: Re: Performance Optimization for Dummies 2 - the SQL
От: "Carlo Stonebanks"
Дата:
Сообщение: Re: Performance Optimization for Dummies 2 - the SQL