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

Re: pl/pgsql functions outperforming sql ones?

From: "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: 2012-01-27 20:09:26
Message-ID: 5A6D37A1-9FD9-4011-B61E-FA383CA43B7E@themactionfaction.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Jan 27, 2012, at 2:59 PM, Carlo Stonebanks wrote:

> Was I even right in thinking I would gain any performance by converting to
> SQL?

As always, it depends. I converted an immutable pl/pgsql function to an SQL function and the body of the function barely changed. However, I experienced an order-of-magnitude speed-up because the SQL function could be folded into the plan (like a view) while a pl/pgsql function will never be folded (and the planner punts and assumes the function will return 100 rows for set-returning functions). However, not all SQL functions can be folded into the plan.

On the other hand, a pl/pgsql function can make use of memoization for number-crunching routines and make business-logical short-circuiting decisions.

Cheers,
M

In response to

pgsql-performance by date

Next:From: Claudio FreireDate: 2012-01-28 02:23:45
Subject: Re: Postgress is taking lot of CPU on our embedded hardware.
Previous:From: Carlo StonebanksDate: 2012-01-27 19:59:08
Subject: Re: pl/pgsql functions outperforming sql ones?

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