Re: [SQL] how to see "where" SQL is better than PLPGSQL

2014-09-28 20:30 GMT+02:00 Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)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(at)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(at)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!
> Gerardo
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>