-- Where the default key_cut chooses a time column, the index also gets key_order's layout for it (the order arrays of the column), so that ORDER BY ts LIMIT n over a cut
-- index does not read every unit; key_order = 'off' keeps the cut alone, a key_order or an order_array_columns of the user's is left as it is. A cut by another key has none.
CREATE EXTENSION pg_sift;
SET sift.async_maintenance = off;
SET client_min_messages = warning;
SET lc_messages = 'C';
\set VERBOSITY terse
CREATE TABLE ka (id int, acct_id int, v int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO ka SELECT i, 1 + (hashint4(i) & 2147483647) % 5000, (hashint4(i + 1) & 2147483647) % 1000, repeat('x', 100) FROM generate_series(1, 400000) i;
VACUUM (FREEZE, ANALYZE) ka;
CREATE TABLE kb (id int, t timestamp, v int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO kb SELECT i, timestamp '2020-01-01' + ((hashint4(i) & 2147483647) % 157766400) * interval '1 second', (hashint4(i + 1) & 2147483647) % 1000, repeat('x', 100) FROM generate_series(1, 400000) i;
VACUUM (FREEZE, ANALYZE) kb;
CREATE FUNCTION ka_chk(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE pl text; used bool := false; ra text; rb text;
BEGIN
	SET LOCAL sift.enable_agg = on; SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; SET LOCAL enable_indexonlyscan = off;
	FOR pl IN EXECUTE 'EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off) ' || q LOOP used := used OR pl LIKE '%Global Order%'; END LOOP;
	EXECUTE 'SELECT string_agg(x::text, '','' ORDER BY ord) FROM (SELECT row_number() OVER () AS ord, * FROM (' || q || ') r) x' INTO ra;
	SET LOCAL sift.enable_agg = off;
	EXECUTE 'SELECT string_agg(x::text, '','' ORDER BY ord) FROM (SELECT row_number() OVER () AS ord, * FROM (' || q || ') r) x' INTO rb;
	RETURN CASE WHEN ra IS DISTINCT FROM rb THEN 'DIFFERS ' || q WHEN used THEN 'array' ELSE 'units' END;
END $$;
CREATE INDEX ka_auto ON ka USING sift_col (acct_id, v);
-- a key that is not a time column keeps the cut alone
SELECT reloptions @> ARRAY['key_cut=auto: acct_id'] AS cut, NOT reloptions::text LIKE '%key_order%' AS no_key_order FROM pg_class WHERE relname = 'ka_auto';
 cut | no_key_order 
-----+--------------
 t   | t
(1 row)

-- an equality on the key of a table in arrival order is read from the one unit it is cut into, not from a heap page for each of its rows (the Index Scan was priced as if they were in heap order)
CREATE FUNCTION ka_scan(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE pl text;
BEGIN
	FOR pl IN EXECUTE 'EXPLAIN (COSTS off) ' || q LOOP
		IF pl ~ 'Scan' THEN RETURN substring(pl from '(Index Only Scan|Index Scan|Bitmap Heap Scan|Seq Scan|Custom Scan)'); END IF;
	END LOOP;
END $$;
SELECT ka_scan('SELECT acct_id, v FROM ka WHERE acct_id = 7') AS key_lookup, ka_scan('SELECT acct_id, v FROM ka WHERE acct_id IN (7, 8, 9)') AS key_list;
   key_lookup    |    key_list     
-----------------+-----------------
 Index Only Scan | Index Only Scan
(1 row)

DROP INDEX ka_auto;
CREATE INDEX kb_auto ON kb USING sift_col (t, v);
SELECT reloptions @> ARRAY['key_cut=auto: t', 'key_order=auto: t'] AS cut_and_key_order FROM pg_class WHERE relname = 'kb_auto';
 cut_and_key_order 
-------------------
 t
(1 row)

SELECT ka_chk('SELECT t, v FROM kb ORDER BY t, id LIMIT 20') AS top20,
       ka_chk('SELECT t, v FROM kb ORDER BY t, id LIMIT 20 OFFSET 3000') AS offset3000,
       ka_chk('SELECT t, v FROM kb WHERE t BETWEEN ''2021-01-01'' AND ''2021-01-04'' ORDER BY t, id LIMIT 50') AS range_limit;
 top20 | offset3000 | range_limit 
-------+------------+-------------
 array | array      | array
(1 row)

-- REINDEX decides again, and gets the same
REINDEX INDEX kb_auto;
SELECT reloptions @> ARRAY['key_cut=auto: t', 'key_order=auto: t'] AS cut_and_key_order FROM pg_class WHERE relname = 'kb_auto';
 cut_and_key_order 
-------------------
 t
(1 row)

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

DROP INDEX kb_auto;
-- off keeps the cut alone
CREATE INDEX kb_off ON kb USING sift_col (t, v) WITH (key_order = 'off');
SELECT reloptions @> ARRAY['key_cut=auto: t', 'key_order=off'] AS cut_kept, NOT reloptions::text LIKE '%key_order=auto%' AS no_arrays FROM pg_class WHERE relname = 'kb_off';
 cut_kept | no_arrays 
----------+-----------
 t        | t
(1 row)

SELECT ka_chk('SELECT t, v FROM kb ORDER BY t, id LIMIT 20') AS top20_without;
 top20_without 
---------------
 units
(1 row)

DROP INDEX kb_off;
-- a key_cut of the user's ('off') takes the chosen key_order with it at the next REINDEX
CREATE INDEX kb_cutoff ON kb USING sift_col (t, v);
ALTER INDEX kb_cutoff SET (key_cut = 'off');
REINDEX INDEX kb_cutoff;
SELECT reloptions @> ARRAY['key_cut=off'] AS cut_off, NOT reloptions::text LIKE '%key_order%' AS key_order_gone FROM pg_class WHERE relname = 'kb_cutoff';
 cut_off | key_order_gone 
---------+----------------
 t       | t
(1 row)

DROP INDEX kb_cutoff;
-- a key_order of the user's wins: the rule leaves the cut to it
CREATE INDEX kb_user ON kb USING sift_col (t, v) WITH (key_order = 'v');
SELECT reloptions @> ARRAY['key_order=v'] AS user_wins, NOT reloptions::text LIKE '%key_cut%' AS no_auto_cut FROM pg_class WHERE relname = 'kb_user';
 user_wins | no_auto_cut 
-----------+-------------
 t         | t
(1 row)

DROP INDEX kb_user;
-- an order_array_columns of the user's (even 'off') is a layout of its own
CREATE INDEX kb_arr ON kb USING sift_col (t, v) WITH (order_array_columns = 'off');
SELECT NOT reloptions::text LIKE '%key_order%' AS no_key_order FROM pg_class WHERE relname = 'kb_arr';
 no_key_order 
--------------
 t
(1 row)

DROP INDEX kb_arr;
-- an entity and a date: the latest rows of one entity read entries of its group, the heap in the order of the dates
CREATE TABLE kc (id int, tenant_id int, event_date date, amount int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO kc SELECT i, 1 + least(999, floor(1000 * power((hashint4(i) & 2147483647) / 2147483647.0, 1.5)))::int, date '2022-01-01' + (i * 730 / 400000 + (hashint4(i + 7) & 2147483647) % 3), (hashint4(i + 13) & 2147483647) % 1000, repeat('x', 100) FROM generate_series(1, 400000) i;
VACUUM (FREEZE, ANALYZE) kc;
CREATE INDEX kc_auto ON kc USING sift_col (tenant_id, event_date, amount);
SELECT reloptions @> ARRAY['key_order=auto: tenant_id, event_date'] AS entity_and_date FROM pg_class WHERE relname = 'kc_auto';
 entity_and_date 
-----------------
 t
(1 row)

SELECT ka_chk('SELECT event_date FROM kc WHERE tenant_id = 7 ORDER BY event_date DESC, id LIMIT 20') AS latest20,
       ka_chk('SELECT event_date FROM kc WHERE tenant_id = 7 AND event_date < ''2023-01-01'' ORDER BY event_date DESC, id LIMIT 20') AS page20;
 latest20 | page20 
----------+--------
 array    | array
(1 row)

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

DROP INDEX kc_auto;
CREATE INDEX kc_off ON kc USING sift_col (tenant_id, event_date, amount) WITH (key_order = 'off');
SELECT NOT reloptions::text LIKE '%key_order=auto%' AS off_keeps_plain_arrays FROM pg_class WHERE relname = 'kc_off';
 off_keeps_plain_arrays 
------------------------
 t
(1 row)

DROP INDEX kc_off;
DROP TABLE kc;
-- 200 entities in a heap that follows the date: the plain choice builds no second order there, the layout would add one (+37% of the index): the plain arrays stay
CREATE TABLE kd (id int, tenant_id int, event_date date, amount int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO kd SELECT i, 1 + least(199, floor(200 * power((hashint4(i) & 2147483647) / 2147483647.0, 1.5)))::int, date '2022-01-01' + (i * 730 / 400000 + (hashint4(i + 7) & 2147483647) % 3), (hashint4(i + 13) & 2147483647) % 1000, repeat('x', 100) FROM generate_series(1, 400000) i;
VACUUM (FREEZE, ANALYZE) kd;
CREATE INDEX kd_auto ON kd USING sift_col (tenant_id, event_date, amount);
SELECT NOT reloptions::text LIKE '%key_order%' AS plain_choice_stays FROM pg_class WHERE relname = 'kd_auto';
 plain_choice_stays 
--------------------
 t
(1 row)

DROP TABLE kd;
-- 40 entities in a random order under a cut by the date: the layout would replace the group by another column and gain nothing beyond the noise (B - C of -2 to +7 in four builds), the plain choice stays
CREATE TABLE kf (id int, tenant_id int, event_date date, amount int, status int2, pad text) WITH (autovacuum_enabled = false);
INSERT INTO kf SELECT i, 1 + least(39, floor(40 * power((hashint4(i) & 2147483647) / 2147483647.0, 1.5)))::int, date '2022-01-01' + ((hashint4(i + 7) & 2147483647) % 730), (hashint4(i + 13) & 2147483647) % 1000, 1 + (hashint4(i + 11) & 2147483647) % 5, md5(i::text) FROM generate_series(1, 300000) i;
VACUUM (FREEZE, ANALYZE) kf;
CREATE INDEX kf_auto ON kf USING sift_col (tenant_id, event_date, amount, status);
SELECT reloptions @> ARRAY['key_cut=auto: event_date'] AS cut_by_date, NOT reloptions::text LIKE '%key_order%' AS plain_choice_stays FROM pg_class WHERE relname = 'kf_auto';
 cut_by_date | plain_choice_stays 
-------------+--------------------
 t           | t
(1 row)

DROP TABLE kf;
-- a date cut with 730 values, no entity: runs of equal keys, a top-N with a filter reads the units the cut keeps (12 blocks), not the order of the key (441 heap fetches): the plain choice stays
CREATE TABLE ke (id int, event_date date, status int2, amount int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO ke SELECT i, date '2022-01-01' + ((hashint4(i + 7) & 2147483647) % 730), (hashint4(i + 11) & 2147483647) % 5, (hashint4(i + 13) & 2147483647) % 1000, md5(i::text) FROM generate_series(1, 400000) i;
VACUUM (FREEZE, ANALYZE) ke;
CREATE INDEX ke_auto ON ke USING sift_col (event_date, status, amount);
SELECT reloptions @> ARRAY['key_cut=auto: event_date'] AS cut_by_date, NOT reloptions::text LIKE '%key_order%' AS plain_choice_stays FROM pg_class WHERE relname = 'ke_auto';
 cut_by_date | plain_choice_stays 
-------------+--------------------
 t           | t
(1 row)

DROP TABLE ke;
DROP FUNCTION ka_chk(text), ka_scan(text);
DROP TABLE ka, kb;
DROP EXTENSION pg_sift;