Re: plpgsql functions organisation - Mailing list pgsql-general

> On 03 May 2015, at 2:56, Melvin Davidson <melvin6925@gmail.com> wrote:
>
> OK, Here is a simple example that shows the difference between using a self contained function  and
> one that calls sub functions.
>
> After loading all the functions below, repeat each of the EXPLAIN statements a few times and note that
> callsubs takes almost TWICE as long to execute as nosub.
>
> CREATE OR REPLACE FUNCTION nosub(text)
>   RETURNS void AS
> $BODY$
...
>     IF LENGTH(p_in_str) <= 6
>         THEN RAISE NOTICE 'Hi %', p_in_str;
>     ELSE
>         RAISE NOTICE 'Hello %', p_in_str;
>     END IF;
>
>     RETURN;
> END;
> $BODY$
…


>  CREATE OR REPLACE FUNCTION called1(text)
>   RETURNS void AS
> $BODY$
...
>     RAISE NOTICE 'Hi %', p_in_str1;
>
>     RETURN;
> END;
> $BODY$
…

>  CREATE OR REPLACE FUNCTION called2(text)
>   RETURNS void AS
> $BODY$
...
>     RAISE NOTICE 'Hello %', p_in_str2;
>
>     RETURN;
> END;
...


That's a rather uninteresting experiment, as all it does is call a function and raise a notice. Relative to what the functions do, the function call itself takes a significant amount of time. No surprise there, you'll see something similar in any language, even C. All you're showing is that calling a function takes some amount of time > 0.

In C, a function call needs to look up an address to jump to, in plpgsql the database needs to look up the function body in a table. If the function is small and atomic it often gets called from multiple other functions and is probably cached anyway. The main difference between C and plpgsql here is that the latter is an interpreted language, so it does need to read in the entire function body after a call - which I'd expect to be quite a bit faster with a smaller (atomic) function body, especially when it hasn't been cached yet.

So far I haven't been convinced.

An actual use-case where the functions actually do something would be far more interesting. I doubt anybody writes functions just to raise a notice. I expect that in reality most plpgsql functions perform database queries and do something with the result. In such cases, function call overhead could be significant if the call is done for each record in a result set, for example. And even then it's worth considering whether that matters to your situation enough that it outweighs the usual benefits of code separation.

> On Sat, May 2, 2015 at 7:37 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 05/02/2015 03:28 PM, Bill Moran wrote:
> On Sat, 02 May 2015 15:06:24 -0700
> Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 05/02/2015 02:07 PM, Jeff Janes wrote:
> On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>      On 05/02/2015 10:12 AM, Melvin Davidson wrote:
>
>          AFAIK, you cannot "package" functions in  PostgreSQL, but it is
>          possible to
>          call a function from within a function.
>
>          That being said, I would seriously look at how and why you are
>          writing
>          your functions
>          as functions that call other functions are not very efficient.
>
>
>      I am not following. That is what packaging is about, separating out
>      'units of work' so they can be combined as needed. Part of that is
>      using existing functions in new functions/classes. In fact in the
>      Postgres source I see this in many places. Now it is entirely
>      possible I missed a memo, so I am open to a more detailed
>      explanation of the inefficiencies involved.
>
>
> The Postgres source is written in C, not in plpgsql.  C has a good
> optimizing compiler and plpgsql doesn't.
>
> Does this actually matter?  I am a biologist that backed into computing,
> so I realize I am weak on the fundamentals. Still the scientist in me
> wants data backing assertions. As I understand it plpgsql works close to
> the server and is optimized to do so.  I know writing in C would be a
> better solution. Still is calling plpgsql functions inside plpgsql
> really a bad thing when just considering plpgsql?
>
> The answer to that is the same answer to so many other things: it depends.
>
> plpgsql functions are slower than C. They also lack a lot of language
> features that C has. That being said, if they're meeting your needs, then
> don't worry about it. plpgsql is around because for most people, it works
> well enough. There are certainly cases when you want to create very complex
> logic in the database and plpgsql is liable to make that difficult. But
> there are a lot of cases where having to manage pointers and a build
> environment and all the things that go with C aren't justified, because
> plpgsql has none of that complexity. There are advantages both ways.
>
> The beauty of PostgreSQL is that you have both available and you
> can choose whichever is best for your situation.
>
> Agreed, though in my case I drop into plpythonu when I want more complex solutions.
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>
> --
> Melvin Davidson
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.