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