-- The head of a unit (its row directory and chunk table) is read in 2 kB and the rest of it when the directory ends beyond: a unit of dense pages fits, a unit whose pages
-- hold the rows of every other offset has a bitmap for each page and does not (256 pages of 140 rows: 9 kB). A key-cut unit has its chunk table first and reads its directory when a
-- scan needs the pages or the TIDs. The answers are those of the normal plan in all of them, before and after the changes of a unit.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET client_min_messages = warning;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET sift.agg_preagg = off;
SET sift.agg_force = on;
CREATE FUNCTION same(q text) RETURNS TABLE (result_rows bigint, vs_normal_plan bigint) LANGUAGE sql AS $$ SELECT * FROM sift_helpers.agg_rows_and_vs_normal_in_session(q) $$;
-- dense pages: the head fits the first read
CREATE TABLE uh_dense (id int, grp int, amount int, pad text) WITH (autovacuum_enabled = off);
INSERT INTO uh_dense SELECT g, g % 7, (g * 31) % 1000, 'p' FROM generate_series(1, 60000) g;
CREATE INDEX uh_dense_i ON uh_dense USING sift_col (id, grp, amount);
-- sparse pages: every other row deleted and vacuumed, so each page keeps its offsets up to the highest and a bitmap of them
CREATE TABLE uh_sparse (id int, grp int, amount int) WITH (autovacuum_enabled = off, fillfactor = 100);
INSERT INTO uh_sparse SELECT g, g % 7, (g * 31) % 1000 FROM generate_series(1, 240000) g;
DELETE FROM uh_sparse WHERE id % 2 = 0;
VACUUM uh_sparse;
CREATE INDEX uh_sparse_i ON uh_sparse USING sift_col (id, grp, amount);
-- a key-cut index
CREATE TABLE uh_cut (id int, tenant int, amount int) WITH (autovacuum_enabled = off);
INSERT INTO uh_cut SELECT g, g % 40, (g * 31) % 1000 FROM generate_series(1, 120000) g;
CREATE INDEX uh_cut_i ON uh_cut USING sift_col (id, tenant, amount) WITH (key_cut = 'tenant');
SELECT sift.col_verify('uh_dense_i', true), sift.col_verify('uh_sparse_i', true), sift.col_verify('uh_cut_i', true);
 col_verify | col_verify | col_verify 
------------+------------+------------
            |            | 
(1 row)

CREATE TABLE q (name text, q text);
INSERT INTO q VALUES
	('dense all units', 'SELECT grp, count(*), sum(amount) FROM uh_dense GROUP BY 1'),
	('dense range', 'SELECT grp, count(*), sum(amount) FROM uh_dense WHERE id BETWEEN 5000 AND 9000 GROUP BY 1'),
	('sparse all units', 'SELECT grp, count(*), sum(amount) FROM uh_sparse GROUP BY 1'),
	('sparse range', 'SELECT grp, count(*), sum(amount) FROM uh_sparse WHERE id BETWEEN 5001 AND 90001 GROUP BY 1'),
	('sparse filter on a measure', 'SELECT grp, count(*), sum(amount) FROM uh_sparse WHERE amount < 40 GROUP BY 1'),
	('sparse ids', 'SELECT count(*), sum(id) FROM uh_sparse WHERE id % 1000 = 1'),
	('key cut tenant', 'SELECT tenant, count(*), sum(amount) FROM uh_cut GROUP BY 1'),
	('key cut one tenant', 'SELECT count(*), sum(amount) FROM uh_cut WHERE tenant = 7');
SELECT name, (same(q)).* FROM q ORDER BY name;
            name            | result_rows | vs_normal_plan 
----------------------------+-------------+----------------
 dense all units            |           7 |              0
 dense range                |           7 |              0
 key cut one tenant         |           1 |              0
 key cut tenant             |          40 |              0
 sparse all units           |           7 |              0
 sparse filter on a measure |           7 |              0
 sparse ids                 |           1 |              0
 sparse range               |           7 |              0
(8 rows)

-- a key-cut unit has its chunk table before its directory (column format 34): the scan reads the head only while the pages of the unit are all visible, and the directory when a page is not
SELECT DISTINCT format FROM sift.col_segments('uh_cut_i') WHERE kind = 'immutable';
 format 
--------
     44
(1 row)

VACUUM uh_cut;
SELECT name, (same(q)).* FROM q WHERE name LIKE 'key cut%' ORDER BY name;
        name        | result_rows | vs_normal_plan 
--------------------+-------------+----------------
 key cut one tenant |           1 |              0
 key cut tenant     |          40 |              0
(2 rows)

UPDATE uh_cut SET amount = amount + 1 WHERE id % 97 = 0;
SELECT name, (same(q)).* FROM q WHERE name LIKE 'key cut%' ORDER BY name;
        name        | result_rows | vs_normal_plan 
--------------------+-------------+----------------
 key cut one tenant |           1 |              0
 key cut tenant     |          40 |              0
(2 rows)

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

-- a unit that changes: new rows in the write buffer, deleted rows, a rewrite of the segments
INSERT INTO uh_sparse SELECT g, g % 7, (g * 31) % 1000 FROM generate_series(240001, 250000) g;
DELETE FROM uh_sparse WHERE id % 3 = 0;
SELECT name, (same(q)).* FROM q WHERE name LIKE 'sparse%' ORDER BY name;
            name            | result_rows | vs_normal_plan 
----------------------------+-------------+----------------
 sparse all units           |           7 |              0
 sparse filter on a measure |           7 |              0
 sparse ids                 |           1 |              0
 sparse range               |           7 |              0
(4 rows)

VACUUM uh_sparse;
SELECT sift.col_merge('uh_sparse_i'::regclass, true) >= 0 AS merged;
 merged 
--------
 t
(1 row)

SELECT name, (same(q)).* FROM q WHERE name LIKE 'sparse%' ORDER BY name;
            name            | result_rows | vs_normal_plan 
----------------------------+-------------+----------------
 sparse all units           |           7 |              0
 sparse filter on a measure |           7 |              0
 sparse ids                 |           1 |              0
 sparse range               |           7 |              0
(4 rows)

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

DROP FUNCTION same(text);
DROP TABLE q, uh_dense, uh_sparse, uh_cut;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;