Created
September 19, 2018 19:10
-
-
Save xvaara/df0b802b86d2aa4f30b88a3fd6b69a14 to your computer and use it in GitHub Desktop.
Benchmark PL/pgSQL, PL/v8, PL/lua-ng
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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!