Re: [HACKERS] indexes and floats

> "Thomas G. Lockhart" <lockhart(at)alumni(dot)caltech(dot)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.

Yep. I believe it happens in the executor, but doesn't appear to happen
in the optimizer at a time when it would be useful. You can create
functional indexes, and I think they are matched by the function, just
not constants.

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

Yep. I think that is the issue, and index matching does not
pre-evaluate a function on a constant.

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

Sure. In the :: case (or CAST (const AS type)), the parser actually
converts the type INSIDE the parser to the proper type. In the float8()
case, the value conversion is delayed until the executor.

I may be wrong in some of this, but that is what I think is happening.

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)