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
