Postgres 8.3 only uses seq scan

Hi,

I have system here with Debian/Testing and the latest 8.2 and 8.3
database installed.

on a blank database I create two very simple tables

                                Table "public.foo"
 Column |       Type        |                      Modifiers
--------+-------------------+------------------------------------------------------
 foo_id | integer           | not null default
nextval('foo_foo_id_seq'::regclass)
 test   | character varying |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (foo_id)


                                Table "public.bar"
 Column |       Type        |                      Modifiers
--------+-------------------+------------------------------------------------------
 bar_id | integer           | not null default
nextval('bar_bar_id_seq'::regclass)
 foo_id | integer           | not null
 test   | character varying |
Indexes:
    "bar_pkey" PRIMARY KEY, btree (bar_id)
    "bar_foo_id_idx" btree (foo_id)
Foreign-key constraints:
    "bar_foo_id_fkey" FOREIGN KEY (foo_id) REFERENCES foo(foo_id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE

now if I run a simple join query over both tables Postgres 8.2 gives
this back for the explain:

# explain select * from foo f, bar b where f.foo_id = b.foo_id;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..33.14 rows=3 width=76)
   ->  Index Scan using bar_foo_id_idx on bar b  (cost=0.00..12.30
rows=3 width=40)
   ->  Index Scan using foo_pkey on foo f  (cost=0.00..6.93 rows=1 width=36)
         Index Cond: (f.foo_id = b.foo_id)


but on the 8.3 version i get this back

# explain select * from foo f, bar b where f.foo_id = b.foo_id;
                            QUERY PLAN
------------------------------------------------------------------
 Hash Join  (cost=1.07..2.14 rows=3 width=24)
   Hash Cond: (b.foo_id = f.foo_id)
   ->  Seq Scan on bar b  (cost=0.00..1.03 rows=3 width=14)
   ->  Hash  (cost=1.03..1.03 rows=3 width=10)
         ->  Seq Scan on foo f  (cost=0.00..1.03 rows=3 width=10)

once I insert a million rows he does use the index:

# explain select * from foo f, bar b where f.foo_id = b.foo_id;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..26.39 rows=9 width=35)
   ->  Seq Scan on foo f  (cost=0.00..1.03 rows=3 width=21)
   ->  Index Scan using bar_foo_id_idx on bar b  (cost=0.00..8.42 rows=3
width=14)
         Index Cond: (b.foo_id = f.foo_id)


I have seen this behavior on all of my postgres 8.3 installs. The
indexes are there, auto vacuum is turned on. even a reindex of the
tables does not help. The configuration files are identical in grounds
of memory usage, query planning, etc.

I see this on RPM packages for RedHat Enterprise, self compiled for
FreeBSD 4, and debian packages. I am seriously very very confused.

What can I do to debug this further, or find out why this happens?

Does this mean Postgres 8.3 thinks a sequence scan is faster than an
index scan? Even on tables with hundred thousands rows?

--
[ 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                                      ]