From: Ilker Egilmez <ilker@gate5.de>
To: pgsql-sql@postgresql.org
Subject: problem: index on number not honoured
Date: Mon, 12 Nov 2001 21:06:16 +0100
Message-ID: <9spaa8$inv$1@news.tht.net> (raw)
hi,
an index on a table column of any number type only gets honoured if you
query it like a string, e.g.
create table t1 ( n int2 ) ;
create index t1n on t1 (n) ;
explain select * from t1 where n = 1 ;
-- Seq Scan on t1 (cost=0.00..22.50 rows=10 width=2)
explain select * from t1 where n = '1' ;
-- Index Scan using t1n on t1 (cost=0.00..8.14 rows=10 width=2)
first i thought this might be an psql client error and tried the same via
jdbc, and look, there it happens again. if i create a PreparedStatemnt and
bind the INT or LONG value with setLong (1,x) the index won't be used in the
select statement. if i bind the value with a setString (1,x+"") command,
then the index is honored correctly. I tested the code against postgres
7.1.3 as well as 7.0.2. this means that i would have to change all my java
code from setLong to setString in order to speed up my apps every time i
query a number. quite ugly!
ilker -)
--
--
gate5 AG
schoenhauser allee 62
10437 berlin
fon + 49 30 446 76 0
fax + 49 30 446 76 555
http://www.gate5.de/ | ilker@gate5.de
--
--
gate5 AG
schoenhauser allee 62
10437 berlin
fon + 49 30 446 76 0
fax + 49 30 446 76 555
http://www.gate5.de/ | ilker@gate5.de
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: ilker@gate5.de
Subject: Re: problem: index on number not honoured
In-Reply-To: <9spaa8$inv$1@news.tht.net>
* 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