-- The small segments (overlays) that promotions leave over the keys of an index that is not cut into partitions are merged by the same rule as in a partitioned one: the
-- segments under half the largest, the smallest that come to twice the largest of them (a size-tiered step: a row is not rewritten at every promotion),
-- when the units and segments a request for a key would no longer open, times the requests the index has had, come to half of the rewrite of their rows. The largest
-- segment is not rewritten, an index nobody reads is not merged, and a promotion of a write buffer made of many small ones ends in few segments.
CREATE EXTENSION pg_sift;
SET sift.test_col_legacy_options = on;
SET client_min_messages = warning;
SET max_parallel_maintenance_workers = 0;
SET sift.async_maintenance = off;
SET sift.col_auto_merge_wal_pct = 5;
CREATE TABLE ml (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 ml_rows(n int) RETURNS void LANGUAGE sql AS $$
  INSERT INTO ml (k, kind_id, amount) SELECT 1 + (random() * 399)::int, 1 + (random() * 6)::int, round((random() * 100)::numeric, 2) FROM generate_series(1, n) $$;
SELECT ml_rows(40000);
CREATE INDEX ml_idx ON ml USING sift_col (id, k, kind_id, amount) WITH (key_cut = k, key_cut_rows = 100);
SET sift.col_auto_merge = off;
-- two promotions of 3,000 rows each over the same keys: two overlays of 7.5% of the rows of the first segment
DO $$ BEGIN FOR i IN 1..2 LOOP PERFORM ml_rows(3000); PERFORM sift.col_promote('ml_idx'); COMMIT; END LOOP; END $$;
VACUUM (FREEZE, ANALYZE, INDEX_CLEANUP off) ml;
RESET sift.col_auto_merge;
ALTER TABLE ml RESET (autovacuum_enabled);
CREATE FUNCTION ml_segments() RETURNS bigint LANGUAGE sql AS $$ SELECT count(*) FROM sift.col_segments('ml_idx') WHERE kind = 'immutable' $$;
CREATE FUNCTION ml_units(key int) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE l text; n bigint := 0;
BEGIN
  FOR l IN EXECUTE format('EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT kind_id, count(*), sum(amount) FROM ml WHERE k = %s GROUP BY kind_id', key) LOOP
    IF l LIKE '%Units:%' THEN n := (regexp_match(l, 'Units: (\d+) read'))[1]::bigint; END IF;
  END LOOP;
  RETURN n;
END $$;
CREATE FUNCTION ml_requests(n int) RETURNS void LANGUAGE plpgsql AS $$
BEGIN FOR i IN 1..n LOOP PERFORM kind_id, count(*), sum(amount) FROM ml WHERE k = 1 + (i * 7) % 400 GROUP BY kind_id; END LOOP; END $$;
SELECT ml_segments() AS segments;
SELECT gen AS base_gen FROM sift.col_segments('ml_idx') WHERE kind = 'immutable' ORDER BY bytes DESC LIMIT 1 \gset
SELECT ml_units(7) AS units_before \gset
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 ml GROUP BY 1, 2) x \gset
RESET sift.enable_agg;
-- no one has read the index: the merge is not worth its rows
SELECT coalesce(sift.col_maintenance_due('ml_idx', true), 'not due') AS due_cold;
SELECT ml_requests(1500);
SELECT pg_stat_force_next_flush();
CHECKPOINT;
SELECT coalesce(sift.col_maintenance_due('ml_idx', true), 'not due') AS due_read;
SELECT sift.col_maintain('ml_idx') AS maintained;
-- the overlays are one, the largest segment is the one it was
SELECT ml_segments() AS segments;
SELECT count(*) = 1 AS base_kept FROM sift.col_segments('ml_idx') WHERE kind = 'immutable' AND gen = :base_gen;
SELECT coalesce(sift.col_maintenance_due('ml_idx', true), 'not due') AS due_merged;
SELECT ml_units(7) < :units_before AS fewer_units;
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 ml GROUP BY 1, 2) x;
-- a size-tiered step: a small promotion next to the merged overlay (under a tenth of it) is not worth a rewrite of that one
SET sift.col_auto_merge = off;
SELECT ml_rows(500);
SELECT sift.col_promote('ml_idx') AS promoted;
RESET sift.col_auto_merge;
SELECT ml_segments() AS segments;
SELECT ml_requests(1500);
SELECT pg_stat_force_next_flush();
SELECT coalesce(sift.col_maintenance_due('ml_idx', true), 'not due') AS due_small_next_to_large;
-- the next promotion of the size of the first overlays makes the step worth it: the three come to twice the merged one
SET sift.col_auto_merge = off;
SELECT ml_rows(6000);
SELECT sift.col_promote('ml_idx') AS promoted;
RESET sift.col_auto_merge;
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 ml GROUP BY 1, 2) x \gset
RESET sift.enable_agg;
SELECT coalesce(sift.col_maintenance_due('ml_idx', true), 'not due') AS due_next_step;
SELECT sift.col_maintain('ml_idx') AS maintained;
SELECT ml_segments() AS segments;
SELECT count(*) = 1 AS base_kept FROM sift.col_segments('ml_idx') WHERE kind = 'immutable' AND gen = :base_gen;
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 ml GROUP BY 1, 2) x;
-- a write buffer of 64 kB makes a segment of every few hundred rows while a statement inserts; the rewrite leaves few
SET sift.col_wbuf_ref = off;	-- the segments are of the pages of a buffer of values: a buffer of references holds more rows at the same limit and makes fewer
CREATE TABLE mw (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, k int NOT NULL, amount float8 NOT NULL) WITH (autovacuum_enabled = off);
CREATE INDEX mw_idx ON mw USING sift_col (id, k, amount) WITH (key_cut = k, key_cut_rows = 100, write_buffer_limit = 64);
SET sift.col_auto_merge = off;
INSERT INTO mw (k, amount) SELECT 1 + (random() * 399)::int, round((random() * 100)::numeric, 2) FROM generate_series(1, 20000);
SELECT sift.col_promote('mw_idx') IS NOT NULL AS promoted;
SELECT count(*) > 8 AS many_segments FROM sift.col_segments('mw_idx') WHERE kind = 'immutable';
SELECT sift.col_maintain('mw_idx') AS maintained;
SELECT count(*) <= 3 AS few_segments FROM sift.col_segments('mw_idx') WHERE kind = 'immutable';
SELECT sum(rows) = 20000 AS all_rows FROM sift.col_segments('mw_idx') WHERE kind = 'immutable';
RESET sift.col_wbuf_ref;
DROP FUNCTION ml_rows(int), ml_segments(), ml_units(int), ml_requests(int);
DROP TABLE ml, mw;
DROP EXTENSION pg_sift CASCADE;