-
-
Save xvaara/df0b802b86d2aa4f30b88a3fd6b69a14 to your computer and use it in GitHub Desktop.
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; |
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
My results from an old xeon server.