From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: 'Pavel Stehule' <pavel.stehule@gmail.com>
Cc: 'Merlin Moncure' <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: Sat, 28 Jan 2012 23:20:40 -0500
Message-ID: <0238E40E527049828C48675488422F6D@CAPRICA> (raw)
In-Reply-To: <CAFj8pRCceGDEp1sxvMoe0oaio6P=O2nJPfs9MWgu4-+zCTdrVg@mail.gmail.com>
References: <CE4615C16F2F41CAB0E67D4099BF20D2@CAPRICA>
<CAHyXU0wSjcc3vZ1__1wuUSmM1jrToMBAUj_5BErHda48oqZaoQ@mail.gmail.com>
<90C49E334DFE48C582A3214BA56E1DD4@CAPRICA>
<CAFj8pRCceGDEp1sxvMoe0oaio6P=O2nJPfs9MWgu4-+zCTdrVg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stonec.register@sympatico.ca, pavel.stehule@gmail.com, mmoncure@gmail.com
Subject: Re: pl/pgsql functions outperforming sql ones?
In-Reply-To: <0238E40E527049828C48675488422F6D@CAPRICA>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox