plpgsql-perftest.sql

CREATE OR REPLACE FUNCTION perf01()
RETURNS bigint AS $$
DECLARE s bigint;
BEGIN
  FOR i IN 1..10000000
  LOOP
    s := s + 1;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION perf02()
RETURNS numeric AS $$
DECLARE s numeric;
BEGIN
  FOR i IN 1..10000000
  LOOP
    s := s + 1.0;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION perf03()
RETURNS numeric AS $$
DECLARE s numeric;
BEGIN
  FOR i IN 1..10000000
  LOOP
    s := s::bigint + 1;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

DROP TYPE IF EXISTS tt CASCADE;
CREATE TYPE tt AS (a int, b int, c int);

CREATE OR REPLACE FUNCTION perf04()
RETURNS void AS $$
DECLARE t tt;
BEGIN
  FOR i IN 1..10000000
  LOOP
    t := ROW(i, i + 1, i + 2);
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION perf05()
RETURNS void AS $$
DECLARE t tt;
BEGIN
  t := ROW(0, 0, 0);
  FOR i IN 1..10000000
  LOOP
    t.a := i;
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION perf06()
RETURNS void AS $$
DECLARE t tt;
BEGIN
  t := ROW(0, 0, 0);
  FOR i IN 1..10000000
  LOOP
    t.a := t.a + 1;
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION perf07()
RETURNS void AS $$
DECLARE a int[];
BEGIN
  FOR i IN 1..100000
  LOOP
    a[i] := 0;
  END LOOP;

  FOR i IN 1..1000
  LOOP
    FOR j IN 1..10000
    LOOP
      a[i] := j;
    END LOOP;
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION perf08()
RETURNS void AS $$
DECLARE a int[];
BEGIN
  FOR i IN 1..100000
  LOOP
    a[i] := 0;
  END LOOP;

  FOR i IN 1..1000
  LOOP
    FOR j IN 1..10000
    LOOP
      a[i] := a[i] + 1;
    END LOOP;
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION perf09()
RETURNS void AS $$
DECLARE a int[];
BEGIN
  FOR i IN 1..100000
  LOOP
    a[i] := 0;
  END LOOP;

  FOR i IN 1..1000
  LOOP
    FOR j IN 1..10000
    LOOP
      a[i] := a[i] + 1.0;
    END LOOP;
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

\timing on

SELECT perf01();
SELECT perf02();
SELECT perf03();
SELECT perf04();
SELECT perf05();
SELECT perf06();
SELECT perf07();
SELECT perf08();
SELECT perf09();

SELECT pg_sleep(60);

SELECT perf01();
SELECT perf02();
SELECT perf03();
SELECT perf04();
SELECT perf05();
SELECT perf06();
SELECT perf07();
SELECT perf08();
SELECT perf09();

SELECT pg_sleep(60);

SELECT perf01();
SELECT perf02();
SELECT perf03();
SELECT perf04();
SELECT perf05();
SELECT perf06();
SELECT perf07();
SELECT perf08();
SELECT perf09();