From: Pierre-Andre Michel <pamichel@geneva-link.ch>
To: pgsql-sql@postgresql.org
Subject: How to use an index on a bigint column
Date: Wed, 10 Apr 2002 18:29:27 +0200
Message-ID: <008e01c1e0ac$e2907800$97d0fea9@acer> (raw)
Hello,
I use many bigint (or int8) columns in my application and I need many of them to be indexed.
I am very surprised to see that the index is never used, except if I quote the value in the where clause, and this trick works only when the operator in the where clause is "=".
I found no way to have postgres using an index when a <=, >=, < or > operator is involved in a where clause (and this seems to be true whatever the datatype of the indexed column.
Here is a script creating a simple table with such an index and a few explained queries that shows the problem.
I would feel better if someone could help me to have the index be used in every case and as far as possible without having to quote values or use another column datatype because many sql queries are automatically generated by compiled classes I cannot control or modify (Enhydra).
Thanks in advance
-----------------------------------------
create table table1 (
id bigint,
name varchar(20)
);
create index idx_id on table1(id);
insert into table1 values (1, 'toto');
insert into table1 values (2, 'titi');
insert into table1 values (3, 'tutu');
explain select * from table1 where id = 2;
explain select * from table1 where id = '2';
explain select * from table1 where id > 2;
----------------------------
psql output
---------------------------
idns_dev=# create table table1 (
idns_dev(# id bigint,
idns_dev(# name varchar(20)
idns_dev(# );
CREATE
idns_dev=# create index idx_id on table1(id);
CREATE
idns_dev=#
idns_dev=# insert into table1 values (1, 'toto');
INSERT 232802 1
idns_dev=# insert into table1 values (2, 'titi');
INSERT 232803 1
idns_dev=# insert into table1 values (3, 'tutu');
INSERT 232804 1
idns_dev=#
idns_dev=# explain select * from table1 where id = 2;
NOTICE: QUERY PLAN:
Seq Scan on table1 (cost=0.00..22.50 rows=10 width=20)
EXPLAIN
idns_dev=# explain select * from table1 where id = '2';
NOTICE: QUERY PLAN:
Index Scan using idx_id on table1 (cost=0.00..8.14 rows=10 width=20)
EXPLAIN
idns_dev=# explain select * from table1 where id > 2;
NOTICE: QUERY PLAN:
Seq Scan on table1 (cost=0.00..22.50 rows=333 width=20)
EXPLAIN
______________________
Pierre-André Michel
SmartGene SA
PSE Bâtiment C
EPFL, Ecublens
CH-1015 Lausanne
tél. prof.: (+4121) 693 85 84
mobile: (+4178) 681 53 03
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pamichel@geneva-link.ch
Subject: Re: How to use an index on a bigint column
In-Reply-To: <008e01c1e0ac$e2907800$97d0fea9@acer>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox