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@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