Thread: pl/pgsql functions outperforming sql ones?

pl/pgsql functions outperforming sql ones?

From
"Carlo Stonebanks"
Date:

Assuming there was some sort of cost to pl/pgsql, I rewrote a bunch of stored functions s in straight SQL. Each stored proc was calling the next, so to get the full effect I had to track down all the pl/pgsql stored functions and convert them to sql. However, I was surprised to find after all of the rewrites, the LANGUAGE sql procs caused the queries to run slower than the LANGUAGE plpgsql.

 

None of the stored functions selected from tables, the operated on and returned scalar values - it was all assign variables, if/then/else - not even any looping.

 

For those who need the dirty details, here they are. If you happen to think this behavior is expected, I needn’t bore you – just let me know!

 

Thanks,

 

Carlo

 

This was all triggered during the optimization of a query like this:

 

SELECT myVar

FROM myTable

WHERE myFunc(myVar);

 

Looking at EXPLAIN ANALYSE I saw something like this:

 

Filter: myFunc(myVar)

 

I rewrote the body of myFunc(myVar) something like this:

 

SELECT CASE WHEN myVar IS NULL THEN false ELSE myOtherFunc(myVar) END

 

When I reran EXPLAIN ANALYZE I got this:

 

Filter: SELECT CASE WHEN myVar IS NULL THEN false ELSE myOtherFunc(myVar) END

 

Nice. So, I did the same treatment to myOtherFunc() (converted to straight sql) but the EXPLAIN ANALYZE didn’t change (reasonable, I guess – how deep would I expect it to go?)

 

All of the procs were IMMUTABLE.

 

I was very surprised to find that the query now ran much slower by a factor of 4.

 

 

Re: pl/pgsql functions outperforming sql ones?

From
Merlin Moncure
Date:
On Thu, Jan 26, 2012 at 6:09 PM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
> Assuming there was some sort of cost to pl/pgsql, I rewrote a bunch of
> stored functions s in straight SQL. Each stored proc was calling the next,
> so to get the full effect I had to track down all the pl/pgsql stored
> functions and convert them to sql. However, I was surprised to find after
> all of the rewrites, the LANGUAGE sql procs caused the queries to run slower
> than the LANGUAGE plpgsql.

One reason that plpgsql can outperform sql functions is that plpgsql
caches plans.  That said, I don't think that's what's happening here.
Did you confirm the performance difference outside of EXPLAIN ANALYZE?
 In particular cases EXPLAIN ANALYZE can skew times, either by
injecting time calls or in how it discards results.

merlin

Re: pl/pgsql functions outperforming sql ones?

From
"Carlo Stonebanks"
Date:
Yes, I did test it  - i.e. I ran the functions on their own as I had always
noticed a minor difference between EXPLAIN ANALYZE results and direct query
calls.

Interesting, so sql functions DON'T cache plans? Will plan-caching be of any
benefit to SQL that makes no reference to any tables? The SQL is emulating
the straight non-set-oriented procedural logic of the original plpgsql.

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: January 27, 2012 10:47 AM
To: Carlo Stonebanks
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

On Thu, Jan 26, 2012 at 6:09 PM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
> Assuming there was some sort of cost to pl/pgsql, I rewrote a bunch of
> stored functions s in straight SQL. Each stored proc was calling the next,
> so to get the full effect I had to track down all the pl/pgsql stored
> functions and convert them to sql. However, I was surprised to find after
> all of the rewrites, the LANGUAGE sql procs caused the queries to run
slower
> than the LANGUAGE plpgsql.

One reason that plpgsql can outperform sql functions is that plpgsql
caches plans.  That said, I don't think that's what's happening here.
Did you confirm the performance difference outside of EXPLAIN ANALYZE?
 In particular cases EXPLAIN ANALYZE can skew times, either by
injecting time calls or in how it discards results.

merlin


Re: pl/pgsql functions outperforming sql ones?

From
Deron
Date:
You can use PREPARE... EXECUTE to "cache" the plan (as well as
parsing).   However, I find it unlikely this will would explain the
loss in performance you experienced.

Deron


On Fri, Jan 27, 2012 at 11:36 AM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
> Yes, I did test it  - i.e. I ran the functions on their own as I had always
> noticed a minor difference between EXPLAIN ANALYZE results and direct query
> calls.
>
> Interesting, so sql functions DON'T cache plans? Will plan-caching be of any
> benefit to SQL that makes no reference to any tables? The SQL is emulating
> the straight non-set-oriented procedural logic of the original plpgsql.
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure@gmail.com]
> Sent: January 27, 2012 10:47 AM
> To: Carlo Stonebanks
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?
>
> On Thu, Jan 26, 2012 at 6:09 PM, Carlo Stonebanks
> <stonec.register@sympatico.ca> wrote:
>> Assuming there was some sort of cost to pl/pgsql, I rewrote a bunch of
>> stored functions s in straight SQL. Each stored proc was calling the next,
>> so to get the full effect I had to track down all the pl/pgsql stored
>> functions and convert them to sql. However, I was surprised to find after
>> all of the rewrites, the LANGUAGE sql procs caused the queries to run
> slower
>> than the LANGUAGE plpgsql.
>
> One reason that plpgsql can outperform sql functions is that plpgsql
> caches plans.  That said, I don't think that's what's happening here.
> Did you confirm the performance difference outside of EXPLAIN ANALYZE?
>  In particular cases EXPLAIN ANALYZE can skew times, either by
> injecting time calls or in how it discards results.
>
> merlin
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Re: pl/pgsql functions outperforming sql ones?

From
"Carlo Stonebanks"
Date:
Was I even right in thinking I would gain any performance by converting to
SQL?

-----Original Message-----
From: Deron [mailto:fecastle@gmail.com]
Sent: January 27, 2012 2:29 PM
To: Carlo Stonebanks
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

You can use PREPARE... EXECUTE to "cache" the plan (as well as
parsing).   However, I find it unlikely this will would explain the
loss in performance you experienced.

Deron


On Fri, Jan 27, 2012 at 11:36 AM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
> Yes, I did test it  - i.e. I ran the functions on their own as I had
always
> noticed a minor difference between EXPLAIN ANALYZE results and direct
query
> calls.
>
> Interesting, so sql functions DON'T cache plans? Will plan-caching be of
any
> benefit to SQL that makes no reference to any tables? The SQL is emulating
> the straight non-set-oriented procedural logic of the original plpgsql.
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure@gmail.com]
> Sent: January 27, 2012 10:47 AM
> To: Carlo Stonebanks
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?
>
> On Thu, Jan 26, 2012 at 6:09 PM, Carlo Stonebanks
> <stonec.register@sympatico.ca> wrote:
>> Assuming there was some sort of cost to pl/pgsql, I rewrote a bunch of
>> stored functions s in straight SQL. Each stored proc was calling the
next,
>> so to get the full effect I had to track down all the pl/pgsql stored
>> functions and convert them to sql. However, I was surprised to find after
>> all of the rewrites, the LANGUAGE sql procs caused the queries to run
> slower
>> than the LANGUAGE plpgsql.
>
> One reason that plpgsql can outperform sql functions is that plpgsql
> caches plans.  That said, I don't think that's what's happening here.
> Did you confirm the performance difference outside of EXPLAIN ANALYZE?
>  In particular cases EXPLAIN ANALYZE can skew times, either by
> injecting time calls or in how it discards results.
>
> merlin
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


Re: pl/pgsql functions outperforming sql ones?

From
"A.M."
Date:
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

Re: pl/pgsql functions outperforming sql ones?

From
Pavel Stehule
Date:
2012/1/27 Carlo Stonebanks <stonec.register@sympatico.ca>:
> Yes, I did test it  - i.e. I ran the functions on their own as I had always
> noticed a minor difference between EXPLAIN ANALYZE results and direct query
> calls.
>
> Interesting, so sql functions DON'T cache plans? Will plan-caching be of any
> benefit to SQL that makes no reference to any tables? The SQL is emulating
> the straight non-set-oriented procedural logic of the original plpgsql.
>

It is not necessary usually - simple SQL functions are merged to outer
query - there are e few cases where this optimization cannot be
processed and then there are performance lost.

For example this optimization is not possible (sometimes) when some
parameter is volatile

Regards

Pavel Stehule

Re: pl/pgsql functions outperforming sql ones?

From
"Carlo Stonebanks"
Date:
Pavel, are you saying that the code of the stored function is actually being
added to the SQL query, instead of a call to it? For example, I have seen
this:

SELECT myVar
FROM myTable
WHERE myVar > 0 AND myFunc(myVar)

And seen the SQL body of myVar appended to the outer query:

... Filter: SELECT CASE WHERE myVar < 10 THEN true ELSE false END

Is this what we are talking about? Two questions:

1) Is this also done when the function is called as a SELECT column;
   e.g. would:
      SELECT myFunc(myVar) AS result
   - become:
      SELECT (
         SELECT CASE WHERE myVar < 10 THEN true ELSE false END
      ) AS result?

2) Does that not bypass the benefits of IMMUTABLE?



-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: January 28, 2012 1:38 AM
To: Carlo Stonebanks
Cc: Merlin Moncure; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

2012/1/27 Carlo Stonebanks <stonec.register@sympatico.ca>:
> Yes, I did test it  - i.e. I ran the functions on their own as I had
always
> noticed a minor difference between EXPLAIN ANALYZE results and direct
query
> calls.
>
> Interesting, so sql functions DON'T cache plans? Will plan-caching be of
any
> benefit to SQL that makes no reference to any tables? The SQL is emulating
> the straight non-set-oriented procedural logic of the original plpgsql.
>

It is not necessary usually - simple SQL functions are merged to outer
query - there are e few cases where this optimization cannot be
processed and then there are performance lost.

For example this optimization is not possible (sometimes) when some
parameter is volatile

Regards

Pavel Stehule

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Re: pl/pgsql functions outperforming sql ones?

From
Pavel Stehule
Date:
Hello

2012/1/30 Carlo Stonebanks <stonec.register@sympatico.ca>:
> Pavel, are you saying that the code of the stored function is actually being
> added to the SQL query, instead of a call to it? For example, I have seen
> this:
>
> SELECT myVar
> FROM myTable
> WHERE myVar > 0 AND myFunc(myVar)
>
> And seen the SQL body of myVar appended to the outer query:
>
> ... Filter: SELECT CASE WHERE myVar < 10 THEN true ELSE false END
>
> Is this what we are talking about? Two questions:

yes - it is SQL function "inlining"

>
> 1) Is this also done when the function is called as a SELECT column;
>   e.g. would:
>      SELECT myFunc(myVar) AS result
>   - become:
>      SELECT (
>         SELECT CASE WHERE myVar < 10 THEN true ELSE false END
>      ) AS result?
>

yes

CREATE OR REPLACE FUNCTION public.fx(integer, integer)
 RETURNS integer
 LANGUAGE sql
AS $function$
select coalesce($1, $2)
$function$

postgres=# explain verbose select fx(random()::int, random()::int);
                          QUERY PLAN
--------------------------------------------------------------
 Result  (cost=0.00..0.02 rows=1 width=0)
   Output: COALESCE((random())::integer, (random())::integer)
(2 rows)


> 2) Does that not bypass the benefits of IMMUTABLE?
>

no - optimizator works with expanded query - usually is preferred
style a writing SQL functions without flags, because optimizer can
work with definition of SQL function and can set well flags. SQL
function is not black box for optimizer like plpgsql does. And SQL
optimizer chooses a inlining or some other optimizations. Sometimes
explicit flags are necessary, but usually not for scalar SQL
functions.

postgres=# create or replace function public.fxs(int)
postgres-# returns setof int as $$
postgres$# select * from generate_series(1,$1)
postgres$# $$ language sql;
CREATE FUNCTION
postgres=# explain verbose select * from fxs(10);
                            QUERY PLAN
-------------------------------------------------------------------
 Function Scan on public.fxs  (cost=0.25..10.25 rows=1000 width=4)
   Output: fxs
   Function Call: fxs(10)
(3 rows)

postgres=# create or replace function public.fxs(int)
returns setof int as $$
select * from generate_series(1,$1)
$$ language sql IMMUTABLE;
CREATE FUNCTION
postgres=# explain verbose select * from fxs(10);
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series  (cost=0.00..10.00
rows=1000 width=4)
   Output: generate_series.generate_series
   Function Call: generate_series(1, 10) --<<<< inlined query
(3 rows)

Regards

Pavel Stehule

>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Pavel Stehule
> Sent: January 28, 2012 1:38 AM
> To: Carlo Stonebanks
> Cc: Merlin Moncure; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?
>
> 2012/1/27 Carlo Stonebanks <stonec.register@sympatico.ca>:
>> Yes, I did test it  - i.e. I ran the functions on their own as I had
> always
>> noticed a minor difference between EXPLAIN ANALYZE results and direct
> query
>> calls.
>>
>> Interesting, so sql functions DON'T cache plans? Will plan-caching be of
> any
>> benefit to SQL that makes no reference to any tables? The SQL is emulating
>> the straight non-set-oriented procedural logic of the original plpgsql.
>>
>
> It is not necessary usually - simple SQL functions are merged to outer
> query - there are e few cases where this optimization cannot be
> processed and then there are performance lost.
>
> For example this optimization is not possible (sometimes) when some
> parameter is volatile
>
> Regards
>
> Pavel Stehule
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: pl/pgsql functions outperforming sql ones?

From
"Carlo Stonebanks"
Date:
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.

Any thoughts or insight would be much appreciated.

Carlo


CREATE OR REPLACE FUNCTION mdx_lib.lex_compare_candidate3(character varying,
character varying)
  RETURNS numeric AS
$BODY$
/*
Rate two strings candidacy for lex_compare.
param 1: first string to compare
param 2: 2nd string to compare
returns: numeric result like mdx_lib.lex_distance
0 is a failure, 1 a perfect match
*/
declare
   str1 varchar = $1;
   str2 varchar = $2;
   acro1 varchar;
   acro2 varchar;
   str_dist numeric;
   acro_dist numeric;
   result numeric;
begin
   if str1 = str2 then
      result = 0;
   else
      str1 = lower(regexp_replace(str1, '[^[:alnum:]]', '', 'g'));
      str2 = lower(regexp_replace(str2, '[^[:alnum:]]', '', 'g'));

      if str1 = str2 then
         result = 0.1;
      else
         str_dist = mdx_lib.lex_distance2(str1, str2);
         acro1 = mdx_lib.lex_acronym2(str1);
         acro2 = mdx_lib.lex_acronym2(str2);
         acro_dist = mdx_lib.lex_distance2(acro1, acro2);
         result = (acro_dist + (str_dist * 2)) / 2;
      end if;
   end if;

   result = 1 - result;
   if result < 0 then
      result = 0;
   end if;
   return result;
end;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;



-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: January 28, 2012 1:38 AM
To: Carlo Stonebanks
Cc: Merlin Moncure; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

2012/1/27 Carlo Stonebanks <stonec.register@sympatico.ca>:
> Yes, I did test it  - i.e. I ran the functions on their own as I had
always
> noticed a minor difference between EXPLAIN ANALYZE results and direct
query
> calls.
>
> Interesting, so sql functions DON'T cache plans? Will plan-caching be of
any
> benefit to SQL that makes no reference to any tables? The SQL is emulating
> the straight non-set-oriented procedural logic of the original plpgsql.
>

It is not necessary usually - simple SQL functions are merged to outer
query - there are e few cases where this optimization cannot be
processed and then there are performance lost.

For example this optimization is not possible (sometimes) when some
parameter is volatile

Regards

Pavel Stehule

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Re: pl/pgsql functions outperforming sql ones?

From
"Carlo Stonebanks"
Date:
Pavel, thank you very much for your explanation.

Is it possible to define under what conditions that sql procs will
outperform plpgsql ones, and vice-versa?

-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: January 30, 2012 2:57 AM
To: Carlo Stonebanks
Cc: Merlin Moncure; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

Hello

2012/1/30 Carlo Stonebanks <stonec.register@sympatico.ca>:
> Pavel, are you saying that the code of the stored function is actually
being
> added to the SQL query, instead of a call to it? For example, I have seen
> this:
>
> SELECT myVar
> FROM myTable
> WHERE myVar > 0 AND myFunc(myVar)
>
> And seen the SQL body of myVar appended to the outer query:
>
> ... Filter: SELECT CASE WHERE myVar < 10 THEN true ELSE false END
>
> Is this what we are talking about? Two questions:

yes - it is SQL function "inlining"

>
> 1) Is this also done when the function is called as a SELECT column;
>   e.g. would:
>      SELECT myFunc(myVar) AS result
>   - become:
>      SELECT (
>         SELECT CASE WHERE myVar < 10 THEN true ELSE false END
>      ) AS result?
>

yes

CREATE OR REPLACE FUNCTION public.fx(integer, integer)
 RETURNS integer
 LANGUAGE sql
AS $function$
select coalesce($1, $2)
$function$

postgres=# explain verbose select fx(random()::int, random()::int);
                          QUERY PLAN
--------------------------------------------------------------
 Result  (cost=0.00..0.02 rows=1 width=0)
   Output: COALESCE((random())::integer, (random())::integer)
(2 rows)


> 2) Does that not bypass the benefits of IMMUTABLE?
>

no - optimizator works with expanded query - usually is preferred
style a writing SQL functions without flags, because optimizer can
work with definition of SQL function and can set well flags. SQL
function is not black box for optimizer like plpgsql does. And SQL
optimizer chooses a inlining or some other optimizations. Sometimes
explicit flags are necessary, but usually not for scalar SQL
functions.

postgres=# create or replace function public.fxs(int)
postgres-# returns setof int as $$
postgres$# select * from generate_series(1,$1)
postgres$# $$ language sql;
CREATE FUNCTION
postgres=# explain verbose select * from fxs(10);
                            QUERY PLAN
-------------------------------------------------------------------
 Function Scan on public.fxs  (cost=0.25..10.25 rows=1000 width=4)
   Output: fxs
   Function Call: fxs(10)
(3 rows)

postgres=# create or replace function public.fxs(int)
returns setof int as $$
select * from generate_series(1,$1)
$$ language sql IMMUTABLE;
CREATE FUNCTION
postgres=# explain verbose select * from fxs(10);
                                    QUERY PLAN
----------------------------------------------------------------------------
-------
 Function Scan on pg_catalog.generate_series  (cost=0.00..10.00
rows=1000 width=4)
   Output: generate_series.generate_series
   Function Call: generate_series(1, 10) --<<<< inlined query
(3 rows)

Regards

Pavel Stehule

>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Pavel Stehule
> Sent: January 28, 2012 1:38 AM
> To: Carlo Stonebanks
> Cc: Merlin Moncure; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?
>
> 2012/1/27 Carlo Stonebanks <stonec.register@sympatico.ca>:
>> Yes, I did test it  - i.e. I ran the functions on their own as I had
> always
>> noticed a minor difference between EXPLAIN ANALYZE results and direct
> query
>> calls.
>>
>> Interesting, so sql functions DON'T cache plans? Will plan-caching be of
> any
>> benefit to SQL that makes no reference to any tables? The SQL is
emulating
>> the straight non-set-oriented procedural logic of the original plpgsql.
>>
>
> It is not necessary usually - simple SQL functions are merged to outer
> query - there are e few cases where this optimization cannot be
> processed and then there are performance lost.
>
> For example this optimization is not possible (sometimes) when some
> parameter is volatile
>
> Regards
>
> Pavel Stehule
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


Re: pl/pgsql functions outperforming sql ones?

From
Pavel Stehule
Date:
2012/1/31 Carlo Stonebanks <stonec.register@sympatico.ca>:
> Pavel, thank you very much for your explanation.
>
> Is it possible to define under what conditions that sql procs will
> outperform plpgsql ones, and vice-versa?

yes, little bit :)

when inlining is possible, then SQL function will be faster - typical
use case is simple scalar functions (with nonvolatile real
parameters).

Regards

Pavel

>
> -----Original Message-----

Re: pl/pgsql functions outperforming sql ones?

From
Robert Haas
Date:
On Sat, Jan 28, 2012 at 11:20 PM, Carlo Stonebanks
<stonec.register@sympatico.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