Re: Postgres 8.3 only uses seq scan

On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer
<clemens.schwaighofer@tequila.jp> wrote:
> 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)

Of course it uses a seq scan.  All the data fits handily into a single
page I assume.

> 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 don't see a million rows here, only three.  Have you run analyze
after loading all that data?  Or is it retrieving 3 rows out of a
million?  If so then an index scan does make sense.