Date: Sun, 28 Sep 2014 16:29:14 -0300 (ART)
From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: "pgsql-general@postgresql.org >> PG-General Mailing List"
 <pgsql-general@postgresql.org>, pgsql-sql <pgsql-sql@postgresql.org>
Message-ID: <2019227270.991514.1411932553232.JavaMail.root@fmed.uba.ar>
In-Reply-To: 
 <CAFj8pRBPBkmYWodzm=iWX_Kwt+ntvF+YQFHtWuUGypdUVORL1g@mail.gmail.com>
Subject: Re: [SQL] how to see "where" SQL is better than PLPGSQL
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

> 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.
>=20
> 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:
>=20
> \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;
>=20
> At first, pgbench is showing a difference between the "pl" and de
> "sql" versions:
>=20
> (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)
>=20
> gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test
> duration: 300 s
> number of transactions actually processed: 13524
> tps =3D 45.074960 (including connections establishing)
> tps =3D 75.260741 (excluding connections establishing)
>=20
> 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 =3D 50.412852 (including connections establishing)
> tps =3D 92.058245 (excluding connections establishing)
>=20
> 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.
>=20
> So, finnaly, the actual question:
> =C2=BFWich signals should i monitor, in order to show that PGPLSQL uses
> more resources than SQL?
>=20
>=20
>=20
> 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.
>=20
>=20
> Regards
>=20
>=20
> Pavel
>=20
>
Thanks Pavel! Im not (directly) concerned about speed, im concerned about r=
esources usage.
May be there is a value that shows the "PGSQL machine necesary for plpgsql =
execution"

Thanks again for your time.
Gerardo


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