Bug #848: Index on bigint column is unused in selects

Peter Roozemaal (mathfox(at)xs4all(dot)nl) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Index on bigint column is unused in selects

Long Description
PostgreSQL 7.2 will allways do a full table scan when the index field is a bigint. even with a "where bigkey = 99" clause.

CREATE TABLE test1 (
een bigint PRIMARY KEY,
twee int UNIQUE NOT NULL,
data text);

peter=> explain select * from test1 where een > 0 and een < 1000;
NOTICE: QUERY PLAN:
Seq Scan on test1 (cost=0.00..25.00 rows=5 width=44)
EXPLAIN

peter=> explain select * from test1 where een = 99;
NOTICE: QUERY PLAN:
Seq Scan on test1 (cost=0.00..22.50 rows=1 width=44)
EXPLAIN

I expected two index scans here.

The same bug/feature is present in version 7.3

Sample Code
peter=> CREATE TABLE test1 (
een bigint PRIMARY KEY,
twee int UNIQUE NOT NULL,
data text);

-- fill table with 100000 rows --

peter=> explain select * from test1 where twee < 1000;
NOTICE: QUERY PLAN:
Seq Scan on test1 (cost=0.00..22.50 rows=333 width=44)
EXPLAIN

peter=> explain select * from test1 where twee > 0 and twee < 1000;
NOTICE: QUERY PLAN:
Index Scan using test1_twee_key on test1 (cost=0.00..17.08 rows=5 width=44)
EXPLAIN

peter=> explain select * from test1 where twee = 99;
NOTICE: QUERY PLAN:
Index Scan using test1_twee_key on test1 (cost=0.00..4.82 rows=1 width=44)
EXPLAIN

peter=> explain select * from test1 where een > 0 and een < 1000;
NOTICE: QUERY PLAN:
Seq Scan on test1 (cost=0.00..25.00 rows=5 width=44)
EXPLAIN

peter=> explain select * from test1 where een = 99;
NOTICE: QUERY PLAN:
Seq Scan on test1 (cost=0.00..22.50 rows=1 width=44)
EXPLAIN

peter=> ANALYSE;
-- output --

peter=> explain select * from test1 where twee < 1000;
NOTICE: QUERY PLAN:
Index Scan using test1_twee_key on test1 (cost=0.00..3.13 rows=10 width=99)
EXPLAIN

No file was uploaded with this report