Skip to content

Instantly share code, notes, and snippets.

@areski

areski/benchmark_lua.sql

Created March 12, 2016 16:15
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save areski/8b6677eb8dcddc5831b0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save areski/8b6677eb8dcddc5831b0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. areski created this gist Mar 12, 2016.
    33 changes: 33 additions & 0 deletions benchmark_lua.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,33 @@
    --
    -- 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;