Re: Performance Optimization for Dummies 2 - the SQL

>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"