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