From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
To: "'Deron'" <fecastle@gmail.com>
Cc: <pgsql-performance@postgresql.org>
References: 
 <CE4615C16F2F41CAB0E67D4099BF20D2@CAPRICA><CAHyXU0wSjcc3vZ1__1wuUSmM1jrToMBAUj_5BErHda48oqZaoQ@mail.gmail.com><90C49E334DFE48C582A3214BA56E1DD4@CAPRICA>
	<CAF3Lvs4mekjgv4u6u464M6cRhz6hjXHFnJxF=grDe2fAn6_OiQ@mail.gmail.com>
In-Reply-To: 
 <CAF3Lvs4mekjgv4u6u464M6cRhz6hjXHFnJxF=grDe2fAn6_OiQ@mail.gmail.com>
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: Fri, 27 Jan 2012 14:59:08 -0500
Message-ID: <AC44B48EF940431080DDB91DAD664207@CAPRICA>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AczdKfMrffLFPMKcR/m7MD0XtM7aBgAA+mKw

Was I even right in thinking I would gain any performance by converting =
to
SQL?

-----Original Message-----
From: Deron [mailto:fecastle@gmail.com]=20
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 =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.
>
> -----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. =A0That said, I don't think that's what's happening =
here.
> Did you confirm the performance difference outside of EXPLAIN ANALYZE?
> =A0In 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