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.
>
This would be logical to me because we allow function creation.
Consider, if someone creates an SQL function on a table being updated
that returns a count of all rows who's index matches an int. Which
would mean that an insert using that function to select rows could need
to be evaluated for each row.
Well, I guess what I'm trying to say is that the current behavior could
be desired/being used by someone. It could also be used as a substitute
for a select trigger.
Of course as always I could be completely wrong.
-DEJ

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