Re: Postgres 8.3 only uses seq scan

Try running EXPLAIN ANALYZE - that gives much more information. For
example it may show differences in number of rows between the two
machines, that the statistics are not up to date, etc.

regards
Tomas

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