pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (3+ messages)  latest in thread

Message-ID: <9spaa8$inv$1@news.tht.net>
Permalink:  ../9spaa8$inv$1@news.tht.net/
Also on:    postgresql.org/message-id/9spaa8$inv$1@news.tht.net

 · 

reply

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