Re: pl/pgsql functions outperforming sql ones?

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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Pavel Stehule
Sent: January 28, 2012 1:38 AM
To: Carlo Stonebanks
Cc: Merlin Moncure; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

2012/1/27 Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)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