how to see "where" SQL is better than PLPGSQL

2014-09-28 20:30 GMT+02:00 Gerardo Herzig <gherzig@fmed.uba.ar>:

Hi all. I see an entire database, with all the stored procedures writen in
plpgsql. Off course, many (if not all) of that SP are simple inserts,
updates, selects and so on.

So, i want to test and show the differences between doing the same
function in pgpgsql vs. plain sql.
Im getting statistics (via collectd if that matters) and doing a modified
version of the pgbench tests, just using pl (and sql) functions instead of
the plain query:

\setrandom delta -5000 5000
BEGIN;
SELECT pgbench_accounts_upd_pl(:delta, :aid);
SELECT get_pgbench_accounts_pl(:aid);
SELECT pgbench_tellers_upd_pl(:delta, :tid);
SELECT pgbench_branches_upd_pl(:delta, :bid);
select pgbench_history_ins_pl(:tid, :bid, :aid, :delta);
END;

At first, pgbench is showing a difference between the "pl" and de "sql"
versions:

(pl.scripts own the "PL" version, sql.script owns the "SQL" version of the
test)
(This is a tiny netbook, with a dual core procesor)

gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test
duration: 300 s
number of transactions actually processed: 13524
tps = 45.074960 (including connections establishing)
tps = 75.260741 (excluding connections establishing)

gherzig@via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test
starting vacuum...end.
duration: 300 s
number of transactions actually processed: 15125
tps = 50.412852 (including connections establishing)
tps = 92.058245 (excluding connections establishing)

So yeah, it looks like the "SQL" version is able to do a 10% more
transactions.
However, i was hoping to see anothers "efects" of using sql (perhaps less
load avg in the SQL version), at the OS level.

So, finnaly, the actual question:
¿Wich signals should i monitor, in order to show that PGPLSQL uses more
resources than SQL?

It is hard question. It is invisible feature of SQL proc - inlining. What I
know, a SQL function is faster than PLpgSQL function, when it is inlined.
But there is nothing visible metric, that inform you about inlining.

Regards

Pavel

Hi all. I see an entire database, with all the stored procedures
writen in plpgsql. Off course, many (if not all) of that SP are
simple inserts, updates, selects and so on.

So, i want to test and show the differences between doing the same
function in pgpgsql vs. plain sql.
Im getting statistics (via collectd if that matters) and doing a
modified version of the pgbench tests, just using pl (and sql)
functions instead of the plain query:

\setrandom delta -5000 5000
BEGIN;
SELECT pgbench_accounts_upd_pl(:delta, :aid);
SELECT get_pgbench_accounts_pl(:aid);
SELECT pgbench_tellers_upd_pl(:delta, :tid);
SELECT pgbench_branches_upd_pl(:delta, :bid);
select pgbench_history_ins_pl(:tid, :bid, :aid, :delta);
END;

At first, pgbench is showing a difference between the "pl" and de
"sql" versions:

(pl.scripts own the "PL" version, sql.script owns the "SQL" version
of the test)
(This is a tiny netbook, with a dual core procesor)

gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test
duration: 300 s
number of transactions actually processed: 13524
tps = 45.074960 (including connections establishing)
tps = 75.260741 (excluding connections establishing)

gherzig@via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test
starting vacuum...end.
duration: 300 s
number of transactions actually processed: 15125
tps = 50.412852 (including connections establishing)
tps = 92.058245 (excluding connections establishing)

So yeah, it looks like the "SQL" version is able to do a 10% more
transactions.
However, i was hoping to see anothers "efects" of using sql (perhaps
less load avg in the SQL version), at the OS level.

So, finnaly, the actual question:
¿Wich signals should i monitor, in order to show that PGPLSQL uses
more resources than SQL?

It is hard question. It is invisible feature of SQL proc - inlining.
What I know, a SQL function is faster than PLpgSQL function, when it
is inlined. But there is nothing visible metric, that inform you
about inlining.

Regards

Pavel

Thanks Pavel! Im not (directly) concerned about speed, im concerned about resources usage.
May be there is a value that shows the "PGSQL machine necesary for plpgsql execution"

Thanks again for your time.
Gerardo

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2014-09-28 21:29 GMT+02:00 Gerardo Herzig <gherzig@fmed.uba.ar>:

Hi all. I see an entire database, with all the stored procedures
writen in plpgsql. Off course, many (if not all) of that SP are
simple inserts, updates, selects and so on.

So, i want to test and show the differences between doing the same
function in pgpgsql vs. plain sql.
Im getting statistics (via collectd if that matters) and doing a
modified version of the pgbench tests, just using pl (and sql)
functions instead of the plain query:

\setrandom delta -5000 5000
BEGIN;
SELECT pgbench_accounts_upd_pl(:delta, :aid);
SELECT get_pgbench_accounts_pl(:aid);
SELECT pgbench_tellers_upd_pl(:delta, :tid);
SELECT pgbench_branches_upd_pl(:delta, :bid);
select pgbench_history_ins_pl(:tid, :bid, :aid, :delta);
END;

At first, pgbench is showing a difference between the "pl" and de
"sql" versions:

(pl.scripts own the "PL" version, sql.script owns the "SQL" version
of the test)
(This is a tiny netbook, with a dual core procesor)

gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test
duration: 300 s
number of transactions actually processed: 13524
tps = 45.074960 (including connections establishing)
tps = 75.260741 (excluding connections establishing)

gherzig@via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test
starting vacuum...end.
duration: 300 s
number of transactions actually processed: 15125
tps = 50.412852 (including connections establishing)
tps = 92.058245 (excluding connections establishing)

So yeah, it looks like the "SQL" version is able to do a 10% more
transactions.
However, i was hoping to see anothers "efects" of using sql (perhaps
less load avg in the SQL version), at the OS level.

So, finnaly, the actual question:
¿Wich signals should i monitor, in order to show that PGPLSQL uses
more resources than SQL?

It is hard question. It is invisible feature of SQL proc - inlining.
What I know, a SQL function is faster than PLpgSQL function, when it
is inlined. But there is nothing visible metric, that inform you
about inlining.

Regards

Pavel

Thanks Pavel! Im not (directly) concerned about speed, im concerned about
resources usage.
May be there is a value that shows the "PGSQL machine necesary for plpgsql
execution"

This is little bit more wide topic. The performance is only one point,
second is a readability, robustness, .. and there are questions about plan
caching, query optimization,

Usually, PLpgSQL should not be used for one line SELECT based functions or
one line expression based functions. But there are some exceptions.

The best way is slow queries monitoring, and slow queries analyse - it is
base for decision for changing language.

Regards

Pavel