From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: 'Deron' <fecastle@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: Fri, 27 Jan 2012 14:59:08 -0500
Message-ID: <AC44B48EF940431080DDB91DAD664207@CAPRICA> (raw)
In-Reply-To: <CAF3Lvs4mekjgv4u6u464M6cRhz6hjXHFnJxF=grDe2fAn6_OiQ@mail.gmail.com>
References: <CE4615C16F2F41CAB0E67D4099BF20D2@CAPRICA>
<CAHyXU0wSjcc3vZ1__1wuUSmM1jrToMBAUj_5BErHda48oqZaoQ@mail.gmail.com>
<90C49E334DFE48C582A3214BA56E1DD4@CAPRICA>
<CAF3Lvs4mekjgv4u6u464M6cRhz6hjXHFnJxF=grDe2fAn6_OiQ@mail.gmail.com>
Was I even right in thinking I would gain any performance by converting to
SQL?
-----Original Message-----
From: Deron [mailto:fecastle@gmail.com]
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 - 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. That said, I don't think that's what's happening here.
> Did you confirm the performance difference outside of EXPLAIN ANALYZE?
> In 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
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, fecastle@gmail.com
Subject: Re: pl/pgsql functions outperforming sql ones?
In-Reply-To: <AC44B48EF940431080DDB91DAD664207@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