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

> > "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.
>
>
> 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@candle.pha.pa.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)