Re: index on numbers not honoured

Hi Ilker!

I've tried this:

create table test(a int4);
copy test from stdin;
2364786
324587
2348409
1298
34980
423498
23
453587
3948
\.
create index i on test(a);
and found that "select ... ='5'" and "select ... =5" use seq scan too.
I think it is because the planner/optimizer thinks that this question is
too simple for using the index (maybe I am wrong).
However, the simplest way to force pg to use index (if you 're sure that
it is faster) to set the variable enable_seqscan off. (imho it's nicer
than setString..)
I use 7.1.1 on a Debian potato.

On Mon, 12 Nov 2001, 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,