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;

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