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

2014-09-28 21:29 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 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

>
> Thanks again for your time.
> Gerardo
>