pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to use an index on a bigint column
3+ messages / 1 participants
[nested] [flat]

* How to use an index on a bigint column
@ 2002-04-10 07:48 Pierre-Andre Michel <pamichel@geneva-link.ch>
  0 siblings, 0 replies; 3+ messages in thread

From: Pierre-Andre Michel @ 2002-04-10 07:48 UTC (permalink / raw)
  To: pgsql-sql

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 not used, expept 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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* How to use an index on a bigint column
@ 2002-04-10 16:29 Pierre-Andre Michel <pamichel@geneva-link.ch>
  0 siblings, 0 replies; 3+ messages in thread

From: Pierre-Andre Michel @ 2002-04-10 16:29 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* How to use an index on a bigint column
@ 2002-04-10 16:29 Pierre-Andre Michel <pamichel@geneva-link.ch>
  0 siblings, 0 replies; 3+ messages in thread

From: Pierre-Andre Michel @ 2002-04-10 16:29 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-04-10 16:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-10 07:48 How to use an index on a bigint column Pierre-Andre Michel <pamichel@geneva-link.ch>
2002-04-10 16:29 How to use an index on a bigint column Pierre-Andre Michel <pamichel@geneva-link.ch>
2002-04-10 16:29 How to use an index on a bigint column Pierre-Andre Michel <pamichel@geneva-link.ch>

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