Re: Performance Optimization for Dummies 2 - the SQL

On 10/4/06, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca> wrote:
> > can you do explain analyze on the two select queries on either side of
> > the union separatly? the subquery is correctly written and unlikely
> > to be a problem (in fact, good style imo). so lets have a look at
> > both sides of facil query and see where the problem is.
>
> Sorry for the delay, the server was down yesterday and couldn't get
> anything.
>
> I have modified the sub-queries a little, trying to get the index scans to
> fire - all the tables involved here are large enough to benefit from index
> scans over sequential scans. I am mystified as to why PART 1 is giving me:
>

> "Seq Scan on facility_address fa (cost=0.00..3014.68 rows=128268 width=12)
> (actual time=0.007..99.033 rows=128268 loops=1)"

not sure on this, lets go back to that.

> into account that perhaps the import row is using the 5-number US ZIP,
> not the 9-number USZIP+4

> where
> a.country_code = 'US'
> and a.state_code = 'IL'
> and a.postal_code like '60640-5759'||'%'
> order by facility_id

1. create a small function, sql preferred which truncates the zip code
to 5 digits or reduces to so called 'fuzzy' matching criteria. lets
call it zip_trunc(text) and make it immutable which it is. write this
in sql, not tcl if possible (trust me).

create index address_idx on address(country_code, state_code,
zip_trunc(postal_code));

rewrite above where clause as

where (a.country_code, a.state_code, zip_trunc(postal_code)) = ('US',
'IL', zip_trunc('60640-5759'));

try it out, then lets see how it goes and then we can take a look at
any seqscan issues.

merlin