Created
March 12, 2016 16:15
-
-
Save areski/8b6677eb8dcddc5831b0 to your computer and use it in GitHub Desktop.
PLLUA example - accumulator
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
-- | |
-- Benchmark PLLUA | |
-- | |
DROP TABLE accumulator; | |
CREATE TABLE accumulator (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT); | |
CREATE OR REPLACE FUNCTION fillaccumulator() RETURNS void AS $$ | |
local query = server.execute('SELECT count(*), SUM(new_value) FROM accumulator', true, 1) -- read-only, only 1 | |
local random = math.random | |
local p = server.prepare("INSERT INTO accumulator (new_value, sum_previous) VALUES($1, $2)", {"int4", "int4"}) | |
p:execute{math.random(0, 99), query[1].sum} -- insert values | |
$$ LANGUAGE pllua; | |
-- SELECT fillaccumulator(); | |
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()) - extract(microseconds from t0); | |
return e / 1000000; | |
END; | |
$$ language plpgsql; | |
SELECT plbench('SELECT fillaccumulator()', 10000); | |
SELECT count(*), SUM(new_value) FROM accumulator; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment