-- The plan of a rewrite (a LEFT JOIN counted from the child's groups, sift.agg_child_first) is set against the plan as written the way PostgreSQL ranks plans: the plan with fewer
-- nodes that enable_* = off disabled first, then the cost. 17 has the penalty in the cost, 18 counts the nodes apart: the choice is the same on every major, and so are the rows.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET jit = off;
CREATE TABLE cw (grp int, a int) WITH (autovacuum_enabled = false);
CREATE TABLE lk (id int PRIMARY KEY, name text);
INSERT INTO cw SELECT 1 + i % 3, i % 5 FROM generate_series(1, 600) i;
INSERT INTO cw SELECT 4, NULL FROM generate_series(1, 2);
INSERT INTO lk VALUES (1, 'one'), (2, 'two'), (3, 'three'), (5, 'five');
CREATE INDEX cw_col ON cw USING sift_col (grp, a);
VACUUM (FREEZE, ANALYZE) cw, lk;

CREATE FUNCTION set_up(settings text, scan bool, child_first bool) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
	PERFORM set_config(o, 'on', true) FROM unnest(ARRAY['enable_seqscan', 'enable_indexscan', 'enable_bitmapscan', 'enable_indexonlyscan', 'enable_hashjoin']) o;
	PERFORM set_config('sift.enable_agg', scan::text, true);
	PERFORM set_config('sift.agg_child_first', child_first::text, true);
	EXECUTE settings;
END $$;
CREATE FUNCTION plan_of(q text, settings text, child_first bool) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; r text := 'normal plan'; BEGIN
	PERFORM set_up(settings, true, child_first);
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF l ~ 'Custom Scan \(SiftAgg\)' THEN r := 'scan'; END IF;
	END LOOP;
	RETURN r;
END $$;
CREATE FUNCTION answer(q text, settings text, scan bool) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r text; BEGIN
	PERFORM set_up(settings, scan, true);
	EXECUTE 'SELECT string_agg(t::text, '';'' ORDER BY t::text) FROM (' || q || ') t' INTO r;
	RETURN r;
END $$;

SELECT s.name AS settings, q.name AS statement, plan_of(q.sql, s.sets, true) AS child_first_on, plan_of(q.sql, s.sets, false) AS child_first_off,
       answer(q.sql, s.sets, true) = answer(q.sql, s.sets, false) AS same_rows
FROM (VALUES
	('default', 'SELECT 1'),
	('seqscan off', 'SET LOCAL enable_seqscan = off'),
	('seqscan, hashjoin off', 'SET LOCAL enable_seqscan = off; SET LOCAL enable_hashjoin = off'),
	('every table scan off', 'SET LOCAL enable_seqscan = off; SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; SET LOCAL enable_indexonlyscan = off')
) s (name, sets),
(VALUES
	('the table as the nullable side', 'SELECT l.id, count(e) AS c, count(*) AS s FROM lk l LEFT JOIN cw e ON e.grp = l.id GROUP BY 1'),
	('a list of values', 'SELECT v.g, count(e) AS c FROM (VALUES (1), (2), (9)) v (g) LEFT JOIN cw e ON e.grp = v.g GROUP BY 1')
) q (name, sql)
ORDER BY 1, 2;

DROP FUNCTION answer(text, text, bool), plan_of(text, text, bool), set_up(text, bool, bool);
DROP TABLE cw, lk;
DROP EXTENSION pg_sift;