> 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@xxxxxxxxx> wrote in message news:b42b73150610161407l55e66854g94b31de6dd511f16@xxxxxxxxxxxxxxxxx > On 10/15/06, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx> 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 >
