> 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"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On 10/15/06, Carlo Stonebanks <[EMAIL PROTECTED]> 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 > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
