Re: Postgres 8.3 only uses seq scan

On 11/26/2008 02:04 PM, Scott Marlowe wrote:
> On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer
> <clemens(dot)schwaighofer(at)tequila(dot)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.

okay, the strange thing is, that in 8.2 it always used an index scan.

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

yeah, there are 3 matching rows, and the rest is just data to make the
table big.

I am just still confused, because if Postgres does only use seq scan
even in very large databases, I am worried I do something very wrong in
my DB design ...

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