-- The groups of one key stay in the direct arrays up to the memory limit of the statement (they were rehashed at 1,048,576), the arrays grow with the key
-- keeping every group's accumulators of every kind, and the groups move to the hash table past sift.test_direct_max or spill when work_mem is smaller:
-- the result is that of the normal plan in each, with 21,844 groups.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;

CREATE TABLE sk_facts (id int, k int, v int, big bigint, f float8, n numeric(12,2), d date) WITH (autovacuum_enabled = false);
INSERT INTO sk_facts
SELECT g, g % 100, g % 1000, g::bigint * 3000000000, g / 7.0, (g % 5000) / 100.0, date '2024-01-01' + g % 700
FROM generate_series(1, 2000) g;
CREATE INDEX sk_facts_col ON sk_facts USING sift_col (k, v, big, f, n, d);
VACUUM (FREEZE, ANALYZE) sk_facts;
-- the statistics are those of the first 2,000 rows (100 groups): the arrays start small and the key outgrows them
INSERT INTO sk_facts
SELECT g, (hashint4(g) & 2147483647) % 24000, g % 1000, g::bigint * 3000000000, g / 7.0, (g % 5000) / 100.0, date '2024-01-01' + g % 700
FROM generate_series(2001, 60000) g;
VACUUM (FREEZE) sk_facts;

CREATE FUNCTION groups_summary(wm text, direct_max int) RETURNS TABLE (scan text, groups bigint, same bool) LANGUAGE plpgsql AS $$
DECLARE
	q text := 'SELECT count(*) AS c, md5(string_agg(g::text, '','' ORDER BY k)) AS m FROM (SELECT k, count(*), sum(v), sum(big), round(avg(f)::numeric, 6), min(d), max(v), sum(n), count(DISTINCT v) FROM sk_facts GROUP BY k) g';
	j jsonb;
	want record;
	got record;
BEGIN
	EXECUTE format('SET LOCAL work_mem = %L', wm);
	EXECUTE format('SET LOCAL sift.test_direct_max = %s', direct_max);
	EXECUTE 'EXPLAIN (COSTS OFF, FORMAT JSON) ' || q INTO j;
	scan := CASE WHEN jsonb_path_exists(j, '$.** ? (@."Custom Plan Provider" == "SiftAgg")') THEN 'SiftAgg' ELSE 'normal' END;
	EXECUTE q INTO got;
	SET LOCAL sift.enable_agg = off;
	EXECUTE q INTO want;
	RESET sift.enable_agg;
	groups := got.c;
	same := got = want;
	RETURN NEXT;
END $$;

SELECT * FROM groups_summary('1GB', 0);
SELECT * FROM groups_summary('1GB', 5000);
SELECT * FROM groups_summary('1GB', 23999);
ANALYZE sk_facts;
SELECT * FROM groups_summary('64kB', 0);

DROP FUNCTION groups_summary(text, int);
DROP TABLE sk_facts;
DROP EXTENSION pg_sift CASCADE;