optimizer not using an index...

SELECT customers.usedby, customers.hostname, customers.email,
customers.valid, customers.signuptime, pincodes.codenum,
pincodes.code, subaccts.type, subaccts.batch,
subaccts.renew, subaccts.active, subaccts.price,
subaccts.rebill, domains.name, domains.client,
domains.authtype, ibill.login, ibill.passwd,
customers.transnum, customers.subscr
FROM pincodes, subaccts, customers, domains, owners, ibill
WHERE pincodes.codenum=customers.codenum AND
pincodes.type=subaccts.type AND
domains.client=subaccts.client AND
domains.client=owners.client AND
ibill.client=domains.client AND
customers.email='caffeine(at)toodarkpark(dot)org';

explain reveals that postgres ( 6.5.0 ) isnt using some of my indexes,
opting instead for a complete table scan ( and drastically slowing things
down ).

Hash Join (cost=48272.95 rows=293465 width=222)
-> Nested Loop (cost=20598.72 rows=376206 width=138)
-> Hash Join (cost=58.73 rows=413 width=118)
-> Seq Scan on subaccts (cost=17.51 rows=379 width=58)
-> Hash (cost=24.84 rows=87 width=60)
-> Hash Join (cost=24.84 rows=87 width=60)
-> Hash Join (cost=14.56 rows=81 width=56)
-> Seq Scan on ibill (cost=3.64 rows=80 width=28)
-> Hash (cost=4.64 rows=80 width=28)
-> Seq Scan on domains (cost=4.64 rows=80 width=28)
-> Hash (cost=3.81 rows=85 width=4)
-> Seq Scan on owners (cost=3.81 rows=85 width=4)
-> Index Scan using codes_type_idx on pincodes (cost=49.73 rows=345235 width=20)
-> Hash (cost=546.46 rows=8757 width=84)
-> Seq Scan on customers (cost=546.46 rows=8757 width=84)

I have an index on customers.name, subaccts.type, ibill.client,
owners.client... every column thats being queried on. tables are all
vacuum analyze'ed ( the DEBUG notice shows that postgres does indeed 'see'
the indexes, which are all btree's, btw ).

customers table has 12k entries, pincodes has 350k, ibill as 80, domains
has 80, owners has 80, subaccts has 380.

doing a complete table scan on a column thats indexed isnt really that
nice, especially since there are 12,000 entries in it. why postgres
chooses to use table scans on other tables is also beyond me: "explain
select * from (ibill|domains|owners|subaccts) where client=1" uses the
proper index. the hash join and nested loop also bug me; thats a lot of
rows to cycle through. interestingly, when querying on "pincodes.code"
instead of "customers.name", postgres does NOT use a full table scan; it uses
the proper indexes:

Hash Join (cost=23.78 rows=5 width=222)
-> Seq Scan on ibill (cost=3.64 rows=80 width=28)
-> Hash (cost=16.37 rows=4 width=194)
-> Nested Loop (cost=16.37 rows=4 width=194)
-> Nested Loop (cost=10.22 rows=3 width=190)
-> Nested Loop (cost=6.12 rows=2 width=162)
-> Nested Loop (cost=4.07 rows=1 width=104)
-> Index Scan using codes_code_idx on pincodes (cost=2.02 rows=1 width=20)
-> Index Scan using users_pkey on customers (cost=2.05 rows=11226 width=84)
-> Index Scan using types_pkey on subaccts (cost=2.05 rows=379 width=58)
-> Index Scan using doms_pkey on domains (cost=2.05 rows=80 width=28)
-> Index Scan using owner_client_idx on owners (cost=2.05 rows=85 width=4)

so what gives ? the two queries are 90% identical apart from the column
that's being keyed on ( customers.name -vs- pincodes.code ).

any help would be MOST appreciated.

---
Howie <caffeine(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."