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 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