-- The second order of the arrays (order_array_group 'auto') is built for a column the table is read by: an entity the rows belong to (a foreign key or a column named *_id) or the column of a
-- b-tree that runs the shape (its first two key columns are the column and a date or timestamp of the index). An integer of 100 or more values with neither (a measure, a code) is a guess at
-- the column a query filters by, and the second order is 3.5 bytes a row of it (a third of the index at 5,000,000 rows) for a shape the table may never ask: it is the user's to name
-- (order_array_group = 'auto: col', which the weighted formula still weighs).
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
CREATE EXTENSION pg_sift_helpers;
SET sift.async_maintenance = off;
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers_per_gather = 0;
\set VERBOSITY terse
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 || '=%'), '-') $$;
-- a measure of 100 values and a column of 50 are no entity, nor is the table's own id; the timestamp is scattered, so it has an array
CREATE TABLE gm (id int, g int, ts timestamp, amount int, pad text);
ALTER TABLE gm ALTER COLUMN pad SET STORAGE PLAIN;
INSERT INTO gm SELECT i, i % 50, timestamp '2024-01-01' + abs(hashint4(i + 7)) % 5000000 * interval '1 second', i % 100, md5(i::text) || md5(i::text || 'x') || md5(i::text || 'y')
FROM generate_series(1, 150000) i;
VACUUM (FREEZE, ANALYZE) gm;
CREATE FUNCTION gm_shapes() RETURNS TABLE (top text, in_list text, measure text) LANGUAGE sql AS $$
	SELECT sift_helpers.topn_check_ordered_rows('SELECT id, ts FROM gm ORDER BY ts DESC, id LIMIT 20'),
		   sift_helpers.topn_check_ordered_rows('SELECT id, ts FROM gm WHERE g IN (3, 5) ORDER BY ts DESC, id LIMIT 20'),
		   sift_helpers.topn_check_ordered_rows('SELECT id, ts FROM gm WHERE amount = 7 ORDER BY ts DESC, id LIMIT 20') $$;
-- by default: no second order, and WHERE measure = x ORDER BY ts LIMIT n reads the units
CREATE INDEX gm_i ON gm USING sift_col (id, g, ts, amount, pad) WITH (key_cut = 'off', key_order = 'off');
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('gm_i', 'order_array_columns') AS order_arrays, opt('gm_i', 'order_array_group') AS second_order;
 order_arrays | second_order 
--------------+--------------
 auto: id, ts | -
(1 row)

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

SELECT * FROM gm_shapes();
     top      |   in_list    | measure 
--------------+--------------+---------
 global order | global order | units
(1 row)

-- the hint: order_array_group = 'auto: amount' keeps the column a candidate (the weighted formula still decides), and the second order is beside the plain one
ALTER INDEX gm_i SET (order_array_group = 'auto: amount');
REINDEX INDEX gm_i;
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('gm_i', 'order_array_columns') AS order_arrays, opt('gm_i', 'order_array_group') AS second_order;
 order_arrays |   second_order   
--------------+------------------
 auto: id, ts | auto: amount; ts
(1 row)

SELECT * FROM gm_shapes();
     top      |   in_list    |   measure    
--------------+--------------+--------------
 global order | global order | global order
(1 row)

-- what the build wrote is the hint of the next REINDEX (an index of an older release keeps its second order)
REINDEX INDEX gm_i;
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('gm_i', 'order_array_group') AS second_order_after_reindex;
 second_order_after_reindex 
----------------------------
 auto: amount; ts
(1 row)

ALTER INDEX gm_i RESET (order_array_group);
REINDEX INDEX gm_i;
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('gm_i', 'order_array_group') AS second_order_reset;
 second_order_reset 
--------------------
 -
(1 row)

DROP INDEX gm_i;
-- a column named outright groups every array and keeps no plain order: the top-N of the others reads the units (the measure is read from the second order, which the label does not tell)
CREATE INDEX gm_i ON gm USING sift_col (id, g, ts, amount, pad) WITH (key_cut = 'off', key_order = 'off', order_array_group = 'amount');
NOTICE:  sift_col index "gm_i": 2 automatic choices, 1 advice
SELECT opt('gm_i', 'order_array_columns') AS order_arrays, opt('gm_i', 'order_array_group') AS second_order;
 order_arrays | second_order 
--------------+--------------
 -            | amount
(1 row)

SELECT * FROM gm_shapes();
  top  | in_list | measure 
-------+---------+---------
 units | units   | units
(1 row)

DROP INDEX gm_i;
DROP FUNCTION gm_shapes();
-- an entity of the same 100 values (a column named *_id) is grouped, with the same rows
CREATE TABLE ge (id int, g int, ts timestamp, set_id int, pad text);
ALTER TABLE ge ALTER COLUMN pad SET STORAGE PLAIN;
INSERT INTO ge SELECT id, g, ts, amount, pad FROM gm;
VACUUM (FREEZE, ANALYZE) ge;
CREATE INDEX ge_auto ON ge USING sift_col (id, g, ts, set_id, pad) WITH (key_cut = 'off', key_order = 'off');
NOTICE:  sift_col index "ge_auto": 3 automatic choices, 1 advice
NOTICE:  sift_col index "ge_auto" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('ge_auto', 'order_array_columns') AS order_arrays, opt('ge_auto', 'order_array_group') AS second_order;
 order_arrays |   second_order   
--------------+------------------
 auto: id, ts | auto: set_id; ts
(1 row)

SELECT sift_helpers.topn_check_ordered_rows('SELECT id, ts FROM ge WHERE set_id = 7 ORDER BY ts DESC, id LIMIT 20') AS entity;
    entity    
--------------
 global order
(1 row)

SELECT regexp_replace(value, '^.*\(second order by ', '(second order by ') AS chosen_by FROM sift.index_choices('ge_auto') WHERE choice = 'order_array_columns';
            chosen_by             
----------------------------------
 (second order by set_id: entity)
(1 row)

DROP INDEX ge_auto;
-- a foreign key is an entity whatever its name
CREATE TABLE key_sets (k int PRIMARY KEY);
INSERT INTO key_sets SELECT g FROM generate_series(0, 99) g;
CREATE TABLE gf (id int, g int, ts timestamp, owner int REFERENCES key_sets (k), pad text);
ALTER TABLE gf ALTER COLUMN pad SET STORAGE PLAIN;
INSERT INTO gf SELECT id, g, ts, amount, pad FROM gm;
VACUUM (FREEZE, ANALYZE) gf;
CREATE INDEX gf_auto ON gf USING sift_col (id, g, ts, owner, pad) WITH (key_cut = 'off', key_order = 'off');
NOTICE:  sift_col index "gf_auto": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gf_auto" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('gf_auto', 'order_array_columns') AS order_arrays, opt('gf_auto', 'order_array_group') AS second_order;
 order_arrays |  second_order   
--------------+-----------------
 auto: id, ts | auto: owner; ts
(1 row)

DROP INDEX gf_auto;
-- a b-tree that runs the shape is the workload: non-unique, its first two key columns are the measure and the timestamp, in either order (a partial one too)
CREATE FUNCTION second_order_with(stmt text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r text;
BEGIN
	IF stmt <> '' THEN EXECUTE stmt; END IF;
	EXECUTE 'CREATE INDEX gm_i ON gm USING sift_col (id, g, ts, amount, pad) WITH (key_cut = ''off'', key_order = ''off'')';
	r := opt('gm_i', 'order_array_group');
	DROP INDEX gm_i;
	DROP INDEX IF EXISTS gm_bt;
	RETURN r;
END $$;
SELECT stmt, second_order_with(stmt) AS second_order FROM unnest(ARRAY[
	'',
	'CREATE INDEX gm_bt ON gm (amount, ts)',
	'CREATE INDEX gm_bt ON gm (ts DESC, amount)',
	'CREATE INDEX gm_bt ON gm (amount, ts) WHERE g < 25',
	'CREATE INDEX gm_bt ON gm (amount)',
	'CREATE INDEX gm_bt ON gm (amount) INCLUDE (ts)',
	'CREATE INDEX gm_bt ON gm (amount, id)',
	'CREATE INDEX gm_bt ON gm (ts, id, amount)',
	'CREATE INDEX gm_bt ON gm (g, ts)',
	'CREATE INDEX gm_bt ON gm (lower(pad), ts)',
	'CREATE UNIQUE INDEX gm_bt ON gm (amount, ts, id)'
]) AS stmt;
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  index "gm_bt" does not exist, skipping
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
                        stmt                        |   second_order   
----------------------------------------------------+------------------
                                                    | -
 CREATE INDEX gm_bt ON gm (amount, ts)              | auto: amount; ts
 CREATE INDEX gm_bt ON gm (ts DESC, amount)         | auto: amount; ts
 CREATE INDEX gm_bt ON gm (amount, ts) WHERE g < 25 | auto: amount; ts
 CREATE INDEX gm_bt ON gm (amount)                  | -
 CREATE INDEX gm_bt ON gm (amount) INCLUDE (ts)     | -
 CREATE INDEX gm_bt ON gm (amount, id)              | -
 CREATE INDEX gm_bt ON gm (ts, id, amount)          | -
 CREATE INDEX gm_bt ON gm (g, ts)                   | -
 CREATE INDEX gm_bt ON gm (lower(pad), ts)          | -
 CREATE UNIQUE INDEX gm_bt ON gm (amount, ts, id)   | -
(11 rows)

-- the group is the b-tree's, the plan reads n entries of it, and the choices say which b-tree chose it; the build's own hint keeps it when the b-tree is dropped for the sift index
CREATE INDEX gm_bt ON gm (amount, ts);
CREATE INDEX gm_i ON gm USING sift_col (id, g, ts, amount, pad) WITH (key_cut = 'off', key_order = 'off');
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('gm_i', 'order_array_group') AS second_order;
   second_order   
------------------
 auto: amount; ts
(1 row)

SELECT regexp_replace(value, '^.*\(second order by ', '(second order by ') AS chosen_by FROM sift.index_choices('gm_i') WHERE choice = 'order_array_columns';
               chosen_by                
----------------------------------------
 (second order by amount: b-tree gm_bt)
(1 row)

CREATE FUNCTION gm_shapes() RETURNS TABLE (measure text) LANGUAGE sql AS $$
	SELECT sift_helpers.topn_check_ordered_rows('SELECT id, ts FROM gm WHERE amount = 7 ORDER BY ts DESC, id LIMIT 20') $$;
SELECT * FROM gm_shapes();
   measure    
--------------
 global order
(1 row)

DROP INDEX gm_bt;
REINDEX INDEX gm_i;
NOTICE:  sift_col index "gm_i": 3 automatic choices, 1 advice
NOTICE:  sift_col index "gm_i" is column format minor 44, which a library before 4.3 cannot read
SELECT opt('gm_i', 'order_array_group') AS second_order_after_the_btree_is_gone;
 second_order_after_the_btree_is_gone 
--------------------------------------
 auto: amount; ts
(1 row)

SELECT regexp_replace(value, '^.*\(second order by ', '(second order by ') AS chosen_by FROM sift.index_choices('gm_i') WHERE choice = 'order_array_columns';
           chosen_by            
--------------------------------
 (second order by amount: hint)
(1 row)

DROP INDEX gm_i;
DROP FUNCTION gm_shapes();
DROP FUNCTION second_order_with(text);
DROP FUNCTION opt(regclass, text);
DROP TABLE gm, ge, gf, key_sets;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift_test, pg_sift CASCADE;