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

On Wed, Aug 11, 2010 at 5:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> In theory, the optimization Brian wants is possible here, right?  I
>> mean, you could replace the functional call with a Param, and pull the
>> Param out and make it an InitPlan.  Seems like that would generally be
>> a win, if you figure to loop more than once and the execution cost of
>> the function is not too tiny.
>
> 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? 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, so I
feel like we have the necessary information when we're constructing
the RelOptInfo. The startup cost is so trivial that I can't see
generating mutiple paths for it; I think you could just make a local
decision whether to apply the optimization or not.

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