Re: pl/pgsql functions outperforming sql ones?

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

-----Original Message-----
From: Deron [mailto:fecastle(at)gmail(dot)com]
Sent: January 27, 2012 2:29 PM
To: Carlo Stonebanks
Cc: pgsql-performance(at)postgresql(dot)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(dot)register(at)sympatico(dot)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(at)gmail(dot)com]
> Sent: January 27, 2012 10:47 AM
> To: Carlo Stonebanks
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?
>
> On Thu, Jan 26, 2012 at 6:09 PM, Carlo Stonebanks
> <stonec(dot)register(at)sympatico(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance