Re: index on numbers not honoured

On Mon, 12 Nov 2001, Ilker Egilmez wrote:

> 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!

The problem is that the constant is being assumed to be int4 in the former
statement and it won't use the index on the int2=int4 case. IIRC, the
second postpones determining the type. The same thing happens on int8
columns as well I think. For int2, you're possibly best off just moving
to int4 :(. There's been talk about trying to do a similar delaying thing
for numeric constants but I think there were difficulties involved (I
think the -general or -hackers archives will have more information)