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

A long time ago, in a galaxy far, far away, moreno(at)mochima(dot)com (Carlos Moreno) wrote:
> I can't find a reasonable explanation for this.
>
> I have a table game, with primary key gameid (an int).
>
> If I use a where involving gameid and <, or >, or <=,
> or >=, then I get a sequential scan. If I use =, then
> of course I get an Index scan.
>
> More surprising to me is the fact that using BETWEEN,
> I get an Index scan!!
>
> The following is a copy-n-paste of the EXPLAIN outputs
> (with the useless lines removed):
>
> 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)

It would appear as though the statistics may be a bit stilted such
that the second query is being handled wrongly.

#1 and #3 are pretty clear...

- In #1, it's using the index, correctly estimating that there are
only a few rows with "gameid = 1000"

- In #3, it's using the index, correctly estimating that there are
few rows with gameid between 1000 and 2000.

It seems surprising that the optimizer is estimating that there are
200420 rows with gameid < 1000.

Is it possible that you did an ANALYZE a long while back, back when
you had an enormous number of rows with gameid < 1000?

Try running ANALYZE again on the table, and see if the estimates
change.
--
select 'aa454' || '@' || 'freenet.carleton.ca';
http://cbbrowne.com/info/advocacy.html
Why isn't phonetic spelled the way it sounds?