>I think there's 2 things that would help this case. First, partition on > country. You can either do this on a table level or on an index level > by putting where clauses on the indexes (index method would be the > fastest one to test, since it's just new indexes). That should shrink > the size of that index noticably. I'm afraid I don't quite understand this, or how to 'partition' this at a table level. Right now, the table consists of ONLY US addresses, so I don't know if I would expect a performance improvement in changing the table or the indexes as the indexes would not reduce anything.> > The other thing is to try and get the planner to not double-scan the > index. If you add the following, I think it will scan the index once for > the LIKE, and then just filter whatever it finds to match the other > conditions. > > and f.default_postal_code LIKE '14224%' I did try this - nothing signoificant came from the results (see below) thanks, Carlo explain analyze 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 like '14224%' and (f.default_postal_code = '14224-1945' or f.default_postal_code = '14224') "Nested Loop Left Join (cost=26155.38..26481.58 rows=1 width=71) (actual time=554.138..554.138 rows=0 loops=1)" " Filter: ("inner".facility_address_id IS NULL)" " -> Bitmap Heap Scan on facility f (cost=26155.38..26477.68 rows=1 width=71) (actual time=554.005..554.025 rows=7 loops=1)" " Recheck Cond: (((default_country_code = 'US'::bpchar) AND ((default_postal_code)::text = '14224-1945'::text)) OR ((default_country_code = 'US'::bpchar) AND ((default_postal_code)::text = '14224'::text)))" " Filter: ((default_postal_code)::text ~~ '14224%'::text)" " -> BitmapOr (cost=26155.38..26155.38 rows=113 width=0) (actual time=553.983..553.983 rows=0 loops=1)" " -> Bitmap Index Scan on facility_facility_country_state_postal_code_idx (cost=0.00..13077.69 rows=57 width=0) (actual time=313.156..313.156 rows=7 loops=1)" " Index Cond: ((default_country_code = 'US'::bpchar) AND ((default_postal_code)::text = '14224-1945'::text))" " -> Bitmap Index Scan on facility_facility_country_state_postal_code_idx (cost=0.00..13077.69 rows=57 width=0) (actual time=240.819..240.819 rows=0 loops=1)" " Index Cond: ((default_country_code = 'US'::bpchar) AND ((default_postal_code)::text = '14224'::text))" " -> Index Scan using facility_address_facility_address_address_type_idx on facility_address fa (cost=0.00..3.89 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=7)" " Index Cond: (fa.facility_id = "outer".facility_id)" "Total runtime: 554.243 ms"