From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
To: "'Pavel Stehule'" <pavel.stehule@gmail.com>
Cc: "'Merlin Moncure'" <mmoncure@gmail.com>,
 <pgsql-performance@postgresql.org>
References: <CE4615C16F2F41CAB0E67D4099BF20D2@CAPRICA>
	<CAHyXU0wSjcc3vZ1__1wuUSmM1jrToMBAUj_5BErHda48oqZaoQ@mail.gmail.com>
	<90C49E334DFE48C582A3214BA56E1DD4@CAPRICA>
	<CAFj8pRCceGDEp1sxvMoe0oaio6P=O2nJPfs9MWgu4-+zCTdrVg@mail.gmail.com>
In-Reply-To: 
 <CAFj8pRCceGDEp1sxvMoe0oaio6P=O2nJPfs9MWgu4-+zCTdrVg@mail.gmail.com>
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: Sat, 28 Jan 2012 23:20:40 -0500
Message-ID: <0238E40E527049828C48675488422F6D@CAPRICA>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Aczdh483IUOBsrbCSdGdWu6mwhjAKQAs7d3Q

Update: The main stored function in question and all of its sub
sub-functions were recoded to new pure sql functions.=20

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 =3D $1;
   str2 varchar =3D $2;
   acro1 varchar;
   acro2 varchar;
   str_dist numeric;
   acro_dist numeric;
   result numeric;
begin
   if str1 =3D str2 then
      result =3D 0;
   else
      str1 =3D lower(regexp_replace(str1, '[^[:alnum:]]', '', 'g'));
      str2 =3D lower(regexp_replace(str2, '[^[:alnum:]]', '', 'g'));

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

   result =3D 1 - result;
   if result < 0 then
      result =3D 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 =A0- 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

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