Benchmark PL/pgSQL, PL/v8, PL/lua-ng

bencmark.sql

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;

 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.

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.

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

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.

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