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@xxxxxxxxx] Sent: January 27, 2012 10:47 AM To: Carlo Stonebanks Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: pl/pgsql functions outperforming sql ones? On Thu, Jan 26, 2012 at 6:09 PM, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
