Odd behaviour -- Index scan vs. seq. scan

From: Carlos Moreno <moreno(at)mochima(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Odd behaviour -- Index scan vs. seq. scan
Date: 2003-09-15 22:44:50
Message-ID: 3F6640E2.4040901@mochima.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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)

Thanks,

Carlos
--

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2003-09-15 22:54:40 Re: need for in-place upgrades (was Re: State of
Previous Message Christopher Browne 2003-09-15 21:36:32 Re: need for in-place upgrades