-- key_cut: the units of a sift_col index are cut by the range of a declared key, not by superblock, and list the heap
-- addresses of their rows. A lookup or a range of the key reads the units of its range whatever the order of the heap; the rows are those of the normal plan after
-- rows in a write buffer, a promotion, deletes and updates, VACUUM, a merge, a refresh and a REINDEX; a lowered sift.col_format_minor writes superblock units again.
CREATE EXTENSION pg_sift;
SET default_statistics_target = 10000;
SET client_min_messages = warning;
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 10000; SET random_page_cost = 10000;
-- 600 customers in a random heap order, 100,000 rows
CREATE TABLE kc (id int, cust int, d date, amount int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO kc SELECT i, (i * 7919) % 600, date '2026-01-01' + (i * 31) % 300, i % 100, repeat('x', 100) FROM generate_series(1, 100000) i;
CREATE INDEX kc_idx ON kc USING sift_col (id, cust, d, amount) WITH (point_tid_columns = 'off', dead_rewrite_pct = 10, key_cut = 'cust, d');
CREATE TABLE kp (LIKE kc);
INSERT INTO kp SELECT * FROM kc;
CREATE INDEX kp_idx ON kp USING sift_col (id, cust, d, amount) WITH (point_tid_columns = 'off', dead_rewrite_pct = 10);
VACUUM (FREEZE, ANALYZE) kc;
VACUUM (FREEZE, ANALYZE) kp;
SELECT sift.col_verify('kc_idx', true);
 col_verify 
------------
 
(1 row)

-- the segment is column format minor 17 with units of about the rows of a superblock; the index without the option is not
SELECT kind, format, units <= 20 AS superblock_units FROM sift.col_segments('kc_idx') WHERE kind = 'immutable';
   kind    | format | superblock_units 
-----------+--------+------------------
 immutable |     38 | t
(1 row)

SELECT kind, format FROM sift.col_segments('kp_idx') WHERE kind = 'immutable';
   kind    | format 
-----------+--------
 immutable |     38
(1 row)

CREATE FUNCTION kchk(step text) RETURNS TABLE (after_step text, shape text, result text) LANGUAGE plpgsql AS $$
DECLARE r record; a text; b text; u text; l text; q text;
BEGIN
	FOR r IN SELECT * FROM (VALUES
		('one customer', 'SELECT count(*), sum(amount), min(id) FROM kc WHERE cust = 42'),
		('customer and dates', 'SELECT count(*), sum(amount), min(id) FROM kc WHERE cust = 42 AND d >= date ''2026-03-01'' AND d < date ''2026-05-01'''),
		('customer range', 'SELECT count(*), sum(amount), min(id) FROM kc WHERE cust BETWEEN 100 AND 103'),
		('customer list', 'SELECT count(*), sum(amount), min(id) FROM kc WHERE cust IN (7, 300, 599)'),
		('other column', 'SELECT count(*), sum(amount) FROM kc WHERE amount = 5'),
		('top-N by key', 'SELECT id, cust, d FROM kc ORDER BY cust, d, id LIMIT 5')) v(s, qq)
	LOOP
		q := r.qq; u := '';
		FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) ' || q LOOP
			IF l LIKE '%Units: %' THEN u := u || ' ' || trim(l); END IF;
		END LOOP;
		EXECUTE format('SELECT string_agg(x::text, E'';'') FROM (%s) x', q) INTO a;
		SET LOCAL sift.enable_agg = off; SET LOCAL sift.enable_topn = off;
		EXECUTE format('SELECT string_agg(x::text, E'';'') FROM (%s) x', q) INTO b;
		RESET sift.enable_agg; RESET sift.enable_topn;
		after_step := step; shape := r.s;
		result := CASE WHEN a IS NOT DISTINCT FROM b THEN 'same' ELSE 'DIFFERENT' END || u;
		RETURN NEXT;
	END LOOP;
END $$;
SELECT * FROM kchk('built');
 after_step |       shape        |                       result                        
------------+--------------------+-----------------------------------------------------
 built      | one customer       | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 built      | customer and dates | same Units: 1 read, 7 pruned, 0 from pre-aggregates
 built      | customer range     | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 built      | customer list      | same Units: 3 read, 5 pruned, 3 from pre-aggregates
 built      | other column       | same Units: 8 read, 0 from pre-aggregates
 built      | top-N by key       | same Units: 8 read
(6 rows)

-- the same customer in the index with superblock units: the random heap puts its rows in every unit
CREATE FUNCTION kunits(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; u text := '';
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) ' || q LOOP
		IF l LIKE '%Units: %' THEN u := u || ' ' || trim(l); END IF;
	END LOOP;
	RETURN trim(u);
END $$;
SELECT kunits('SELECT count(*), sum(amount) FROM kp WHERE cust = 42') AS superblock_units;
           superblock_units           
--------------------------------------
 Units: 8 read, 0 from pre-aggregates
(1 row)

INSERT INTO kc SELECT 200000 + i, 42, date '2026-04-01', 7, 'y' FROM generate_series(1, 30) i;
SELECT * FROM kchk('write buffer');
  after_step  |       shape        |                                         result                                          
--------------+--------------------+-----------------------------------------------------------------------------------------
 write buffer | one customer       | same Units: 2 read, 7 pruned, 0 from pre-aggregates
 write buffer | customer and dates | same Units: 2 read, 7 pruned, 0 from pre-aggregates
 write buffer | customer range     | same Units: 2 read, 7 pruned, 0 from pre-aggregates
 write buffer | customer list      | same Units: 4 read, 5 pruned, 1 from pre-aggregates
 write buffer | other column       | same Units: 9 read, 0 from pre-aggregates, 0 net of dead rows, 7 with dead rows scanned
 write buffer | top-N by key       | same Units: 9 read
(6 rows)

SELECT sift.col_promote('kc_idx') > 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT * FROM kchk('promoted');
 after_step |       shape        |                                              result                                               
------------+--------------------+---------------------------------------------------------------------------------------------------
 promoted   | one customer       | same Units: 2 read, 7 pruned, 0 from pre-aggregates
 promoted   | customer and dates | same Units: 2 read, 7 pruned, 0 from pre-aggregates
 promoted   | customer range     | same Units: 1 read, 8 pruned, 0 from pre-aggregates
 promoted   | customer list      | same Units: 3 read, 6 pruned, 1 from pre-aggregates
 promoted   | other column       | same Units: 8 read, 1 pruned, 0 from pre-aggregates, 0 net of dead rows, 6 with dead rows scanned
 promoted   | top-N by key       | same Units: 9 read
(6 rows)

DELETE FROM kc WHERE id % 7 = 0;
UPDATE kc SET amount = amount + 1, cust = 42 WHERE id % 1009 = 0;
SELECT * FROM kchk('deleted and updated');
     after_step      |       shape        |                                               result                                                
---------------------+--------------------+-----------------------------------------------------------------------------------------------------
 deleted and updated | one customer       | same Units: 10 read, 7 pruned, 0 from pre-aggregates
 deleted and updated | customer and dates | same Units: 10 read, 7 pruned, 0 from pre-aggregates
 deleted and updated | customer range     | same Units: 9 read, 8 pruned, 0 from pre-aggregates
 deleted and updated | customer list      | same Units: 11 read, 6 pruned, 0 from pre-aggregates
 deleted and updated | other column       | same Units: 16 read, 1 pruned, 0 from pre-aggregates, 0 net of dead rows, 16 with dead rows scanned
 deleted and updated | top-N by key       | same Units: 17 read
(6 rows)

VACUUM kc;
SELECT * FROM kchk('vacuumed');
 after_step |       shape        |                                               result                                               
------------+--------------------+----------------------------------------------------------------------------------------------------
 vacuumed   | one customer       | same Units: 10 read, 6 pruned, 2 from pre-aggregates
 vacuumed   | customer and dates | same Units: 10 read, 6 pruned, 1 from pre-aggregates
 vacuumed   | customer range     | same Units: 9 read, 7 pruned, 1 from pre-aggregates
 vacuumed   | customer list      | same Units: 11 read, 5 pruned, 3 from pre-aggregates
 vacuumed   | other column       | same Units: 15 read, 1 pruned, 0 from pre-aggregates, 0 net of dead rows, 8 with dead rows scanned
 vacuumed   | top-N by key       | same Units: 16 read
(6 rows)

SELECT sift.col_merge('kc_idx', true) >= 0 AS merged;
 merged 
--------
 t
(1 row)

SELECT * FROM kchk('merged');
 after_step |       shape        |                       result                        
------------+--------------------+-----------------------------------------------------
 merged     | one customer       | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 merged     | customer and dates | same Units: 1 read, 7 pruned, 0 from pre-aggregates
 merged     | customer range     | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 merged     | customer list      | same Units: 3 read, 5 pruned, 3 from pre-aggregates
 merged     | other column       | same Units: 8 read, 0 from pre-aggregates
 merged     | top-N by key       | same Units: 8 read
(6 rows)

SELECT count(*) >= 0 AS refreshed FROM sift.col_refresh('kc_idx');
 refreshed 
-----------
 t
(1 row)

SELECT * FROM kchk('refreshed');
 after_step |       shape        |                       result                        
------------+--------------------+-----------------------------------------------------
 refreshed  | one customer       | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 refreshed  | customer and dates | same Units: 1 read, 7 pruned, 0 from pre-aggregates
 refreshed  | customer range     | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 refreshed  | customer list      | same Units: 3 read, 5 pruned, 3 from pre-aggregates
 refreshed  | other column       | same Units: 8 read, 0 from pre-aggregates
 refreshed  | top-N by key       | same Units: 8 read
(6 rows)

REINDEX INDEX kc_idx;
SELECT * FROM kchk('reindexed');
 after_step |       shape        |                       result                        
------------+--------------------+-----------------------------------------------------
 reindexed  | one customer       | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 reindexed  | customer and dates | same Units: 1 read, 7 pruned, 0 from pre-aggregates
 reindexed  | customer range     | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 reindexed  | customer list      | same Units: 3 read, 5 pruned, 3 from pre-aggregates
 reindexed  | other column       | same Units: 8 read, 0 from pre-aggregates
 reindexed  | top-N by key       | same Units: 8 read
(6 rows)

SELECT sift.col_verify('kc_idx', true);
 col_verify 
------------
 
(1 row)

SELECT DISTINCT format FROM sift.col_segments('kc_idx') WHERE kind = 'immutable';
 format 
--------
     44
(1 row)

-- the setting that rolls a 3.3 index back to a release that cannot read minor 17 writes superblock units again
SET sift.col_format_minor = 14;
REINDEX INDEX kc_idx;
SELECT DISTINCT format FROM sift.col_segments('kc_idx') WHERE kind = 'immutable';
 format 
--------
     12
(1 row)

SELECT * FROM kchk('lowered setting');
   after_step    |       shape        |                  result                   
-----------------+--------------------+-------------------------------------------
 lowered setting | one customer       | same Units: 8 read, 0 from pre-aggregates
 lowered setting | customer and dates | same Units: 8 read, 0 from pre-aggregates
 lowered setting | customer range     | same Units: 8 read, 0 from pre-aggregates
 lowered setting | customer list      | same Units: 8 read, 0 from pre-aggregates
 lowered setting | other column       | same Units: 8 read, 0 from pre-aggregates
 lowered setting | top-N by key       | same Units: 8 read
(6 rows)

RESET sift.col_format_minor;
REINDEX INDEX kc_idx;
SELECT DISTINCT format FROM sift.col_segments('kc_idx') WHERE kind = 'immutable';
 format 
--------
     44
(1 row)

-- a build that passes its memory writes a sorted segment for each run of rows, and a merge streams the runs into one segment: the rows stay those of the normal plan
DROP INDEX kc_idx;
SET sift.test_col_segment_kb = 64;
SET sift.col_build_key_ranges = off;
CREATE INDEX kc_idx ON kc USING sift_col (id, cust, d, amount) WITH (point_tid_columns = 'off', dead_rewrite_pct = 10, key_cut = 'cust, d');
RESET sift.test_col_segment_kb;
RESET sift.col_build_key_ranges;
SELECT count(*) > 1 AS runs, min(format) AS format FROM sift.col_segments('kc_idx') WHERE kind = 'immutable';
 runs | format 
------+--------
 t    |     34
(1 row)

SELECT * FROM kchk('runs');
 after_step |       shape        |                       result                        
------------+--------------------+-----------------------------------------------------
 runs       | one customer       | same Units: 8 read, 7 pruned, 0 from pre-aggregates
 runs       | customer and dates | same Units: 8 read, 7 pruned, 0 from pre-aggregates
 runs       | customer range     | same Units: 8 read, 7 pruned, 0 from pre-aggregates
 runs       | customer list      | same Units: 15 read, 7 from pre-aggregates
 runs       | other column       | same Units: 8 read, 7 pruned, 0 from pre-aggregates
 runs       | top-N by key       | same Units: 15 read
(6 rows)

SET maintenance_work_mem = '1MB';
SELECT sift.col_merge('kc_idx', true) >= 0 AS merged;
 merged 
--------
 t
(1 row)

RESET maintenance_work_mem;
SELECT count(*) AS segments, min(format) AS format FROM sift.col_segments('kc_idx') WHERE kind = 'immutable';
 segments | format 
----------+--------
        1 |     34
(1 row)

SELECT * FROM kchk('runs merged');
 after_step  |       shape        |                       result                        
-------------+--------------------+-----------------------------------------------------
 runs merged | one customer       | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 runs merged | customer and dates | same Units: 1 read, 7 pruned, 0 from pre-aggregates
 runs merged | customer range     | same Units: 1 read, 7 pruned, 1 from pre-aggregates
 runs merged | customer list      | same Units: 3 read, 5 pruned, 3 from pre-aggregates
 runs merged | other column       | same Units: 8 read, 0 from pre-aggregates
 runs merged | top-N by key       | same Units: 8 read
(6 rows)

SELECT sift.col_verify('kc_idx', true);
 col_verify 
------------
 
(1 row)

-- an index that gets the option later has segments cut by superblock: a merge loads and sorts them, and the rows written since are in key-cut segments
CREATE TABLE kl (LIKE kc);
INSERT INTO kl SELECT * FROM kc WHERE id <= 60000;
CREATE INDEX kl_idx ON kl USING sift_col (id, cust, d, amount) WITH (point_tid_columns = 'off', dead_rewrite_pct = 10);
ALTER INDEX kl_idx SET (key_cut = 'cust, d');
INSERT INTO kl SELECT * FROM kc WHERE id > 60000;
SELECT sift.col_promote('kl_idx');
 col_promote 
-------------
           1
(1 row)

SELECT string_agg(DISTINCT format::text, ',' ORDER BY format::text) AS formats FROM sift.col_segments('kl_idx') WHERE kind = 'immutable';
 formats 
---------
 12,34
(1 row)

SELECT sift.col_merge('kl_idx', true) >= 0 AS merged;
 merged 
--------
 t
(1 row)

SELECT string_agg(DISTINCT format::text, ',') AS formats, count(*) AS segments FROM sift.col_segments('kl_idx') WHERE kind = 'immutable';
 formats | segments 
---------+----------
 34      |        1
(1 row)

SELECT sift.col_verify('kl_idx', true);
 col_verify 
------------
 
(1 row)

SELECT (SELECT count(*) || ' ' || sum(amount) FROM kl WHERE cust = 42) = (SELECT count(*) || ' ' || sum(amount) FROM kc WHERE cust = 42) AS same_rows;
 same_rows 
-----------
 t
(1 row)

SELECT sift.col_verify('kc_idx', true);
 col_verify 
------------
 
(1 row)

DROP TABLE kl;
-- the rows of a table make runs (one for each superblock here), and the directory holds a fixed number of segments: the build merges the runs as it writes them
-- (a row is written once for each level it reaches), so the memory it was given is enough for a table of any size: the runs that the memory makes are 8
-- segments, and with room for 6 the build leaves fewer, holding the same rows
SET sift.test_col_segment_kb = 16;
SET sift.col_build_key_ranges = off;
CREATE INDEX kc_runs ON kc USING sift_col (id, cust, d, amount) WITH (point_tid_columns = 'off', key_cut = 'cust, d');
SET sift.test_col_max_segments = 6;
CREATE INDEX kc_big ON kc USING sift_col (id, cust, d, amount) WITH (point_tid_columns = 'off', key_cut = 'cust, d');
RESET sift.test_col_max_segments;
RESET sift.test_col_segment_kb;
RESET sift.col_build_key_ranges;
SELECT count(*) AS runs, sum(rows) AS rows FROM sift.col_segments('kc_runs') WHERE kind = 'immutable';
 runs | rows  
------+-------
    8 | 85741
(1 row)

SELECT count(*) < 6 AS merged, bool_and(format = 17) AS key_cut_format, sum(rows) AS rows FROM sift.col_segments('kc_big') WHERE kind = 'immutable';
 merged | key_cut_format | rows  
--------+----------------+-------
 t      | f              | 85741
(1 row)

SELECT sift.col_verify('kc_big', true);
 col_verify 
------------
 
(1 row)

DROP INDEX kc_big, kc_runs;
DROP FUNCTION kchk(text), kunits(text);
DROP TABLE kc, kp;
DROP EXTENSION pg_sift;