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

On Thu, Aug 12, 2010 at 10:44 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Aug 11, 2010 at 5:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Yeah, possibly.  It would probably be difficult for the planner to
>>> figure out where the cutover point is to make that worthwhile, though;
>>> the point where you'd need to make the transformation is long before we
>>> have any rowcount estimates.
>
>> This may be a stupid question, but why does the transformation have to
>> be done before we have the row count estimates?
>
> 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.

>> I think we're just
>> looking for a scan node with a filter condition that contains a stable
>> subexpression that's expensive enough to be worth factoring out,
>
> I do *not* want to grovel over every subexpression (and
> sub-sub-expression, etc) in a query thinking about whether to do this.
> That gets O(expensive) pretty quickly.  My idea of the appropriate scope
> of a hack like this is just to prevent any performance loss from SRF
> inlining.

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.

> Another approach we could take is to fix the implementation limitation
> in inline_set_returning_function() about punting when there's a
> sub-select in the arguments.  Then users could make this happen for
> themselves when it matters.

Hmm. I'm usually in favor of removing implementation restrictions,
but I'm not too sure about the effects of removing this one. It seems
like it would be nicer to have a solution that didn't require the user
to write the query a certain way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company