Re: [HACKERS] indexes and floats

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> The parser is converting this query to become
>   select x from f8 where x = float8(500);

> The problem appears to be that the optimizer/executor does not know how
> to evaluate the constant string once-only, and insists on doing a
> sequential scan for some reason.

Ah, it's finally starting to make some sense to me.  What you're saying
is that this is a failure to do constant-folding.

Doing a sequential scan would be appropriate if the righthand side of
the comparison needed to be evaluated afresh at each row.  If the
optimizer thinks that, then that explains a lot.

The question then is why the righthand side doesn't look like a
constant.  I'd have expected that any expression not involving a table
attribute would be evaluated once (folded into a constant) before any
decisions are made on how to perform the scan.  Is that reasonable, or
is there some aspect of SQL semantics that makes it wrong?

If it is supposed to be happening, could it be that float8() is for
some reason not marked as a safely foldable function?

While I'm asking dumb questions: are "float8(500)" and "500::float8"
treated differently?  Actually, I can see that they are:

    play=> explain select x from f8 where x = 500::float8;
    NOTICE:  QUERY PLAN:
    Index Scan using f8_i on f8  (cost=2.05 size=1 width=8)

    play=> explain select x from f8 where x = float8(500);
    NOTICE:  QUERY PLAN:
    Seq Scan on f8  (cost=40.00 size=100 width=8)

But why?  Is there a difference in semantics?

            regards, tom lane