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.
On Fri, Jan 27, 2012 at 11:36 AM, Carlo Stonebanks
> 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
> 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
>> 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.
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
In response to
pgsql-performance by date
|Next:||From: Heikki Linnakangas||Date: 2012-01-27 19:56:53|
|Subject: Re: Postgress is taking lot of CPU on our embedded hardware.|
|Previous:||From: Carlo Stonebanks||Date: 2012-01-27 18:36:49|
|Subject: Re: pl/pgsql functions outperforming sql ones?|