test.sql

application/x-sql

Download

\set iter 10
\set exp 4

\set myschema tmp_notify_optimise

DROP SCHEMA IF EXISTS :myschema CASCADE;
CREATE SCHEMA :myschema;
SET search_path = :myschema, public;

CREATE FUNCTION run_test(
INOUT n int,
INOUT m int,
OUT total_usec float)
RETURNS RECORD
LANGUAGE plpgsql
AS $$
DECLARE
	t text;
	c timestamp;
BEGIN
	c := clock_timestamp();
	FOR i IN 1 .. n LOOP
		EXECUTE 'NOTIFY test_channel_' || i % m;
	END LOOP;
	total_usec := EXTRACT(EPOCH FROM (1000000*(clock_timestamp() - c)));
END;
$$;

CREATE TABLE test_results
(n int
,m int
,iter int
,total_usec bigint);

-- This is WRONG because it happens in a single transaction

\pset format unaligned
\pset tuples_only on

\o var_test.sql

SELECT $$\set QUIET on$$;

SELECT '
INSERT INTO test_results(n,m,total_usec,iter)
  SELECT (run_test(' || n || ',' || 1 || ')).*,' || i || ';
INSERT INTO test_results(n,m,total_usec,iter)
  SELECT (run_test(' || n || ',' || n || ')).*,' || i || ';
' FROM
(
	SELECT	(10^e)::int AS n
	,	i
	FROM	generate_series(1,:exp) n(e)
	,	generate_series(1,:iter) i(i)
	ORDER BY n,i
) x(n,i)
;

\o

\i var_test.sql

\pset format aligned
\pset tuples_only off

TABLE test_results;

\echo
\echo 'results of the test:'
\echo
SELECT	n
,	m
,	iter
,	round(avg_usec,3) as avg_usec
FROM (
	SELECT	n
	,	m
	,	count(1) as iter
	,	avg(total_usec)/n	AS avg_usec
	FROM	test_results
	GROUP BY n,m) x
ORDER BY m,n
;