Re: Odd behaviour -- Index scan vs. seq. scan

On Mon, 15 Sep 2003, Adam Kavan wrote:

>
> >
> > explain delete from game where gameid = 1000;
> > Index Scan using game_pkey on game (cost=0.00..3.14 rows=1 width=6)
> >
> > explain delete from game where gameid < 1000;
> > Seq Scan on game (cost=0.00..4779.50 rows=200420 width=6)
> >
> > explain delete from game where gameid between 1000 and 2000;
> > Index Scan using game_pkey on game (cost=0.00..3.15 rows=1 width=6)
> >
> >
> >How's that possible? Is it purposely done like this, or
> >is it a bug? (BTW, Postgres version is 7.2.3)
>
>
> Postgres thinks that for the = line there will only be 1 row so t uses an
> index scan. Same thing for the between. However it thinks that there are
> 200420 rows below 1000 and decides a seq scan would be faster. You can run
> EXPLAIN ANALYZE to see if its guesses are correct. You can also try SET
> enable_seqscan = FALSE; to see if it is faster doing an index scan. If it
> is faster to do an index scan edit your postgres.conf file and lower the
> cost for a random tuple, etc.

Before you do that you might wanna issue this command:

alter table game alter column gameid set statistics 100;
analyze game;

and see what you get.