Hi,
i just stumbled on something very strange.
I have here a Postgres 8.3 and a Postgres 8.2 installation, as I am in
the process of merging. Both are from the debian/testing tree, both have
the same configuration file.
In my DB where I found out this trouble I have two tables, I do a very
simple join over both. The foreign key in the second table has an index.
Postgres 8.2 gives me this out:
explain SELECT DISTINCT email FROM email e, email_group eg WHERE
e.email_group_id = eg.email_group_i
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Unique (cost=65.16..66.81 rows=85 width=27)
-> Sort (cost=65.16..65.98 rows=330 width=27)
Sort Key: e.email
-> Merge Join (cost=0.00..51.35 rows=330 width=27)
Merge Cond: (eg.email_group_id = e.email_group_id)
-> Index Scan using email_group_pkey on email_group eg
(cost=0.00..12.91 rows=44 width=4)
-> Index Scan using idx_email_email_group_id on email e
(cost=0.00..34.21 rows=330 width=31)
Postgres 8.3 returns this:
explain SELECT DISTINCT email FROM email e, email_group eg WHERE
e.email_group_id = eg.email_group_id;
QUERY PLAN
---------------------------------------------------------------------------------------
Unique (cost=268688.95..274975.13 rows=51213 width=26)
-> Sort (cost=268688.95..271832.04 rows=1257236 width=26)
Sort Key: e.email
-> Hash Join (cost=2.12..85452.48 rows=1257236 width=26)
Hash Cond: (e.email_group_id = eg.email_group_id)
-> Seq Scan on email e (cost=0.00..68163.36
rows=1257236 width=30)
-> Hash (cost=1.50..1.50 rows=50 width=4)
-> Seq Scan on email_group eg (cost=0.00..1.50
rows=50 width=4)
I have reindexed the tables, vacuum (analyze) the whole DB, checked the
config if there are some settings different. But I am at a loss here.
Why is Postgres not using the indexes in the 8.3 installation.
I tried this on a different DB on the same server and on a different
server and I always get "seq_scan" back and never the usage of the index.
Any tips why this is so?
--
[ Clemens Schwaighofer -----=====:::::~ ]
[ IT Engineer/Manager ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group ]
[ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706 Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp ]
