Skip to content

Instantly share code, notes, and snippets.

@xvaara
Created September 19, 2018 19:10
Show Gist options
  • Save xvaara/df0b802b86d2aa4f30b88a3fd6b69a14 to your computer and use it in GitHub Desktop.
Save xvaara/df0b802b86d2aa4f30b88a3fd6b69a14 to your computer and use it in GitHub Desktop.
Benchmark PL/pgSQL, PL/v8, PL/lua-ng
DROP TABLE IF EXISTS accumulator_lua;
CREATE TABLE accumulator_lua (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
DROP TABLE IF EXISTS accumulator_lua_pre;
CREATE TABLE accumulator_lua_pre (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
DROP TABLE IF EXISTS accumulator_v8;
CREATE TABLE accumulator_v8 (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
DROP TABLE IF EXISTS accumulator_plpgsql;
CREATE TABLE accumulator_plpgsql (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
CREATE EXTENSION IF NOT EXISTs plv8;
CREATE EXTENSION IF NOT EXISTs pllua;
CREATE EXTENSION IF NOT EXISTs plpgsql;
CREATE OR REPLACE FUNCTION fill_lua_pre() RETURNS void AS $$
local query = spi.execute("SELECT count(*) as count, SUM(new_value) as sum FROM accumulator_lua_pre") -- read-only, only 1
p:execute(random(0, 99), query[1].sum) -- insert values
end
do -- the part below will be executed once before the first call
p = spi.prepare("INSERT INTO accumulator_lua_pre (new_value, sum_previous) VALUES ($1, $2)")
random = math.random
$$ LANGUAGE pllua;
CREATE OR REPLACE FUNCTION fill_lua() RETURNS void AS $$
local query = spi.execute("SELECT count(*) as count, SUM(new_value) as sum FROM accumulator_lua") -- read-only, only 1
local random = math.random
local p = spi.prepare("INSERT INTO accumulator_lua (new_value, sum_previous) VALUES ($1, $2)")
p:execute(random(0, 99), query[1].sum) -- insert values
$$ LANGUAGE pllua;
CREATE OR REPLACE FUNCTION fill_v8 () RETURNS void AS $$
var rows = plv8.execute( "SELECT count(*), SUM(new_value) FROM accumulator_v8" );
var rand = Math.floor((Math.random() * 100) + 1)
var prepared = plv8.prepare("INSERT INTO accumulator_v8 (new_value, sum_previous) VALUES ($1, $2)")
prepared.execute([rand, rows[0].sum]);
$$
LANGUAGE plv8;
-- doesn't work...
-- CREATE OR REPLACE FUNCTION fill_v8_pre () RETURNS void AS $$
-- var rows = plv8.execute( "SELECT count(*), SUM(new_value) FROM accumulator" );
-- var rand = Math.floor((Math.random() * 100) + 1)
-- if (!plv8.$prepared) {
-- plv8.$prepared = plv8.prepare("INSERT INTO accumulator (new_value, sum_previous) VALUES ($1, $2)")
-- }
-- // plv8.elog(NOTICE, plv8.$prepared);
-- plv8.$prepared.execute([rand, rows[0].sum]);
-- $$
-- LANGUAGE plv8;
CREATE OR REPLACE FUNCTION "fill_plpgsql"() RETURNS void AS
$BODY$
DECLARE
acc_count integer;
acc_sum integer;
randint integer;
BEGIN
SELECT count(*), SUM(new_value) INTO acc_count, acc_sum FROM "accumulator_plpgsql";
IF FOUND THEN
randint = trunc(random() * 99 + 1);
INSERT INTO "accumulator_plpgsql" ("new_value", "sum_previous") VALUES (randint, acc_sum);
--RETURN TRUE;
END IF;
--RETURN FALSE;
END;
$BODY$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION plbench(query text, n int) returns float as $$
DECLARE
t0 timestamp with time zone;
e float;
BEGIN
t0 := clock_timestamp();
for i in 1 .. n loop
execute query;
end loop;
e = extract(microseconds from (clock_timestamp() - t0));
return e / 1000000;
END;
$$ language plpgsql;
SELECT
plbench('SELECT fill_plpgsql()', 10000) as plpgsql,
plbench('SELECT fill_lua()', 10000) as lua,
plbench('SELECT fill_lua_pre()', 10000) as lua_pre,
plbench('SELECT fill_v8()', 10000) as v8;
@xvaara
Copy link
Author

xvaara commented Sep 19, 2018

 plpgsql  |   lua    | lua_pre  |    v8
----------+----------+----------+-----------
 7.785684 | 9.750836 | 9.227317 | 14.167607

seconds to run 10k iterations.

My results from an old xeon server.

@xvaara
Copy link
Author

xvaara commented Sep 19, 2018

Benchmarking on DigitalOcean with ubuntu-s-4vcpu-8gb

default from postgresql.org (non luajit, older versions)

 plpgsql  |   lua    | lua_pre  |    v8
----------+----------+----------+-----------
 7.101293 | 8.420627 | 7.783287 | 11.843494

seconds to run 10k iterations.

latest pllua-ng and plv8
https://github.com/plv8/plv8/archive/v2.3.7.tar.gz
https://github.com/RhodiumToad/pllua-ng.git
http://luajit.org/download/LuaJIT-2.1.0-beta3.tar.gz

 plpgsql  |   lua    | lua_pre  |    v8
----------+----------+----------+----------
 6.621787 | 8.365727 | 7.996122 | 12.17378

seconds to run 10k iterations.

On my mac (no plv8, can't get that to build)

 plpgsql  |   lua    | lua_pre
----------+----------+----------
 4.985471 | 6.253387 | 5.879452

seconds to run 10k iterations.

@JerrySievert
Copy link

JerrySievert commented Sep 19, 2018

which version of plv8 are you using, and what sort of build issues on the Mac are you having?

Copy link

ghost commented Sep 20, 2018

It would be helpful if you added some units or whether smaller numbers mean better performance to your gist, as it stand it is not immediately obvious what the metrics represent.

@xvaara
Copy link
Author

xvaara commented Sep 25, 2018

which version of plv8 are you using, and what sort of build issues on the Mac are you having?

I went through plv8 github issues and then noticed that I have v8 installed from homebrew, so running brew uninstall v8 fixed it for me!

 plpgsql  |   lua    | lua_pre |    v8
----------+----------+---------+----------
 5.135765 | 6.314065 | 5.91201 | 9.296767

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment