On 10/4/06, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx> 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
