Was I even right in thinking I would gain any performance by converting to SQL? -----Original Message----- From: Deron [mailto:fecastle@xxxxxxxxx] Sent: January 27, 2012 2:29 PM To: Carlo Stonebanks Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxx> 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@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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
