Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #848: Index on bigint column is unused in selects
Date: 2002-12-14 20:33:00
Message-ID: 20021214203300.DCDD04758E6@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: Frank van VugtDate: 2002-12-15 11:02:40
Subject: v7.3 : \encoding doesn't show changes in client encoding that are not done with \encoding itself (i.e. set names/set client_encoding)
Previous:From: Bruce MomjianDate: 2002-12-14 18:49:47
Subject: Re: postmaster segfaults when pg_hba.cof is missing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group