A18msvCi

1DROP TABLE IF EXISTS accumulator_lua;
2CREATE TABLE accumulator_lua (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
3
4DROP TABLE IF EXISTS accumulator_lua_pre;
5CREATE TABLE accumulator_lua_pre (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
6
7DROP TABLE IF EXISTS accumulator_v8;
8CREATE TABLE accumulator_v8 (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
9
10DROP TABLE IF EXISTS accumulator_plpgsql;
11CREATE TABLE accumulator_plpgsql (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);
12
13CREATE EXTENSION IF NOT EXISTs plv8;
14CREATE EXTENSION IF NOT EXISTs pllua;
15CREATE EXTENSION IF NOT EXISTs plpgsql;
16
17CREATE OR REPLACE FUNCTION fill_lua_pre() RETURNS void AS $$
18    local query = spi.execute("SELECT count(*) as count, SUM(new_value) as sum FROM accumulator_lua_pre") -- read-only, only 1
19    p:execute(random(0, 99), query[1].sum) -- insert values
20end
21do -- the part below will be executed once before the first call
22    p = spi.prepare("INSERT INTO accumulator_lua_pre (new_value, sum_previous) VALUES ($1, $2)")
23    random = math.random
24$$ LANGUAGE pllua;
25
26CREATE OR REPLACE FUNCTION fill_lua() RETURNS void AS $$
27    local query = spi.execute("SELECT count(*) as count, SUM(new_value) as sum FROM accumulator_lua") -- read-only, only 1
28    local random = math.random
29    local p = spi.prepare("INSERT INTO accumulator_lua (new_value, sum_previous) VALUES ($1, $2)")
30    p:execute(random(0, 99), query[1].sum) -- insert values
31$$ LANGUAGE pllua;
32
33
34
35
36
37CREATE OR REPLACE FUNCTION fill_v8 () RETURNS void AS $$
38    var rows = plv8.execute( "SELECT count(*), SUM(new_value) FROM accumulator_v8" );
39    var rand = Math.floor((Math.random() * 100) + 1)
40    var prepared = plv8.prepare("INSERT INTO accumulator_v8 (new_value, sum_previous) VALUES ($1, $2)")
41    prepared.execute([rand, rows[0].sum]);
42$$
43LANGUAGE plv8;
44
45-- doesn't work...
46-- CREATE OR REPLACE FUNCTION fill_v8_pre () RETURNS void AS $$
47--     var rows = plv8.execute( "SELECT count(*), SUM(new_value) FROM accumulator" );
48--     var rand = Math.floor((Math.random() * 100) + 1)
49--     if (!plv8.$prepared) {
50--         plv8.$prepared = plv8.prepare("INSERT INTO accumulator (new_value, sum_previous) VALUES ($1, $2)")
51--     }
52--     // plv8.elog(NOTICE, plv8.$prepared);
53--     plv8.$prepared.execute([rand, rows[0].sum]);
54-- $$
55-- LANGUAGE plv8;
56
57CREATE OR REPLACE FUNCTION "fill_plpgsql"() RETURNS void AS
58$BODY$
59DECLARE
60    acc_count integer;
61    acc_sum integer;
62    randint integer;
63BEGIN
64    SELECT count(*), SUM(new_value) INTO acc_count, acc_sum FROM "accumulator_plpgsql";
65
66    IF FOUND THEN
67        randint = trunc(random() * 99 + 1);
68        INSERT INTO "accumulator_plpgsql" ("new_value", "sum_previous") VALUES (randint, acc_sum);
69        --RETURN TRUE;
70    END IF;
71    --RETURN FALSE;
72END;
73$BODY$ LANGUAGE plpgsql;
74
75
76CREATE OR REPLACE FUNCTION plbench(query text, n int) returns float as $$
77DECLARE
78    t0 timestamp with time zone;
79    e float;
80BEGIN
81    t0 := clock_timestamp();
82    for i in 1 .. n loop
83        execute query;
84    end loop;
85    e = extract(microseconds from (clock_timestamp() - t0));
86    return e / 1000000;
87END;
88$$ language plpgsql;
89
90
91SELECT 
92  plbench('SELECT fill_plpgsql()', 10000) as plpgsql,
93  plbench('SELECT fill_lua()', 10000) as lua,
94  plbench('SELECT fill_lua_pre()', 10000) as lua_pre,
95  plbench('SELECT fill_v8()', 10000) as v8;