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

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

--- Adam Kavan
--- akavan(at)cox(dot)net