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