Re: Odd behaviour -- Index scan vs. seq. scan - Mailing list pgsql-general

>
>     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@cox.net