-- A segment under half of the rows of the largest (an overlay: what a promotion of the write buffer leaves over the keys of the large ones) is written lean: without the point directories,
-- whose arrays span the rows of the whole index and cost more than the rows of the overlay, and it gets no net sidecar. Its order arrays stay (the planner reads ORDER BY .. LIMIT from them) unless they are the build's own choice and the overlays are few (col_overlay_unordered).
-- A merge that makes a large segment of it writes the full set again, and the answers are the same all along.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET sift.test_col_legacy_options = on;
SET client_min_messages = warning;
SET max_parallel_maintenance_workers = 0;
SET sift.col_auto_merge = off;
CREATE TABLE ol (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);
INSERT INTO ol (dim_a_id, kind_id, amount) SELECT 1 + g % 3000, 1 + g % 7, g % 100 FROM generate_series(1, 60000) g;
CREATE INDEX ol_idx ON ol USING sift_col (id, dim_a_id, kind_id, amount) WITH (key_cut = dim_a_id, key_cut_rows = 25);
VACUUM (FREEZE, ANALYZE) ol;
SELECT format AS large_format, bytes::numeric / rows AS large_bpr FROM sift.col_segments('ol_idx') WHERE kind = 'immutable' \gset
-- the answers of the classes the arrays serve
CREATE FUNCTION ol_answers() RETURNS text LANGUAGE sql AS $$
  SELECT md5(concat_ws('|',
    (SELECT string_agg(x::text, ',' ORDER BY x::text) FROM (SELECT kind_id, count(*), sum(amount) FROM ol WHERE dim_a_id BETWEEN 10 AND 90 GROUP BY 1) x),
    (SELECT string_agg(id::text, ',' ORDER BY id) FROM (SELECT id FROM ol WHERE dim_a_id = 7 ORDER BY amount DESC, id LIMIT 20) x),
    (SELECT count(*)::text FROM ol WHERE id IN (5, 50000, 60005, 60100)))) $$;
-- three promotions, each of 100 rows: overlays
DO $$ BEGIN FOR i IN 1..3 LOOP
  INSERT INTO ol (dim_a_id, kind_id, amount) SELECT 1 + g % 3000, 1 + g % 7, g % 100 FROM generate_series(1, 100) g;
  PERFORM sift.col_promote('ol_idx'); COMMIT;
END LOOP; END $$;
SELECT count(*) AS overlays,
       bool_and(format < :large_format) AS lean_format,
       bool_and(bytes::numeric / rows > :large_bpr) AS dearer_per_row_only_by_fixed_bytes
  FROM sift.col_segments('ol_idx') WHERE kind = 'immutable' AND rows < 1000;
 overlays | lean_format | dearer_per_row_only_by_fixed_bytes 
----------+-------------+------------------------------------
        3 | t           | t
(1 row)

SET sift.enable_agg = off;
SELECT ol_answers() AS native \gset
RESET sift.enable_agg;
SELECT ol_answers() = :'native' AS same_answers_with_overlays;
 same_answers_with_overlays 
----------------------------
 t
(1 row)

-- dead rows in the overlays and in the large segment: only the large one gets a net sidecar (its bytes grow), the overlays are left as they are
CREATE TEMP TABLE before AS SELECT gen, rows, bytes FROM sift.col_segments('ol_idx') WHERE kind = 'immutable';
DELETE FROM ol WHERE id % 11 = 0 AND id > 60000 OR id % 11 = 1 AND id < 5000;
VACUUM ol;
SELECT b.rows < 1000 AS overlay, b.bytes = s.bytes AS bytes_kept, s.dirty
  FROM before b JOIN sift.col_segments('ol_idx') s USING (gen) ORDER BY b.rows < 1000, b.gen;
 overlay | bytes_kept | dirty 
---------+------------+-------
 f       | f          | t
 t       | t          | t
 t       | t          | t
 t       | t          | t
(4 rows)

SET sift.enable_agg = off;
SELECT ol_answers() AS native_after_delete \gset
RESET sift.enable_agg;
SELECT ol_answers() = :'native_after_delete' AS same_answers_after_delete;
 same_answers_after_delete 
---------------------------
 t
(1 row)

-- an index with an order array keeps it in the overlay: the top-N reads the order
CREATE TABLE og (id int, ts timestamp);
INSERT INTO og SELECT i, TIMESTAMP '2026-01-01' + (i * 7919 % 10007) * INTERVAL '1 minute' FROM generate_series(1, 20000) i;
CREATE INDEX og_ix ON og USING sift_col (id, ts) WITH (key_cut = 'off', order_columns = 'ts', order_array_columns = 'ts');
VACUUM (FREEZE, ANALYZE) og;
INSERT INTO og SELECT 20000 + i, TIMESTAMP '2026-02-01' + i * INTERVAL '1 minute' FROM generate_series(1, 300) i;
SELECT sift.col_promote('og_ix') >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT count(*) AS overlays FROM sift.col_segments('og_ix') WHERE kind = 'immutable' AND rows < 1000;
 overlays 
----------
        1
(1 row)

SET enable_sort = off;
SELECT sift_helpers.plan_reads_global_order('SELECT id FROM og ORDER BY ts DESC LIMIT 20') AS top_n_reads_the_order;
 top_n_reads_the_order 
-----------------------
 f
(1 row)

RESET enable_sort;
DROP TABLE og;
-- the merge into one segment writes the full set: the large format again
SELECT sift.col_merge('ol_idx', true) > 0 AS merged;
 merged 
--------
 t
(1 row)

SELECT count(*) AS segments, bool_and(format = :large_format) AS full_format FROM sift.col_segments('ol_idx') WHERE kind = 'immutable';
 segments | full_format 
----------+-------------
        1 | t
(1 row)

SELECT ol_answers() = :'native_after_delete' AS same_answers_merged;
 same_answers_merged 
---------------------
 t
(1 row)

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

DROP FUNCTION ol_answers();
DROP TABLE ol;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift CASCADE;