bench-skip-texteq.sql

\timing on
-- Benchmark "texteq".

BEGIN;
-- Table having a single "text" column and 100 rows @ 10 MiB each.
CREATE TEMP TABLE t (c text);
INSERT INTO t
	SELECT repeat('foobarbazz', 1024 * 1024) || to_char(n, '000000')
	FROM generate_series(1,100) ser(n);
SELECT pg_size_pretty(pg_total_relation_size('t'));

-- Compare each row, failing at the length check.  With the patch, we skip all
-- detoasts.  This is a best-case for the patch.
CREATE FUNCTION pg_temp.try() RETURNS void LANGUAGE plpgsql AS $$
BEGIN
	FOR i IN 1..30 LOOP
		PERFORM count(*) FROM t WHERE c = repeat('foobarbazz', 1024 * 1025);
	END LOOP;
END
$$;
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
ROLLBACK;


BEGIN;
\set text144 '''abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789'''
-- Table having a single "text" column and 3M rows @ 144 B each.
CREATE TEMP TABLE t (c text);
INSERT INTO t SELECT :text144 FROM generate_series(1,3000000);
SELECT pg_size_pretty(pg_total_relation_size('t'));

-- Compare each row successfully.  No detoasting would ever be involved.  This
-- should reveal the simple-case overhead of the patch.
CREATE FUNCTION pg_temp.try(text) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
	FOR i IN 1..30 LOOP
		PERFORM count(*) FROM t WHERE c = $1;
	END LOOP;
END
$$;
SELECT pg_temp.try(:text144);
SELECT pg_temp.try(:text144);
SELECT pg_temp.try(:text144);
SELECT pg_temp.try(:text144);
SELECT pg_temp.try(:text144);
ROLLBACK;


BEGIN;
-- Table having a single "text" column and 5M tiny (3 B) rows.
CREATE TEMP TABLE t (c text);
INSERT INTO t SELECT 'foo' FROM generate_series(1,5000000);
SELECT pg_size_pretty(pg_total_relation_size('t'));

-- Compare each row successfully.  Another perspective on the patch overhead.
CREATE FUNCTION pg_temp.try() RETURNS void LANGUAGE plpgsql AS $$
BEGIN
	FOR i IN 1..30 LOOP
		PERFORM count(*) FROM t WHERE c = 'foo';
	END LOOP;
END
$$;
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();

-- Compare each row, failing at the length check.  As patched, we avoid
-- detoasting the packed varlena, so we might win.
CREATE FUNCTION pg_temp.tryfail() RETURNS void LANGUAGE plpgsql AS $$
BEGIN
	FOR i IN 1..30 LOOP
		PERFORM count(*) FROM t WHERE c = 'notfoo';
	END LOOP;
END
$$;
SELECT pg_temp.tryfail();
SELECT pg_temp.tryfail();
SELECT pg_temp.tryfail();
SELECT pg_temp.tryfail();
SELECT pg_temp.tryfail();
ROLLBACK;