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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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
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(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
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

In response to

Responses

Browse pgsql-performance by date

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