Postgres 8.3 is not using indexes - Mailing list pgsql-general

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                                      ]