Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Aug 12, 2010 at 10:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, I was thinking in terms of doing it when we do the SRF inlining.
>> It might be that we could get away with just having an arbitrary cost
>> limit like 100*cpu_operator_cost, and not think about how many rows
>> would actually be involved.

> I'm not exactly following this.  My guess is that the breakeven point
> is going to be pretty low because I think Param nodes are pretty
> cheap.

If you have any significant number of executions of the expression, then
of course converting it to an initplan is a win.  What I'm worried about
is where you have just a small number (like maybe only one, if it gets
converted to an indexqual for instance).  Then the added expense of
setting up the initplan isn't going to be repaid.  As long as the
expression is pretty expensive, the percentage overhead of wrapping it
in an initplan will probably be tolerable anyway, but I'm not sure where
the threshold of "pretty expensive" is for that.

> Well, that's certainly a good place to start, but I was thinking that
> it would be nice to optimize things like this:

> SELECT * FROM foo WHERE somecolumn = somefunc();

> This is OK if we choose a straight index scan, but it's probably very
> much worth optimizing if we end up doing anything else.  If that's too
> hairy, then maybe not, but it's not obvious to me why it would be
> expensive.

Because you have to look at every subexpression of every subexpression
to figure out if it's (a) stable and (b) expensive.  Each of those
checks is un-cheap in itself, and if you blindly apply them at every
node of an expression tree the cost will be exponential.

            regards, tom lane