agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedhow to see "where" SQL is better than PLPGSQL
4+ messages / 2 participants
[nested] [flat]
* how to see "where" SQL is better than PLPGSQL
@ 2014-09-28 18:30 Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 4+ messages in thread
From: Gerardo Herzig @ 2014-09-28 18:30 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] how to see "where" SQL is better than PLPGSQL
@ 2014-09-28 18:49 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 4+ messages in thread
From: Pavel Stehule @ 2014-09-28 18:49 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: " <pgsql-general@postgresql.org>; pgsql-sql
2014-09-28 20:30 GMT+02:00 Gerardo Herzig <gherzig@fmed.uba.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@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!
> Gerardo
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] how to see "where" SQL is better than PLPGSQL
@ 2014-09-28 19:29 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gerardo Herzig @ 2014-09-28 19:29 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: " <pgsql-general@postgresql.org>; pgsql-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?
>
>
>
> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to see "where" SQL is better than PLPGSQL
@ 2014-09-28 19:44 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2014-09-28 19:44 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: " <pgsql-general@postgresql.org>; pgsql-sql
2014-09-28 21:29 GMT+02:00 Gerardo Herzig <gherzig@fmed.uba.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@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"
>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-09-28 19:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-09-28 18:30 how to see "where" SQL is better than PLPGSQL Gerardo Herzig <gherzig@fmed.uba.ar>
2014-09-28 18:49 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-09-28 19:29 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2014-09-28 19:44 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox