Re: index on numbers not honoured

I found that 'bug' too, but only on case of numeric,decimal type but not
in version  7.1.3 and not in case of int,float ... .
(As I know that numeric and decimal are same)

create table test2
    (x1 numeric not null default 0, x2 decimal not null default 1);

crate index t2x1 on test2 (x1);
crate index t2x2 on test2 (x2);

db=# explain select * from test2 where x2=1;
NOTICE:  QUERY PLAN:
Index Scan using t2x2 on test2  (cost=0.00..8.14 rows=10 width=24)

db=# explain select * from test2 where x1=1;
NOTICE:  QUERY PLAN:
Index Scan using t2x1 on test2  (cost=0.00..8.14 rows=10 width=24)


CoL


Ilker Egilmez wrote:

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