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:

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)

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 Thu, 15 Nov 2001, Ilker Egilmez wrote:

hi risko,
try to create your table, then create your index and then insert your values.
this time the index will be used - very strange!
ilker -)

I summed it:

show enable_seqscan;
show enable_indexscan;
drop index i;
drop table test;
create table test(a int4);
create index i on test(a);
copy test from stdin;
65
87
23
\.
explain select * from test where a=87;
drop index i;
explain select * from test where a=87;
create index i on test (a);
explain select * from test where a=87;

I experienced it too, first indexed, but then seqscan and seqscan again.
I think it is because the query optimizer. I advise you to control
manually in these cases the behavior of it by setting the enable_seqscan
variable (I often do it :).

On Fri, 16 Nov 2001, Risko Peter wrote:

On Thu, 15 Nov 2001, Ilker Egilmez wrote:

hi risko,
try to create your table, then create your index and then insert your values.
this time the index will be used - very strange!
ilker -)

I summed it:

show enable_seqscan;
show enable_indexscan;
drop index i;
drop table test;
create table test(a int4);
create index i on test(a);
copy test from stdin;
65
87
23
\.
explain select * from test where a=87;
drop index i;
explain select * from test where a=87;
create index i on test (a);
explain select * from test where a=87;

I experienced it too, first indexed, but then seqscan and seqscan again.
I think it is because the query optimizer. I advise you to control
manually in these cases the behavior of it by setting the enable_seqscan
variable (I often do it :).

Well, you're going to need a vacuum analyze in there between the copy
and first select to get something meaningful. The reason it probably uses
an index on the first one is that the default statistics will choose that.
However, that is almost certainly the *wrong* plan for all three of those
queries. Choosing from a table that's one page should almost certainly
use sequence scan and choosing one row of three should in most cases
as well (see past discussions for details)