Thread: overhead of plpgsql functions over simple select
I'd like to encapsulate something that now is just a simple select in a plpgsql function now just to keep an interface consistent but even for well... encapsulating the sql. Right now a simple select statement will suffice. What kind of overhead a plpgsql that just return a select incur compared to a simple select? I'm not that worried of old query plans. thx -- Ivan Sergio Borgonovo http://www.webthatworks.it
Just to seek some tips on how to efficiently debug PL/SQL. One thing that bugs me in particular is the inability to trace a SQL line number in an error message to the line in my PL/PGSQL code. Thanks, CYW
> From: cyw@dls.net <cyw@dls.net> > Subject: [GENERAL] Tips on how to efficiently debugging PL/PGSQL > To: pgsql-general@postgresql.org > Date: Thursday, 23 October, 2008, 6:19 PM > Just to seek some tips on how to efficiently debug PL/SQL. > > One thing that bugs me in particular is the inability to > trace a SQL line > number in an error message to the line in my PL/PGSQL code. > edb have a debugger that intigrates with pgadmin http://pgfoundry.org/projects/edb-debugger/
Glyn Astill wrote: >> From: cyw@dls.net <cyw@dls.net> >> Just to seek some tips on how to efficiently debug PL/SQL. >> >edb have a debugger that intigrates with pgadmin > >http://pgfoundry.org/projects/edb-debugger/ This debugger is integrated with pgAdminIII that is shipped with PostgreSQL 8.3. Just right click the desired function and chose an action from the "Debugging" context menu. Rainer
Hello
postgres=# create function simplefce(a int, b int) returns int as
$$select $1 + $2$$ language sql immutable strict;
CREATE FUNCTION
postgres=# create function simplefce1(a int, b int) returns int as
$$begin return a+b; end;$$ language plpgsql immutable strict;
CREATE FUNCTION
postgres=#
postgres=# select sum(simplefce(i,1)) from generate_series(1,100000) g(i);
sum
------------
5000150000
(1 row)
Time: 255,997 ms
postgres=# select sum(simplefce1(i,1)) from generate_series(1,100000) g(i);
sum
------------
5000150000
(1 row)
Time: 646,791 ms
Regards
Pavel Stehule
2008/10/23 Ivan Sergio Borgonovo <mail@webthatworks.it>:
> I'd like to encapsulate something that now is just a simple select
> in a plpgsql function now just to keep an interface consistent but
> even for well... encapsulating the sql.
>
> Right now a simple select statement will suffice.
>
> What kind of overhead a plpgsql that just return a select incur
> compared to a simple select?
>
> I'm not that worried of old query plans.
>
> thx
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> postgres=# create function simplefce(a int, b int) returns int as
> $$select $1 + $2$$ language sql immutable strict;
> CREATE FUNCTION
> postgres=# create function simplefce1(a int, b int) returns int as
> $$begin return a+b; end;$$ language plpgsql immutable strict;
> CREATE FUNCTION
That's a pretty unfair comparison, because that SQL function is simple
enough to be inlined. The place to use plpgsql is when you need some
procedural logic; at which point a SQL function simply fails to provide
the required functionality.
regards, tom lane
2008/10/24 Tom Lane <tgl@sss.pgh.pa.us>: > "Pavel Stehule" <pavel.stehule@gmail.com> writes: >> postgres=# create function simplefce(a int, b int) returns int as >> $$select $1 + $2$$ language sql immutable strict; >> CREATE FUNCTION >> postgres=# create function simplefce1(a int, b int) returns int as >> $$begin return a+b; end;$$ language plpgsql immutable strict; >> CREATE FUNCTION > > That's a pretty unfair comparison, because that SQL function is simple > enough to be inlined. The place to use plpgsql is when you need some > procedural logic; at which point a SQL function simply fails to provide > the required functionality. > Yes, this test is maximal unfair to plpgsql - it's too simply function. But it was original question. What is overhead plpgsql call on simple functions? On every little bit complicated functions overhead should be less. And this sample shows sense of using SQL functions. regards Pavel Stehule > regards, tom lane >
On Fri, 24 Oct 2008 07:03:35 +0200 "Pavel Stehule" <pavel.stehule@gmail.com> wrote: > 2008/10/24 Tom Lane <tgl@sss.pgh.pa.us>: > > "Pavel Stehule" <pavel.stehule@gmail.com> writes: > >> postgres=# create function simplefce(a int, b int) returns int > >> as $$select $1 + $2$$ language sql immutable strict; > >> CREATE FUNCTION > >> postgres=# create function simplefce1(a int, b int) returns int > >> as $$begin return a+b; end;$$ language plpgsql immutable strict; > >> CREATE FUNCTION > > > > That's a pretty unfair comparison, because that SQL function is > > simple enough to be inlined. The place to use plpgsql is when > > you need some procedural logic; at which point a SQL function > > simply fails to provide the required functionality. > > > > Yes, this test is maximal unfair to plpgsql - it's too simply > function. But it was original question. What is overhead plpgsql > call on simple functions? On every little bit complicated functions > overhead should be less. And this sample shows sense of using SQL > functions. It's just one case. Furthermore I was interested in plain select statement vs. plsql encapsulating a simple select statement. But since we are at it, it would be nice to have a larger picture. I just avoided a test because I didn't know what to test. eg. If I'm using a stable function that return records plpgsql functions are more complicated just to interpret, they are simply longer, then as I'm learning now they can't be embedded while sql functions can (am I right?). To make a meaningful test I should know what are the potential factors that make the difference between the 2 (3 actually, simple sql statement, sql functions and plpgsql functions). I can't even understand if all immutable sql functions can be embedded. The more field are returned (unless I've a custom type or a matching table) the longer will be the plpgsql function etc... I couldn't think anything other than cost of interpretation (or does postgresql has a sort of JIT) and cost of call that can impact the difference. I can't still understand when and if it is going to make a difference. Yeah I understood that at least in immutable functions sql is faster. I did some simple tests and it looks as being roughly 3 time faster. With higher numbers the difference seems to get smaller, maybe because of the higher cost of allocating memory caused by generate_series(?). So I know that immutable simple(?) functions are much faster in sql... anything else to avoid? What are the factors that play a role in execution times? -- Ivan Sergio Borgonovo http://www.webthatworks.it