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.