-- sift.plan_scale plans as if the tables with a sift_col index had that many times the pages and rows, to see the choice of the planner at a larger size
-- without the data: the cost of the scan and of the normal plan, each by the model's own terms (a hash aggregate that stops fitting work_mem grows faster
-- than the rows, a scan of units does not). Only the plan changes: the rows are those of the table.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET work_mem = '4MB';
CREATE TABLE items (id int PRIMARY KEY, grp int NOT NULL, part int NOT NULL, amount float8 NOT NULL);
SELECT setseed(0.42);
INSERT INTO items SELECT g, (random() * 50000)::int, g % 100, random() * 100 FROM generate_series(1, 100000) g;
CREATE INDEX items_col ON items USING sift_col (id, grp, part, amount);
VACUUM (FREEZE, ANALYZE) items;

CREATE FUNCTION costs(q text, scale float8) RETURNS TABLE (scan numeric, normal numeric) LANGUAGE plpgsql AS $$
BEGIN
	PERFORM set_config('sift.plan_scale', scale::text, true);
	RETURN QUERY SELECT e.scan_cost::numeric, e.normal_cost::numeric FROM sift.explain_why(q) e LIMIT 1;
END $$;

SELECT current_setting('sift.plan_scale') AS default_scale;

-- 100 groups: the normal plan grows with the rows (8 to 12 times for 10 times the table), the scan less (a fixed start-up, and the pre-aggregates answer a unit whatever its rows)
SELECT s10.normal / s1.normal BETWEEN 8 AND 12 AS normal_linear, s100.scan / s1.scan < 80 AS scan_flatter
FROM costs('SELECT part, count(*), sum(amount) FROM items GROUP BY part', 1) s1, costs('SELECT part, count(*), sum(amount) FROM items GROUP BY part', 10) s10,
     costs('SELECT part, count(*), sum(amount) FROM items GROUP BY part', 100) s100;

-- 50,000 groups: past 100 times the rows the hash aggregate of the normal plan and the group table of the scan outgrow work_mem, and both grow faster than the table
SELECT s100.normal / s1.normal > 110 AS normal_superlinear, s100.scan / s1.scan > 110 AS scan_superlinear
FROM costs('SELECT grp, count(*), sum(amount) FROM items GROUP BY grp', 1) s1, costs('SELECT grp, count(*), sum(amount) FROM items GROUP BY grp', 100) s100;

-- the plan at a larger size reads the same rows
SET sift.plan_scale = 100;
SELECT count(*) = (SELECT count(DISTINCT grp) FROM items) AS same_groups, sum(n) = (SELECT count(*) FROM items) AS same_rows FROM (SELECT grp, count(*) AS n FROM items GROUP BY grp) x;
RESET sift.plan_scale;

DROP FUNCTION costs(text, float8);
DROP TABLE items;
DROP EXTENSION pg_sift;