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

Peter Roozemaal (mathfox@xs4all.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

On Sat, 2002-12-14 at 15:33, pgsql-bugs@postgresql.org wrote:
> PostgreSQL 7.2 will allways do a full table scan when the index field
> is a bigint. even with a "where bigkey = 99" clause.

This is a known problem: you need to enclose the integer literal in
single quotes, or cast it to int8 for the optimizer to consider an index
scan. i.e

select * from test1 where een = '99';

Cheers,

Neil