-- A unit that a request reads for one key costs what it takes to open it and the rows it scans to find the key's: an overlay of a promotion holds its rows in units as wide as the
-- index's (here one unit of 30,000 rows over the 400 keys), so that sparing the request that unit is worth its rows and not the 46,000 instructions of a narrow one. The merge of
-- two such overlays is due after hundreds of requests, not the thousands the units alone would ask for.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_maintenance_workers = 0;
SET sift.col_auto_merge_wal_pct = 5;
CREATE TABLE wu (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, k int NOT NULL, kind_id int NOT NULL, amount float8 NOT NULL) WITH (autovacuum_enabled = off);
CREATE FUNCTION wu_rows(n int) RETURNS void LANGUAGE sql AS $$
  INSERT INTO wu (k, kind_id, amount) SELECT 1 + (random() * 399)::int, 1 + (random() * 6)::int, round((random() * 100)::numeric, 2) FROM generate_series(1, n) $$;
SELECT wu_rows(400000);
CREATE INDEX wu_idx ON wu USING sift_col (id, k, kind_id, amount) WITH (key_cut = k);
SET sift.col_auto_merge = off;
DO $$ BEGIN FOR i IN 1..2 LOOP PERFORM wu_rows(30000); PERFORM sift.col_promote('wu_idx'); COMMIT; END LOOP; END $$;
VACUUM (FREEZE, ANALYZE, INDEX_CLEANUP off) wu;
RESET sift.col_auto_merge;
ALTER TABLE wu RESET (autovacuum_enabled);
CREATE FUNCTION wu_segments() RETURNS bigint LANGUAGE sql AS $$ SELECT count(*) FROM sift.col_segments('wu_idx') WHERE kind = 'immutable' $$;
CREATE FUNCTION wu_requests(n int) RETURNS void LANGUAGE plpgsql AS $$
BEGIN FOR i IN 1..n LOOP PERFORM kind_id, count(*), sum(amount) FROM wu WHERE k = 1 + (i * 7) % 400 GROUP BY kind_id; END LOOP; END $$;
SELECT wu_segments() AS segments;
SET sift.enable_agg = off;
SELECT md5(string_agg(x::text, ',' ORDER BY x::text)) AS native FROM (SELECT k, kind_id, count(*), round(sum(amount)::numeric, 2) FROM wu GROUP BY 1, 2) x \gset
RESET sift.enable_agg;
SELECT coalesce(sift.col_maintenance_due('wu_idx', true), 'not due') AS due_cold;
SELECT wu_requests(300);
SELECT pg_stat_force_next_flush();
CHECKPOINT;
SELECT coalesce(sift.col_maintenance_due('wu_idx', true), 'not due') AS due_300;
SELECT wu_requests(300);
SELECT pg_stat_force_next_flush();
CHECKPOINT;
SELECT coalesce(sift.col_maintenance_due('wu_idx', true), 'not due') AS due_600;
SELECT sift.col_maintain('wu_idx') AS maintained;
SELECT wu_segments() AS segments;
SELECT coalesce(sift.col_maintenance_due('wu_idx', true), 'not due') AS due_merged;
SELECT md5(string_agg(x::text, ',' ORDER BY x::text)) = :'native' AS same_rows FROM (SELECT k, kind_id, count(*), round(sum(amount)::numeric, 2) FROM wu GROUP BY 1, 2) x;
DROP FUNCTION wu_rows(int), wu_segments(), wu_requests(int);
DROP TABLE wu;
DROP EXTENSION pg_sift CASCADE;