Re: pl/pgsql functions outperforming sql ones?

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 barely changed. 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 return 100 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-logical short-circuiting decisions.

Cheers,
M