bench-texteq.sql

\timing on

-- Benchmark "texteq".  Creates a table having a single "text" column and
-- inserts a small number of rows, each having the same length (~10 MiB; the
-- data does compress over 98%).  The datums differ only in the last few
-- characters.  Then, compare to another, similar string.

BEGIN;
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'));

CREATE FUNCTION pg_temp.try() RETURNS void LANGUAGE plpgsql AS $$
BEGIN
	FOR i IN 1..15 LOOP
		PERFORM count(*) FROM t WHERE c = repeat('foobarbazz', 1024 * 1024) || to_char(0, '000000');
	END LOOP;
END
$$;
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
SELECT pg_temp.try();
ROLLBACK;