On Jan 27, 2012, at 2:59 PM, Carlo Stonebanks wrote:
> Was I even right in thinking I would gain any performance by converting to
> SQL?
As always, it depends. I converted an immutable pl/pgsql function to an SQL function and the body of the function
barelychanged. However, I experienced an order-of-magnitude speed-up because the SQL function could be folded into the
plan(like a view) while a pl/pgsql function will never be folded (and the planner punts and assumes the function will
return100 rows for set-returning functions). However, not all SQL functions can be folded into the plan.
On the other hand, a pl/pgsql function can make use of memoization for number-crunching routines and make
business-logicalshort-circuiting decisions.
Cheers,
M