pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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