Re: [HACKERS] indexes and floats

> >> Is this a bug that the index doesn't work on floats or is it a
> >> datatype mismatch thing?
> >> Seq Scan on foo (cost=0.00 size=0 width=8)
> The table consists of city, state (both text), lon and lat (both
> float4). The text index is working but not the floats. I have another
> table with 12,000 - 14,000 rows and I'm getting the same thing.

Hmm. You are right. I'm glad you insisted on pursuing this, but I might
have been able to see the problem right away if you had described it in
detail (e.g. "floats" is not clearly float4) and if your initial example
had actually been with float4 data (it showed a width of 8 which is
likely to be a float8) :/

Anyway, the problem seems to be specific to float4. The workaround is to
use float8 for now, and we'll look into fixing the problem for the next
release...

- Tom

Test cases from the regression database follow:

regression=> create table test (f4 float4);
CREATE
regression=> insert into test select seqno from bt_f8_heap;
INSERT 0 10000
regression=> create index ti on test using btree (f4);
CREATE
regression=> vacuum;
VACUUM
regression=> explain select f4 from test where f4 = 500.0;
NOTICE: QUERY PLAN:

Seq Scan on test (cost=394.00 size=1 width=4)

EXPLAIN

regression=> create table test8 as select seqno from bt_f8_heap;
SELECT
regression=> create index t8 on test8 using btree (seqno);
CREATE
regression=> vacuum;
VACUUM
regression=> explain select seqno from test8 where seqno = 500.0;
NOTICE: QUERY PLAN:

Index Scan using t8 on test8 (cost=2.05 size=1 width=8)

EXPLAIN