Re: index is not used

mila boldareva <pierro@xxxxxx> writes:
> ... but the index on real field3 is used only when I
> ask ordering, and a query like below remains seq. scan:

>     trc=# explain select * from mytable where field3 = 0.1 limit 1;

Unadorned "0.1" will be taken as a float8 (double precision) constant,
and the system is not currently smart enough to convert a cross-datatype
comparison (viz, float4 vs float8) into an indexscan.  You can either
change the field type to double precision or write an explicit coercion:

        select * from mytable where field3 = 0.1::real limit 1;

A hack that some people use instead is to quote the constant, even
though it's numeric:

        select * from mytable where field3 = '0.1' limit 1;

It turns out that this causes the system to postpone assigning a
specific type to the constant until late enough in processing the query
that it knows float4 is the best choice instead of float8.

This is basically the same story as for int2 and int8 columns.  It's
pretty ugly, and fixing it is on the TODO list.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html