Received: from aristote2.infomaniak.ch (aristote2.infomaniak.ch [212.23.248.16]) by postgresql.org (Postfix) with ESMTP id EFE85476470 for ; Wed, 10 Apr 2002 12:23:29 -0400 (EDT) Received: from acer (unverified [128.179.67.18]) by aristote2.infomaniak.ch (Rockliffe SMTPRA 4.5.6) with ESMTP id for ; Wed, 10 Apr 2002 18:22:59 +0200 Message-ID: <008e01c1e0ac$e2907800$97d0fea9@acer> Reply-To: "Pierre-Andre Michel" From: "Pierre-Andre Michel" To: Subject: How to use an index on a bigint column Date: Wed, 10 Apr 2002 18:29:27 +0200 Organization: SmartGene MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008B_01C1E0BD.A5D62480" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Archive-Number: 200204/119 X-Sequence-Number: 7384 This is a multi-part message in MIME format. ------=_NextPart_000_008B_01C1E0BD.A5D62480 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I use many bigint (or int8) columns in my application and I need many of th= em to be indexed.=20 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 "=3D".=20 I found no way to have postgres using an index when a <=3D, >=3D, < or > op= erator is involved in a where clause (and this seems to be true whatever th= e datatype of the indexed column. Here is a script creating a simple table with such an index and a few expla= ined queries that shows the problem. I would feel better if someone could help me to have the index be used in e= very case and as far as possible without having to quote values or use anot= her column datatype because many sql queries are automatically generated by= compiled classes I cannot control or modify (Enhydra).=20 Thanks in advance ----------------------------------------- create table table1 ( id bigint, name varchar(20)=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 =3D 2; explain select * from table1 where id =3D '2'; explain select * from table1 where id > 2; ---------------------------- psql output --------------------------- idns_dev=3D# create table table1 ( idns_dev(# id bigint, idns_dev(# name varchar(20) idns_dev(# ); CREATE idns_dev=3D# create index idx_id on table1(id); CREATE idns_dev=3D# idns_dev=3D# insert into table1 values (1, 'toto'); INSERT 232802 1 idns_dev=3D# insert into table1 values (2, 'titi'); INSERT 232803 1 idns_dev=3D# insert into table1 values (3, 'tutu'); INSERT 232804 1 idns_dev=3D# idns_dev=3D# explain select * from table1 where id =3D 2; NOTICE: QUERY PLAN: =20 Seq Scan on table1 (cost=3D0.00..22.50 rows=3D10 width=3D20) =20 EXPLAIN idns_dev=3D# explain select * from table1 where id =3D '2'; NOTICE: QUERY PLAN: =20 Index Scan using idx_id on table1 (cost=3D0.00..8.14 rows=3D10 width=3D20) =20 EXPLAIN idns_dev=3D# explain select * from table1 where id > 2; NOTICE: QUERY PLAN: =20 Seq Scan on table1 (cost=3D0.00..22.50 rows=3D333 width=3D20) =20 EXPLAIN ______________________ Pierre-Andr=E9 Michel SmartGene SA PSE B=E2timent C EPFL, Ecublens CH-1015 Lausanne t=E9l. prof.: (+4121) 693 85 84 mobile: (+4178) 681 53 03 ------=_NextPart_000_008B_01C1E0BD.A5D62480 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello,
 
I use many bigint (or int8) columns in my= =20 application and I need many of them to be indexed.
I am very surprised to see that the index = is never=20 used, except if I quote the value in the where clause, and this trick works= only=20 when the operator in the where clause is "=3D".
I found no way to have postgres using = ;an index=20 when a <=3D, >=3D, < or > operator is involved in a where claus= e (and=20 this seems to be true whatever the datatype of the indexed column.
 
Here is a script creating a simple table w= ith such=20 an index and a few explained queries that shows the problem.
I would feel better if someone could help = me to=20 have the index be used in every case and as far as possible without having = to=20 quote values or use another column datatype because many sql queries are=20 automatically generated by compiled classes I cannot control or modify=20 (Enhydra).
 
Thanks in advance
 
-----------------------------------------
 
create table table1 (
  id=20 bigint,
  name varchar(20)
);
create index idx_id on=20 table1(id);
 
insert into table1 values (1, 'toto');
= insert=20 into table1 values (2, 'titi');
insert into table1 values (3,=20 'tutu');
 
explain select * from table1 where id =3D= =20 2;
explain select * from table1 where id =3D '2';
explain select * fr= om=20 table1 where id > 2;
 
----------------------------
psql=20 output
---------------------------
 
idns_dev=3D# create table table1=20 (
idns_dev(#   id bigint,
idns_dev(#   name=20 varchar(20)
idns_dev(# );
CREATE
idns_dev=3D# create index idx_id = on=20 table1(id);
CREATE
idns_dev=3D#
idns_dev=3D# insert into table1 va= lues (1,=20 'toto');
INSERT 232802 1
idns_dev=3D# insert into table1 values (2,= =20 'titi');
INSERT 232803 1
idns_dev=3D# insert into table1 values (3,= =20 'tutu');
INSERT 232804 1
idns_dev=3D#
idns_dev=3D# explain select = * from=20 table1 where id =3D 2;
NOTICE:  QUERY PLAN:
 
Seq Scan o= n=20 table1  (cost=3D0.00..22.50 rows=3D10=20 width=3D20)
 
EXPLAIN
idns_dev=3D# explain select * from tabl= e1 where=20 id =3D '2';
NOTICE:  QUERY PLAN:
 
Index Scan using idx_= id on=20 table1  (cost=3D0.00..8.14 rows=3D10=20 width=3D20)
 
EXPLAIN
idns_dev=3D# explain select * from tabl= e1 where=20 id > 2;
NOTICE:  QUERY PLAN:
 
Seq Scan on table1&nbs= p;=20 (cost=3D0.00..22.50 rows=3D333 width=3D20)
 
EXPLAIN
<= /DIV>
______________________
Pierre-Andr=E9= =20 Michel
 
SmartGene SA
PSE B=E2timent C
EPFL,= =20 Ecublens
CH-1015 Lausanne
 
t=E9l. prof.: (+4121) 693 85=20 84
mobile:    (+4178) 681 53 03
------=_NextPart_000_008B_01C1E0BD.A5D62480--