predicate_order_bench.sql

--
-- Synthetic benchmark for qual clause ordering
--
-- This script is intended only as an illustration of executor work
-- differences caused by qual reordering.  It is not a regression test.
--
-- It is best run in a superuser session because it uses pg_stat_reset()
-- and function statistics.
--

SET max_parallel_workers_per_gather = 0;
SET jit = off;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET track_functions = 'all';

DROP OPERATOR IF EXISTS #==# (int4, int4);
DROP OPERATOR IF EXISTS #=# (int4, int4);
DROP FUNCTION IF EXISTS bench_eq_light(int, int);
DROP FUNCTION IF EXISTS bench_eq_medium(int, int);
DROP TABLE IF EXISTS predicate_bench;

-- Use UNLOGGED to speed up setup.
CREATE UNLOGGED TABLE predicate_bench (
	a int,
	b int
);

INSERT INTO predicate_bench
SELECT g,
       CASE WHEN g % 100 = 0 THEN 1 ELSE 0 END
FROM generate_series(1, 5000000) AS s(g);

ANALYZE predicate_bench;

CREATE FUNCTION bench_eq_medium(int, int)
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
PARALLEL SAFE
COST 2
AS $$
DECLARE
	v int := $1;
	i int;
BEGIN
	FOR i IN 1..40 LOOP
		v := v # i;
	END LOOP;

	RETURN $1 = $2;
END;
$$;

CREATE OPERATOR #=# (
	LEFTARG = int4,
	RIGHTARG = int4,
	PROCEDURE = bench_eq_medium,
	RESTRICT = eqsel,
	JOIN = eqjoinsel
);

CREATE FUNCTION bench_eq_light(int, int)
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
PARALLEL SAFE
COST 1
AS $$
DECLARE
	v int := $1;
	i int;
BEGIN
	FOR i IN 1..20 LOOP
		v := v # i;
	END LOOP;

	RETURN $1 = $2;
END;
$$;

CREATE OPERATOR #==# (
	LEFTARG = int4,
	RIGHTARG = int4,
	PROCEDURE = bench_eq_light,
	RESTRICT = eqsel,
	JOIN = eqjoinsel
);

SELECT pg_stat_reset();

--
-- The predicate "a #=# 42" passes only one row.
-- The predicate "b #==# 0" passes about 99% of rows.
--
-- With cost-only ordering, the cheaper "b #==# 0" clause tends to stay first:
--   Filter: ((b #==# 0) AND (a #=# 42))
--
-- With cost/selectivity ordering, the more selective "a #=# 42" clause
-- should move first:
--   Filter: ((a #=# 42) AND (b #==# 0))
--
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT count(*)
FROM predicate_bench
WHERE b #==# 0
  AND a #=# 42;

--
-- Function call counts are a convenient way to observe the difference:
--
-- New ordering is expected to produce approximately:
--   bench_eq_light  |       1
--   bench_eq_medium | 5000000
--
-- Old ordering is expected to produce approximately:
--   bench_eq_light  | 5000000
--   bench_eq_medium | 4950000
--
SELECT funcname, calls
FROM pg_stat_user_functions
WHERE funcname LIKE 'bench_eq_%'
ORDER BY funcname;