-- A key-cut index in partitions that takes inserts all day collects small segments (overlays) over the keys the new rows are for: a request for such a key opens a unit in
-- each of them. The rule that weighs the units a merge of the small overlays would spare the requests the index has had (idx_scan, which the aggregate scan counts too)
-- against the rows the merge rewrites says when it is due, and the merge takes the overlays alone: the partition stays.
CREATE EXTENSION pg_sift;
SET sift.test_col_legacy_options = on;
SET client_min_messages = warning;
SET max_parallel_maintenance_workers = 0;
SET sift.col_auto_merge_partition_kb = 400;
-- the budget of the churn (sift.col_auto_merge_wal_pct) does not hold the merge of the overlays: a server that writes nothing else is when it costs least
SET sift.col_auto_merge_wal_pct = 5;
CREATE TABLE ov (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dim_a_id int NOT NULL, kind_id int NOT NULL, amount float8 NOT NULL) WITH (autovacuum_enabled = off);
CREATE FUNCTION ov_rows(n int, keys int) RETURNS void LANGUAGE sql AS $$
  INSERT INTO ov (dim_a_id, kind_id, amount) SELECT 1 + (random() * (keys - 1))::int, 1 + (random() * 6)::int, round((random() * 100)::numeric, 2) FROM generate_series(1, n) $$;
SELECT ov_rows(60000, 3000);
 ov_rows 
---------
 
(1 row)

CREATE INDEX ov_idx ON ov USING sift_col (id, dim_a_id, kind_id, amount) WITH (key_cut = dim_a_id, key_cut_rows = 25);
-- the partition holds dead rows (it is dirty): the merge of the overlays leaves it as it is, whatever the rewrite of a dirty segment would do
DELETE FROM ov WHERE id % 97 = 0;
UPDATE ov SET amount = amount + 1 WHERE id % 89 = 0;
VACUUM (FREEZE, ANALYZE) ov;
SET sift.col_auto_merge = off;
DO $$ BEGIN FOR i IN 1..12 LOOP PERFORM ov_rows(150, 12); PERFORM sift.col_promote('ov_idx'); COMMIT; END LOOP; END $$;
VACUUM (FREEZE, ANALYZE, INDEX_CLEANUP off) ov;
RESET sift.col_auto_merge;
CREATE FUNCTION ov_segments() RETURNS bigint LANGUAGE sql AS $$ SELECT count(*) FROM sift.col_segments('ov_idx') WHERE kind = 'immutable' $$;
CREATE FUNCTION ov_units(k 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 ov WHERE dim_a_id = %s GROUP BY kind_id', k) LOOP
    IF l LIKE '%Units:%' THEN n := (regexp_match(l, 'Units: (\d+) read'))[1]::bigint; END IF;
  END LOOP;
  RETURN n;
END $$;
CREATE FUNCTION ov_requests(n int) RETURNS void LANGUAGE plpgsql AS $$
BEGIN FOR i IN 1..n LOOP PERFORM kind_id, count(*), sum(amount) FROM ov WHERE dim_a_id = 1 + i % 12 GROUP BY kind_id; END LOOP; END $$;
SELECT ov_segments() AS segments;
 segments 
----------
       13
(1 row)

SELECT dirty AS partition_dirty, gen AS part_gen FROM sift.col_segments('ov_idx') WHERE kind = 'immutable' AND rows > 50000 \gset
SELECT :'partition_dirty' AS partition_dirty;
 partition_dirty 
-----------------
 t
(1 row)

-- no one has read the index: the merge is not worth its rows
SELECT coalesce(sift.col_maintenance_due('ov_idx', true), 'not due') AS due_cold;
 due_cold 
----------
 not due
(1 row)

SELECT sift.col_maintain('ov_idx') AS maintained_cold;
 maintained_cold 
-----------------
 f
(1 row)

SELECT ov_segments() AS segments;
 segments 
----------
       13
(1 row)

SELECT ov_units(5) AS units_before \gset
SET sift.enable_agg = off;
SELECT md5(string_agg(x::text, ',' ORDER BY x::text)) AS native FROM (SELECT dim_a_id, kind_id, count(*), round(sum(amount)::numeric, 2) FROM ov WHERE dim_a_id <= 40 GROUP BY 1, 2) x \gset
RESET sift.enable_agg;
SELECT ov_requests(200);
 ov_requests 
-------------
 
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

CHECKPOINT;
SELECT coalesce(sift.col_maintenance_due('ov_idx', true), 'not due') AS due_read;
   due_read    
---------------
 overlay units
(1 row)

SELECT pg_current_wal_lsn() AS wal0, (SELECT sum(bytes) FROM sift.col_segments('ov_idx') WHERE kind = 'immutable' AND rows < 50000) AS overlay_bytes \gset
SELECT sift.col_maintain('ov_idx') AS maintained;
 maintained 
------------
 t
(1 row)

-- the pass writes about the bytes of the overlays, not those of the partition (a rewrite of the dirty partition was ten times that)
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'wal0') < 3 * :overlay_bytes AS wal_within_overlays;
 wal_within_overlays 
---------------------
 t
(1 row)

-- the overlays are one, the partition is the one it was
SELECT ov_segments() AS segments;
 segments 
----------
        4
(1 row)

SELECT coalesce(sift.col_maintenance_due('ov_idx', true), 'not due') AS due_merged;
 due_merged 
------------
 not due
(1 row)

SELECT count(*) = 1 AS partition_kept FROM sift.col_segments('ov_idx') WHERE kind = 'immutable' AND gen = :part_gen;
 partition_kept 
----------------
 t
(1 row)

SELECT ov_units(5) < :units_before AS fewer_units;
 fewer_units 
-------------
 t
(1 row)

SELECT md5(string_agg(x::text, ',' ORDER BY x::text)) = :'native' AS same_rows FROM (SELECT dim_a_id, kind_id, count(*), round(sum(amount)::numeric, 2) FROM ov WHERE dim_a_id <= 40 GROUP BY 1, 2) x;
 same_rows 
-----------
 t
(1 row)

DROP FUNCTION ov_rows(int, int), ov_segments(), ov_units(int), ov_requests(int);
DROP TABLE ov;
DROP EXTENSION pg_sift CASCADE;