I've got a phonebook database I'm writing, an in it is a zip code
table that has ~42k zip codes indexed off the integer zip code. This work's
*great* 'cept the last time I built my database and loaded the zip codes,
my query didn't want to use the index
I think this is 6.3.2 (is there a quick way to get the version
number?).
phonebook=> explain
phonebook-> select person.person_key, person.first_name,
person.last_name,
addr_type.name as type, addrs.addr, zip.city, zip.state,
zip.zip_key as zip_code
from person, addr_type, addrs, zip, addr_map
where addr_type.addr_type_key=addrs.addr_type_key
and addrs.zip_key=zip.zip_key
and addr_map.person_key=person.person_key
and addr_map.addrs_key=addrs.addrs_key;
NOTICE: QUERY PLAN:
Nested Loop (cost=1.03 size=1 width=104)
-> Nested Loop (cost=0.00 size=1 width=76)
-> Nested Loop (cost=0.00 size=1 width=68)
-> Nested Loop (cost=0.00 size=1 width=40)
-> Seq Scan on addrs (cost=0.00 size=0 width=24)
-> Seq Scan on addr_type (cost=1.10 size=3 width=16)
-> Seq Scan on zip (cost=0.00 size=0 width=28)
-> Seq Scan on addr_map (cost=0.00 size=0 width=8)
-> Seq Scan on person (cost=1.03 size=1 width=28)
...notice the statistics are *obviously* wrong there, though I did a few
vacuum verbose analyze's which seemed to do the right thing.
I dropped the Index and rebuilt it, and I get this:
Nested Loop (cost=2.13 size=1 width=104)
-> Nested Loop (cost=1.10 size=1 width=76)
-> Nested Loop (cost=0.00 size=1 width=60)
-> Nested Loop (cost=0.00 size=1 width=52)
-> Seq Scan on addrs (cost=0.00 size=0 width=24)
-> Index Scan on zip (cost=2.05 size=42155 width=28)
-> Seq Scan on addr_map (cost=0.00 size=0 width=8)
-> Seq Scan on addr_type (cost=1.10 size=3 width=16)
-> Seq Scan on person (cost=1.03 size=1 width=28)
I also get my data back a lot faster. :)
--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________
