-- A unique key that follows the heap order (a serial id) gets the build's order array, not the point directory: the array is runs of consecutive heap addresses, so it answers the
-- equality in a tenth of the bytes of the directory, for about the same instructions and fewer cold blocks. A key scattered over the heap keeps the directory (its array is as large and reads 2.5 times the instructions),
-- and an index with no array of the build's ('off', a table under 100,000 rows) keeps the directory for the ordered key too. An array the user lists is the user's: whole, and the directory is not added to it.
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
SET sift.test_col_legacy_options = on;
SET sift.async_maintenance = off;
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers_per_gather = 0;
\set VERBOSITY terse
CREATE TABLE pk (id int, uid int, ts timestamp, pad text);
INSERT INTO pk SELECT i, (i::bigint * 7919 % 150011)::int, timestamp '2024-01-01' + abs(hashint4(i + 7)) % 5000000 * interval '1 second', md5(i::text)
FROM generate_series(1, 150000) i;
VACUUM (FREEZE, ANALYZE) pk;
CREATE FUNCTION opt(regclass, text) RETURNS text LANGUAGE sql AS $$ SELECT coalesce((SELECT split_part(o, '=', 2) FROM pg_class c, unnest(c.reloptions) o WHERE c.oid = $1 AND o LIKE $2 || '=%'), '-') $$;
CREATE INDEX pk_auto ON pk USING sift_col (id, ts) WITH (key_cut = 'off', key_order = 'off');
NOTICE:  sift_col index "pk_auto": 2 automatic choices, 1 advice
NOTICE:  sift_col index "pk_auto" is column format minor 44, which a library before 4.3 cannot read
-- the same index with the directory of the ordered key and no array of it: what the build chose before
CREATE INDEX pk_dir ON pk USING sift_col (id, ts) WITH (key_cut = 'off', key_order = 'off', point_tid_columns = 'id', order_columns = 'ts', order_array_columns = 'ts');
NOTICE:  sift_col index "pk_dir": 3 automatic choices, 1 advice
NOTICE:  sift_col index "pk_dir" is column format minor 44, which a library before 4.3 cannot read
SELECT i AS idx, opt(i::regclass, 'order_array_columns') AS order_arrays, opt(i::regclass, 'point_tid_columns') AS directory
FROM unnest(ARRAY['pk_auto', 'pk_dir']) i ORDER BY 1;
   idx   | order_arrays | directory 
---------+--------------+-----------
 pk_auto | auto: id, ts | -
 pk_dir  | ts           | id
(2 rows)

SELECT pg_relation_size('pk_auto') < pg_relation_size('pk_dir') AS smaller,
	round(pg_relation_size('pk_dir')::numeric / pg_relation_size('pk_auto'), 1) AS dir_over_auto;
 smaller | dir_over_auto 
---------+---------------
 t       |           1.7
(1 row)

-- the lookup of the key reads no unit and no row through the array, as through the directory
SELECT i AS idx, c.units, c.rows_scanned FROM unnest(ARRAY['pk_auto', 'pk_dir']) i, LATERAL sift._test_col_counters(i::regclass, '0:eq:77777', 24) c ORDER BY 1;
   idx   | units | rows_scanned 
---------+-------+--------------
 pk_auto |     0 |            0
 pk_dir  |     0 |            0
(2 rows)

DROP INDEX pk_dir;
SELECT sift.col_verify('pk_auto');
 col_verify 
------------
 
(1 row)

SET enable_seqscan = off; SET enable_bitmapscan = off;
EXPLAIN (COSTS OFF) SELECT id, ts FROM pk WHERE id = 77777;
           QUERY PLAN           
--------------------------------
 Index Scan using pk_auto on pk
   Index Cond: (id = 77777)
(2 rows)

SELECT count(*), min(id), max(ts) FROM pk WHERE id = 77777;
 count |  min  |           max            
-------+-------+--------------------------
     1 | 77777 | Wed Feb 14 04:13:49 2024
(1 row)

SELECT count(*), min(id) FROM pk WHERE id IN (5, 77777, 149999, 150001);
 count | min 
-------+-----
     3 |   5
(1 row)

SET enable_seqscan = on; SET enable_bitmapscan = on;
-- rows written after the build, a promotion and a merge keep the answers
INSERT INTO pk SELECT 150000 + i, i, timestamp '2025-01-01' + i * interval '1 second', 'w' FROM generate_series(1, 3000) i;
DELETE FROM pk WHERE id % 211 = 0;
SELECT sift.col_promote('pk_auto'::regclass) >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

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

SELECT sift.col_verify('pk_auto');
 col_verify 
------------
 
(1 row)

SET enable_seqscan = off; SET enable_bitmapscan = off;
SELECT count(*), min(id) FROM pk WHERE id IN (5, 77777, 150211, 151000, 152999);
 count | min 
-------+-----
     5 |   5
(1 row)

SET enable_seqscan = on; SET enable_bitmapscan = on;
-- a rebuild decides again from the data it finds
REINDEX INDEX pk_auto;
NOTICE:  sift_col index "pk_auto": 2 automatic choices, 1 advice
NOTICE:  sift_col index "pk_auto" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('pk_auto', 'order_array_columns') AS order_arrays, opt('pk_auto', 'point_tid_columns') AS directory;
 order_arrays | directory 
--------------+-----------
 auto: id, ts | -
(1 row)

DROP INDEX pk_auto;
-- a key scattered over the heap keeps the directory (and its own array, as before); no array of the build's, or a table under 100,000 rows: the ordered key keeps the directory; a listed array replaces it
CREATE INDEX pk_uid ON pk USING sift_col (uid, ts) WITH (key_cut = 'off', key_order = 'off');
NOTICE:  sift_col index "pk_uid": 3 automatic choices, 1 advice
NOTICE:  sift_col index "pk_uid" is column format minor 44, which a library before 4.3 cannot read
CREATE INDEX pk_off ON pk USING sift_col (id, ts) WITH (key_cut = 'off', key_order = 'off', order_array_columns = 'off');
NOTICE:  sift_col index "pk_off": 1 automatic choice (point_tid_columns), 1 advice (track_changes)
CREATE INDEX pk_list ON pk USING sift_col (id, ts) WITH (key_cut = 'off', key_order = 'off', order_columns = 'id', order_array_columns = 'id');
NOTICE:  sift_col index "pk_list": 1 automatic choice (order_array_columns), 1 advice (track_changes)
NOTICE:  sift_col index "pk_list" is column format minor 44, which a library before 4.3 cannot read
CREATE TABLE pks AS SELECT id, uid, ts, pad FROM pk WHERE id <= 90000;
VACUUM (FREEZE, ANALYZE) pks;
CREATE INDEX pks_i ON pks USING sift_col (id, ts) WITH (key_cut = 'off', key_order = 'off');
NOTICE:  sift_col index "pks_i": 1 automatic choice (point_tid_columns), 1 advice (track_changes)
SELECT i AS idx, opt(i::regclass, 'order_array_columns') AS order_arrays, opt(i::regclass, 'point_tid_columns') AS directory
FROM unnest(ARRAY['pk_uid', 'pk_off', 'pk_list', 'pks_i']) i ORDER BY 1;
   idx   | order_arrays  | directory 
---------+---------------+-----------
 pk_list | id            | -
 pk_off  | off           | auto: id
 pk_uid  | auto: uid, ts | auto: uid
 pks_i   | -             | auto: id
(4 rows)

DROP FUNCTION opt(regclass, text);
DROP TABLE pk, pks;
DROP EXTENSION pg_sift_test, pg_sift CASCADE;