Skip site navigation (1) Skip section navigation (2)

Re: pl/pgsql functions outperforming sql ones?

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: "'Merlin Moncure'" <mmoncure(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: 2012-01-27 18:36:49
Message-ID: 90C49E334DFE48C582A3214BA56E1DD4@CAPRICA (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.


In response to


pgsql-performance by date

Next:From: DeronDate: 2012-01-27 19:29:05
Subject: Re: pl/pgsql functions outperforming sql ones?
Previous:From: Jayashankar K BDate: 2012-01-27 18:30:00
Subject: Re: Postgress is taking lot of CPU on our embedded hardware.

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group