agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: pgsql-general@postgresql.org, pgsql-sql <pgsql-sql@postgresql.org>
Subject: how to see "where" SQL is better than PLPGSQL
Date: Sun, 28 Sep 2014 15:30:15 -0300 (ART)
Message-ID: <796319273.990931.1411929015224.JavaMail.root@fmed.uba.ar> (raw)
In-Reply-To: <784256159.990526.1411927128224.JavaMail.root@fmed.uba.ar>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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?

Thanks!
Gerardo


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

view thread (4+ messages)  latest in thread

Message-ID: <796319273.990931.1411929015224.JavaMail.root@fmed.uba.ar>
Permalink:  ../796319273.990931.1411929015224.JavaMail.root@fmed.uba.ar/
Also on:    postgresql.org/message-id/796319273.990931.1411929015224.JavaMail.root@fmed.uba.ar

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gherzig@fmed.uba.ar
  Subject: Re: how to see "where" SQL is better than PLPGSQL
  In-Reply-To: <796319273.990931.1411929015224.JavaMail.root@fmed.uba.ar>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox