Re: query optimization with UDFs

On Mon, 2006-10-09 at 22:49 -0400, jungmin shin wrote:
> Does anybody know what the Postgres does for optimizing the queries
> with UDFs?

The optimizer considers function volatility to avoid reevaluating UDFs
needlessly, and to use index scans on predicates involving a function.
Also, functions defined in the SQL language will be inlined into the
function call site, when possible. That's all that comes to mind at the
moment...

Notably, the optimizer doesn't have support for realistic costing of
UDFs: it can't tell how expensive evaluating a UDF is going to be, nor
the number and distribution of the rows that will be produced by a
set-returning function. The Berkeley release of Postgres supported
"expensive function optimization" (a.k.a xfunc), which was an
implementation of Hellerstein and Stonebraker's work on "Predicate
Migration"[1]. That code is no longer in the current Postgres source.

BTW, I think it would make sense to implement a limited subset of the
xfunc ideas: add options to CREATE FUNCTION to allow cost information to
be specified, and then take advantage of this information instead of
using the existing constant kludges. This would be a tangible
improvement, and would have minimal impact on the planner. A further
improvement would be predicate migration: the xfunc code modified the
optimizer to consider pulling predicates up above joins in the query
plan. The traditional assumption is that all predicates are cheap, so
they are always pushed down in the hopes that this will reduce the size
of intermediate result sets. Naturally, this is not always wise with
expensive UDFs. After the basics have been done, it might be interesting
to implement this, provided it can be done without increasing the
planner's search space too much.

-Neil

[1] http://db.cs.berkeley.edu/jmh/miscpapers/sigmod93.pdf