-- order_array_columns is chosen by the build when it is unset or 'auto': a column of 100,000 rows or more with 1,000 or more distinct values whose values are
-- scattered over the heap (correlation under 0.99) gets the global order, so that a range of it reads the entries between its bounds; a column in heap order, with few values, on
-- a smaller table or a narrow index gets none; a table without statistics is analyzed first. A date or timestamp comes before the other columns, as a range or an
-- ORDER BY .. LIMIT is what it is read by. 'off' and a list are the user's.
-- The pad column has strings over 64 bytes: a text column of shorter strings is chosen too (col_gorder_text), and the array holds the strings again, so these tests keep it out.
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
SET sift.test_col_legacy_options = on;
SET sift.async_maintenance = off;
-- plain chunks: the column format minors below tell the layouts apart (col_gorder_seek covers the seekable ones)
SET sift.test_limits = 'gord_seek_pct=0';
SET max_parallel_maintenance_workers = 0;
CREATE TABLE ga (id int, r int, g int, ts timestamp, pad text);
ALTER TABLE ga ALTER COLUMN pad SET STORAGE PLAIN;
INSERT INTO ga SELECT i, abs(hashint4(i)) % 5000000, i % 50, timestamp '2024-01-01' + i * interval '1 second', md5(i::text) || md5(i::text || 'x') FROM generate_series(1, 120000) i;
-- no statistics yet: the build analyzes the table and chooses
CREATE INDEX ga_nostats ON ga USING sift_col (id, r, g, ts, pad);
NOTICE:  sift_col index "ga_nostats": 3 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; point_tid_columns = r;
order_array_columns = id, r; order_layout = id exact, r compact in 1% of chunks
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ga_nostats');
NOTICE:  sift_col index "ga_nostats" is column format minor 41, which a library before 4.0 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
SELECT reloptions FROM pg_class WHERE relname = 'ga_nostats';
                           reloptions                            
-----------------------------------------------------------------
 {"order_array_columns=auto: id, r","point_tid_columns=auto: r"}
(1 row)

DROP INDEX ga_nostats;
ANALYZE ga;
-- r is scattered, g has 50 values, id and ts follow the heap
CREATE INDEX ga_auto ON ga USING sift_col (id, r, g, ts, pad);
NOTICE:  sift_col index "ga_auto": 3 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; point_tid_columns = r;
order_array_columns = id, r; order_layout = id exact, r compact in 1% of chunks
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ga_auto');
NOTICE:  sift_col index "ga_auto" is column format minor 41, which a library before 4.0 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
SELECT reloptions FROM pg_class WHERE relname = 'ga_auto';
                           reloptions                            
-----------------------------------------------------------------
 {"order_array_columns=auto: id, r","point_tid_columns=auto: r"}
(1 row)

SELECT min(format) AS format FROM sift.col_segments('ga_auto') WHERE kind = 'immutable';
 format 
--------
     41
(1 row)

-- a range of r reads the addresses and no unit
SELECT p.units AS units_with_addresses, p.rows = 0 AS no_row_read, f.units > p.units AS fewer_units
FROM sift._test_col_counters('ga_auto', '1:ge:2000000;1:le:2004000', 24) p, sift._test_col_counters('ga_auto', '1:ge:2000000;1:le:2004000', 0) f;
 units_with_addresses | no_row_read | fewer_units 
----------------------+-------------+-------------
                    0 | t           | t
(1 row)

-- off and a list are the user's
CREATE INDEX ga_off ON ga USING sift_col (id, r, g, ts, pad) WITH (order_array_columns = off);
NOTICE:  sift_col index "ga_off": 1 automatic choice (point_tid_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; point_tid_columns = id, r
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ga_off');
CREATE INDEX ga_list ON ga USING sift_col (id, r, g, ts, pad) WITH (order_columns = 'g', order_array_columns = 'g');
NOTICE:  sift_col index "ga_list": 2 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; point_tid_columns = id, r;
order_array_columns = g
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ga_list');
NOTICE:  sift_col index "ga_list" is column format minor 38, which a library before 3.7 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
SELECT relname, reloptions FROM pg_class WHERE relname IN ('ga_off', 'ga_list') ORDER BY 1;
 relname |                               reloptions                                
---------+-------------------------------------------------------------------------
 ga_list | {order_columns=g,order_array_columns=g,"point_tid_columns=auto: id, r"}
 ga_off  | {order_array_columns=off,"point_tid_columns=auto: id, r"}
(2 rows)

-- the whole order of a listed column is minor 38, which 3.7 reads and 3.6 does not (the NOTICE of a listed column says so)
SELECT min(format) AS format FROM sift.col_segments('ga_list') WHERE kind = 'immutable';
 format 
--------
     38
(1 row)

-- the update list is for an option that names columns: off is not a gain to reindex for
SELECT count(*) AS commands FROM sift.upgrade_commands() WHERE object = 'ga_off'::regclass;
 commands 
----------
        0
(1 row)

DROP INDEX ga_auto, ga_off, ga_list;
-- a narrow index of small columns: the arrays would be a third of it
CREATE INDEX ga_narrow ON ga USING sift_col (id, r, g) WITH (point_tid_columns = 'off');
NOTICE:  sift_col index "ga_narrow": 2 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; order_array_columns = id, r;
order_layout = id exact, r compact in 1% of chunks
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ga_narrow');
NOTICE:  sift_col index "ga_narrow" is column format minor 41, which a library before 4.0 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
SELECT reloptions FROM pg_class WHERE relname = 'ga_narrow';
                        reloptions                         
-----------------------------------------------------------
 {point_tid_columns=off,"order_array_columns=auto: id, r"}
(1 row)

DROP INDEX ga_narrow;
-- a table under 100,000 rows
CREATE TABLE gs AS SELECT id, r, g, ts, pad FROM ga WHERE id <= 90000;
ANALYZE gs;
CREATE INDEX gs_i ON gs USING sift_col (id, r, g, ts, pad);
NOTICE:  sift_col index "gs_i": 1 automatic choice (point_tid_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; point_tid_columns = id, r
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('gs_i');
SELECT reloptions FROM pg_class WHERE relname = 'gs_i';
            reloptions             
-----------------------------------
 {"point_tid_columns=auto: id, r"}
(1 row)

-- the choice is made again by REINDEX: after the statistics change the column is dropped
CREATE INDEX ga_re ON ga USING sift_col (id, r, g, ts, pad);
NOTICE:  sift_col index "ga_re": 3 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; point_tid_columns = r;
order_array_columns = id, r; order_layout = id exact, r compact in 1% of chunks
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ga_re');
NOTICE:  sift_col index "ga_re" is column format minor 41, which a library before 4.0 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
SELECT reloptions FROM pg_class WHERE relname = 'ga_re';
                           reloptions                            
-----------------------------------------------------------------
 {"order_array_columns=auto: id, r","point_tid_columns=auto: r"}
(1 row)

UPDATE ga SET r = id;
ANALYZE ga;
REINDEX INDEX ga_re;
NOTICE:  sift_col index "ga_re": 1 automatic choice (order_array_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; order_array_columns = id, r
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ga_re');
NOTICE:  sift_col index "ga_re" is column format minor 38, which a library before 3.7 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
SELECT reloptions FROM pg_class WHERE relname = 'ga_re';
                         reloptions                         
------------------------------------------------------------
 {"order_array_columns=auto: id, r",point_tid_columns=auto}
(1 row)

-- a table never analyzed, a timestamp at random in the heap and another column of more values: both are chosen, the timestamp first
CREATE TABLE gt (id int, v int, ts timestamptz, pad text);
ALTER TABLE gt ALTER COLUMN pad SET STORAGE PLAIN;
SELECT setseed(0.5);
 setseed 
---------
 
(1 row)

INSERT INTO gt SELECT i, (random() * 100000000)::int, timestamptz '2024-01-01' + random() * interval '700 days', md5(i::text) || md5(i::text || 'x') FROM generate_series(1, 120000) i;
CREATE INDEX gt_i ON gt USING sift_col (id, v, ts, pad);
NOTICE:  sift_col index "gt_i": 3 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; point_tid_columns = v;
order_array_columns = id, v, ts; order_layout = id exact, and 2 more
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('gt_i');
NOTICE:  sift_col index "gt_i" is column format minor 41, which a library before 4.0 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
SELECT reloptions FROM pg_class WHERE relname = 'gt_i';
                             reloptions                              
---------------------------------------------------------------------
 {"order_array_columns=auto: id, v, ts","point_tid_columns=auto: v"}
(1 row)

-- a REINDEX cannot analyze (ANALYZE opens the index it rebuilds): a table without statistics chooses nothing
DELETE FROM pg_statistic WHERE starelid = 'gt'::regclass;
ALTER INDEX gt_i RESET (order_array_columns);
REINDEX INDEX gt_i;
NOTICE:  sift_col index "gt_i": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('gt_i');
REINDEX TABLE gt;
NOTICE:  sift_col index "gt_i": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('gt_i');
SELECT reloptions FROM pg_class WHERE relname = 'gt_i';
        reloptions        
--------------------------
 {point_tid_columns=auto}
(1 row)

DROP TABLE ga, gs, gt;
DROP EXTENSION pg_sift_test, pg_sift CASCADE;