-- The merge of overlays is a size-tiered step (the merged one is at least twice the largest it takes), and more overlays alive than a read is to open a unit in (four) are merged
-- whatever their sizes, the smallest first, once the index has been read: overlays that grow by a factor of three each are no step by size, five of them are one over the cap.
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 = 4000;
SET sift.col_auto_merge_wal_pct = 5;
CREATE TABLE oc (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 oc_rows(n int, keys int) RETURNS void LANGUAGE sql AS $$
  INSERT INTO oc (dim_a_id, kind_id, amount) SELECT 1 + g % keys, 1 + g % 7, g % 100 FROM generate_series(1, n) g $$;
SELECT oc_rows(60000, 3000);
CREATE INDEX oc_idx ON oc 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
DELETE FROM oc WHERE id % 97 = 0;
UPDATE oc SET amount = amount + 1 WHERE id % 89 = 0;
VACUUM (FREEZE, ANALYZE) oc;
SET sift.col_auto_merge = off;
DO $$ DECLARE n int; BEGIN FOREACH n IN ARRAY ARRAY[150, 600, 3500, 8000, 25000] LOOP PERFORM oc_rows(n, 12); PERFORM sift.col_promote('oc_idx'); COMMIT; END LOOP; END $$;
VACUUM (FREEZE, ANALYZE, INDEX_CLEANUP off) oc;
RESET sift.col_auto_merge;
CREATE FUNCTION oc_overlays() RETURNS bigint LANGUAGE sql AS $$ SELECT count(*) FROM sift.col_segments('oc_idx') WHERE kind = 'immutable' AND rows < 50000 $$;
CREATE FUNCTION oc_requests(n int) RETURNS void LANGUAGE plpgsql AS $$
BEGIN FOR i IN 1..n LOOP PERFORM kind_id, count(*), sum(amount) FROM oc WHERE dim_a_id = 1 + i % 12 GROUP BY kind_id; END LOOP; END $$;
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(*), sum(amount) FROM oc WHERE dim_a_id <= 40 GROUP BY 1, 2) x \gset
RESET sift.enable_agg;
SELECT oc_overlays() AS overlays;
-- no one has read the index: the overlays are left alone
SELECT sift.col_maintain('oc_idx') AS maintained_cold;
SELECT oc_overlays() AS overlays_cold;
SELECT oc_requests(200);
SELECT pg_stat_force_next_flush();
SELECT sift.col_maintain('oc_idx') AS maintained_read;
SELECT oc_overlays() <= 4 AS within_cap;
SELECT md5(string_agg(x::text, ',' ORDER BY x::text)) = :'native' AS same_rows FROM (SELECT dim_a_id, kind_id, count(*), sum(amount) FROM oc WHERE dim_a_id <= 40 GROUP BY 1, 2) x;
SELECT sift.col_verify('oc_idx', true);
DROP FUNCTION oc_rows(int, int), oc_overlays(), oc_requests(int);
DROP TABLE oc;
DROP EXTENSION pg_sift CASCADE;