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

Wed, 5 Aug 1998 15:17:27 -0500
"Jackson, DeJuan" <djackson@cpsgroup.com>
RE: [HACKERS] indexes and floats
> 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.

As you point out, the current behavior allows functions with
side-effects to work, where if we change it then functions must be
side-effect-free. Most functions do not have side effects, though one
using the SPI interface for example might be prone to them.

We _could_ add a column attribute for each function which declares a
function as being "safe" or not.

                      - Tom

Thu, 06 Aug 1998 05:59:48 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] indexes and floats