> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
