Re: Performance Optimization for Dummies 2 - the SQL

On Mon, Oct 16, 2006 at 05:56:54PM -0400, Carlo Stonebanks wrote:
> >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.>

It will help because you can then drop country_code from the index,
making it smaller.

> > 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%'

Hrm... well, first step would be to drop the explicit postal code tests
just to validate that it's faster to do the LIKE than it is to do the
two explicit tests. If that proves to be the case, you can wrap that in
a subquery, and put the final where clause in the outer part of the
query. You'll probably have to use the OFFSET 0 hack, too.

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

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)