-- count(DISTINCT) of a column keeps a bitmap per group, in chunks of 4096 values that a group allocates and zeroes when one of its values first falls in
-- (about 0.7 microseconds a chunk), so a query with thousands of groups of many rows pays for them while one with a hundred does not. The scan is priced for the
-- chunks while they fit the memory of the set (work_mem times hash_mem_multiplier), and only for the sets that start as bitmaps: a group of few rows (under 16
-- chunks of the range of the column, and the directory) starts as a hash set, as does a set past the memory, whose pair costs between 0.1 and 2 microseconds
-- with the column and the size of the set, which is not priced. The statements run with enable_agg on and off: the rows must be the same.
-- The workers of a scan are priced for their start (3 ms and 0.5 ms each) and, for a count(DISTINCT), for the leader merging the pairs of the sets one by one;
-- a parallel_setup_cost of 0 asks for them.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
CREATE EXTENSION pg_sift_helpers;
SET max_parallel_workers_per_gather = 2;
CREATE TABLE items (id int PRIMARY KEY, grp int NOT NULL, part int NOT NULL, cat int NOT NULL, amount float8 NOT NULL, half int NOT NULL);
SELECT setseed(0.42);
INSERT INTO items SELECT g, (random() * 4999)::int, g % 100, (random() * 999)::int, random() * 100, g % 2500 FROM generate_series(1, 100000) g;
CREATE INDEX items_grp ON items (grp);
CREATE INDEX items_col ON items USING sift_col (id, grp, part, cat, amount, half);
VACUUM (FREEZE, ANALYZE) items;

CREATE FUNCTION scan_cost(q text) RETURNS numeric LANGUAGE sql AS $$ SELECT scan_cost::numeric FROM sift.explain_why(q) WHERE scan_cost IS NOT NULL $$;
CREATE FUNCTION differences(q text) RETURNS bigint LANGUAGE sql AS $$ SELECT sift_helpers.differences_agg(q) $$;

-- 2,500 groups of 40 rows of a column of 1,000 values (one chunk a group) cost the scan well over the same groups of a column of 100,000 values, whose sets are hash
-- sets at 40 rows a group (a bitmap of 25 chunks needs 416). With 5,000 groups of 20 rows the sets of the 1,000 values start as hash sets too (a bitmap needs 32
-- rows a group): the two cost the same, as they do with 100 groups, where both are bitmaps
SELECT groups,
       scan_cost(format('SELECT %s, count(*) AS n, count(DISTINCT cat) AS d FROM items GROUP BY %s', g, g)) / scan_cost(format('SELECT %s, count(*) AS n, count(DISTINCT id) AS d FROM items GROUP BY %s', g, g)) > 1.5 AS chunks_priced,
       differences(format('SELECT %s, count(*) AS n, count(DISTINCT cat) AS d FROM items GROUP BY %s', g, g)) AS differences
FROM (VALUES ('2500 groups', 'half'), ('5000 groups', 'grp'), ('100 groups', 'part')) v(groups, g) ORDER BY groups;

-- workers planned for the scan of a table priced as 100 times larger (sift.plan_scale): a sum scales with them, a count(DISTINCT) does not (the leader merges
-- every pair), and a start that costs 3 ms keeps a scan of a few milliseconds serial whatever the setup cost; 0 asks for the workers
CREATE FUNCTION workers(q text) RETURNS int LANGUAGE plpgsql AS $$
DECLARE j jsonb;
BEGIN
	-- the table scans off, as for runs_in_scan: the workers are the scan's, not those of a parallel plan of the server's own (PostgreSQL 19 plans one for a count(DISTINCT)).
	-- PostgreSQL 19 has no hook for sift.plan_scale to scale the table the scan is compared with: its expected file (_1) has the sum serial at the setup costs above 0
	PERFORM set_config(s, 'false', true) FROM unnest(ARRAY['enable_seqscan', 'enable_indexscan', 'enable_bitmapscan', 'enable_indexonlyscan']) s;
	EXECUTE 'EXPLAIN (FORMAT JSON) ' || q INTO j;
	RETURN coalesce((SELECT max(m[1]::int) FROM regexp_matches(j::text, '"Workers Planned": (\d+)', 'g') m), 0);
END $$;
SET min_parallel_table_scan_size = 0;
SET parallel_tuple_cost = 0;
SET sift.agg_preagg = off;
SET sift.plan_scale = 100;
CREATE FUNCTION workers_at(setup int) RETURNS TABLE (setup_cost int, sum_workers int, grouped_distinct_workers int, global_distinct_workers int) LANGUAGE plpgsql AS $$
BEGIN
	PERFORM set_config('parallel_setup_cost', setup::text, true);
	RETURN QUERY SELECT setup, workers('SELECT part, sum(amount) FROM items GROUP BY part'), workers('SELECT grp, count(DISTINCT cat) FROM items GROUP BY grp'),
		workers('SELECT count(DISTINCT cat) FROM items');
END $$;
SELECT * FROM workers_at(0) UNION ALL SELECT * FROM workers_at(1) UNION ALL SELECT * FROM workers_at(100) ORDER BY 1;
RESET sift.plan_scale;
SELECT sum_workers AS small_scan_workers FROM workers_at(1);
-- at the default setup cost a group by key of a table priced as 5 times larger stays in one process (500,000 rows of this width: the 2 workers of the heap's size
-- cost 4 ms to start and 11 million instructions, more than they save); priced as 40 times larger it takes them
SET sift.plan_scale = 5;
SELECT workers('SELECT grp, count(*) AS n, sum(amount) AS s FROM items GROUP BY grp') AS workers_at_5;
SET sift.plan_scale = 40;
SELECT workers('SELECT grp, count(*) AS n, sum(amount) AS s FROM items GROUP BY grp') AS workers_at_40;
RESET sift.plan_scale;
RESET parallel_setup_cost; RESET min_parallel_table_scan_size; RESET parallel_tuple_cost; RESET sift.agg_preagg;

DROP FUNCTION scan_cost(text), differences(text), workers(text), workers_at(int);
DROP TABLE items;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;