Re: [HACKERS] indexes and floats - Mailing list pgsql-hackers

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>> 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.

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

Ah-hah, all of a sudden this looks *real* familiar.  I bet it's because
pgsql is not noticing that "500.0" can be interpreted as a float4.
Let's try it.

play=> create table f8 (x float8);
CREATE
play=> \copy f8 from countup;            // 0..999 in a text file
Successfully copied.
play=> create index f8_i on f8 using btree (x);
CREATE
play=> create table f4 (x float4);
CREATE
play=> \copy f4 from countup;
Successfully copied.
play=> create index f4_i on f4 using btree (x);
CREATE
play=> vacuum verbose analyze;
.... blah blah
play=> explain select x from f8 where x = 500;
NOTICE:  QUERY PLAN:

Seq Scan on f8  (cost=40.00 size=100 width=8)

EXPLAIN
play=> explain select x from f8 where x = 500.0;
NOTICE:  QUERY PLAN:

Index Scan using f8_i on f8  (cost=2.05 size=1 width=8)

EXPLAIN
play=> explain select x from f4 where x = 500;
NOTICE:  QUERY PLAN:

Seq Scan on f4  (cost=40.00 size=100 width=4)

EXPLAIN
play=> explain select x from f4 where x = 500.0;
NOTICE:  QUERY PLAN:

Seq Scan on f4  (cost=40.00 size=1 width=4)

EXPLAIN
play=> explain select x from f4 where x = 500 :: float4;
NOTICE:  QUERY PLAN:

Index Scan using f4_i on f4  (cost=2.05 size=1 width=4)

EXPLAIN
play=> explain select x from f4 where x = 500.0 :: float4;
ERROR:  parser_typecast: cannot cast this expression to type 'float4'
play=>

(This is with cvs sources of a few days ago.)

I see two different bugs here:

1. select ... where x = constant; is optimized to an index scan
only if the constant is of the exact type of the field x.
Apparently, the coercion of the constant to match the type of x
happens only after the optimizer decides it doesn't know what to do.
The coercion ought to happen *before* the optimizer runs.
(I have no idea whether this is a new bug caused by the recent
type-system changes, or whether it existed in 6.3.2 and earlier.)

2. Type coercion fails for "500.0 :: float4" (ditto for "500.0 :: float8"
btw).  Presumably this is a simple localized bug in the parser or the type
coercion logic for floats.

I had previously complained of bug #1 in connection with OIDs;
with the present sources, "where oid = 123456" will not use
an index on OID, while "where oid = 123456::oid" will.

I will bet lunch (at the nearest McD's, I'm not rich ;-)) that
Vince Vielhaber's recent gripe about
    select city from locations where lower(city) = lower('st. ignace');
failing to use an index
    create index locations_city on locations(lower(city) text_ops);
is an artifact of the same sort of type-mismatch problem.

            regards, tom lane