Re: How to use index in WHERE int = float

On Wed, Nov 05, 2008 at 02:21:33PM -0700, Scott Marlowe wrote:
> On Wed, Nov 5, 2008 at 11:52 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
> > explain analyze select * from firma2.dok where dokumnr='1228137'::float8
> >
> > How to force PostgreSql to speed up without changing query ?
>
> Stop trying to compare exact and inexact types?
>
> You do realize that a float is not an exact number. What you and I
> see as 1228137 might really be, internally, 1228136.9999999999999999 ?

A double precision number is defined to have 52bits of precision;
which implies that integer values up to 4503599627370496 can be stored
accurately (I'd not recommend relying on this though). In terms of
correctness, what he's doing is OK as the column is a 32bit int and its
complete range can be represented accurately. Yet, from an aesthetic
point of view, the OP is loosing a lot of points here. PG 8.3 would
even throw it out, unless dokumnr was explicitly cast to a float8 as
well.

Complaining about performance when doing this seems somewhat
contradictory! Asking the database to do extra work (in this case,
converting an integer to a floating point number, and then doing a
floating point compare) for no good reason is never good.

Sam