optimizer not using an index... - Mailing list pgsql-sql

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.clientAND       domains.client=owners.client AND       ibill.client=domains.client AND
customers.email='caffeine@toodarkpark.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=80width=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
onpincodes  (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=4width=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.07rows=1 width=104)                               ->  Index Scan using codes_code_idx on pincodes  (cost=2.02
rows=1width=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_idxon 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@toodarkpark.org>   URL: http://www.toodarkpark.org    
"The distance between insanity and genius is measured only by success."