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

On Wed, Aug 11, 2010 at 5:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> In theory, the optimization Brian wants is possible here, right? =A0I
>> mean, you could replace the functional call with a Param, and pull the
>> Param out and make it an InitPlan. =A0Seems 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. =A0It 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.

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