Re: [HACKERS] indexes and floats

>
>    > Sorry, but maybe it would be better to add new attribute
>    > to pg_proc (and change CREATE FUNCTION syntax) to let
>    > parser know does result of function call on constants depend
>    > on execution time or not. This would be much better just
>    > execute function in parser and replace function with
>    > constant.
>    > Currently, only random(), now() and SPI-functions should be
>    > replaced by PARAM_EXEC, all others could be evaluated by parser.
>
>    I see.  We have pg_proc.proiscachable.  This looks like a good
>    candidate, and is not used for anything currently.  Maybe rename it to
>    something clearer.
>
> Wouldn't the concept of VOLATILE be relevant here (ala the same
> keyword in C and C++)?  In those languages the keyword influences the
> nature of optimizations that can be done, which seems to be the
> problems involved here.  We could have a function bool
> pg_proc.is_volatile() to determine whether or not the result of the
> function call can be determined from its arguments.  If so and the
> arguments are constants, the function could be evaluated once in the
> parser; otherwise, it would need to be evaluated for each tuple.

In Illustra this is called "variant". A variant function (eg random()) must
be evaluated each time. A nonvariant function can have it's result cached
not just in the parser, and not just for constants, but also for any run of
identical argument values such as might occur in a table scan.

For example assuming projected_price_calc() is an expensive but nonvariant
function and the rows returned from the join are ordered by stock symbol
the following query wins big time if the result of projected_price_calc()
can be cached from one execution to the next.

select symbol, price from stocks, trades
 where trades.symbol = stocks.symbol
   and projected_price_calc(stocks.symbol) < price;

That is, don't think of this as a special case in the parser for doing
constant folding, think of it as a general way of "memoizing" or cacheing
function results. Hence the current name "iscacheble" is actually pretty
good.

Also, perhaps instead of doing constant folding in the parser, consider makeing
it part of rewrite. This pass would just traverse the tree looking for
functions with constant arguements and would pre-evaluate them and and
save the result in the wherever the cacheable results would be stored. No
special case required except that the optimizer would have to notice that
a pre-computed result was available to use as a key value.

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - If simplicity worked, the world would be overrun with insects. -