Re: pl/pgsql functions outperforming sql ones?

On Sat, Jan 28, 2012 at 11:20 PM, Carlo Stonebanks
<stonec(dot)register(at)sympatico(dot)ca> wrote:
> Update: The main stored function in question and all of its sub
> sub-functions were recoded to new pure sql functions.
>
> I then stub tested the sub functions sql vs. plpgsql.
>
> Here were the results for new sql vs old plpgsql:
>
> Individual sub functions tested 20-30% faster
>
> But the main function calling new sql sub functions ran 100% slower
>
> So I tried this:
>
> I modified the old plpgsql function to call the new sql sub functions.
>
> THAT ran 20-30% faster then the unmodified version.
>
> That modified function is listed below. All the functions ending in 2 are
> the new SQL versions.

One advantage of PL/pgsql for code like this is that you can compute
values once and save them in variables. SQL doesn't have variables,
so you can end up repeating the same SQL in multiple places (causing
multiple evaluation), or even if you manage to avoid that, the system
can inline things in multiple places and produce the same effect.

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