-- paths = auto for the array columns: a sample of the table says whether the arrays differ (their elements stand in place of the whole-array image) or repeat (the image stays)
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET client_min_messages = notice;
CREATE TABLE aa (id int, uniq text[], rep text[], mid text[], ids int4[]) WITH (autovacuum_enabled = false);
INSERT INTO aa SELECT i, ARRAY['a' || i, 'b' || i % 50, 'c' || i % 7], ARRAY['r' || i % 5, 's' || i % 3], ARRAY['m' || i % 100, 'n' || i % 11], ARRAY[i, i % 100]
FROM generate_series(1, 12000) i;
ANALYZE aa;
CREATE INDEX aa_col ON aa USING sift_col (id, uniq, rep, mid, ids);
NOTICE:  sift_col index "aa_col": 2 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; array_lengths = uniq, rep, mid, ids;
array_elements = uniq, rep, mid, ids
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('aa_col');
SELECT regexp_replace(reloptions::text, '(auto: )[^,}]*', '\1...') AS options FROM pg_class WHERE relname = 'aa_col';
                                  options                                   
----------------------------------------------------------------------------
 {array_lengths=on,"paths=auto: ..., +rep[] text, +mid[] text, ids[] int4"}
(1 row)

SELECT leaf, path, type FROM sift.col_leaves('aa_col') ORDER BY leaf;
 leaf |  path  |       type       
------+--------+------------------
    0 | id     | integer
    1 | uniq   | text[]
    2 | uniq#1 | integer (lane 1)
    3 | uniq#2 | integer (lane 2)
    4 | uniq[] | bigint
    5 | uniq[] | text
    6 | rep    | text[]
    7 | rep#1  | integer (lane 1)
    8 | rep#2  | integer (lane 2)
    9 | rep[]  | bigint
   10 | rep[]  | text
   11 | mid    | text[]
   12 | mid#1  | integer (lane 1)
   13 | mid#2  | integer (lane 2)
   14 | mid[]  | bigint
   15 | mid[]  | text
   16 | ids    | integer[]
   17 | ids#1  | integer (lane 1)
   18 | ids#2  | integer (lane 2)
   19 | ids[]  | bigint
   20 | ids[]  | integer
(21 rows)

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

SET seq_page_cost = 1000;
SET random_page_cost = 1000;
SELECT count(*) FROM aa WHERE uniq @> ARRAY['b7', 'c0'];
 count 
-------
    35
(1 row)

SELECT count(*) FROM aa WHERE rep @> ARRAY['r2'];
 count 
-------
  2400
(1 row)

SELECT count(*) FROM aa WHERE mid @> ARRAY['m7', 'n3'];
 count 
-------
    11
(1 row)

SELECT count(*) FROM aa WHERE ids && ARRAY[5, 99999];
 count 
-------
   120
(1 row)

SELECT rep, count(*) FROM aa GROUP BY 1 ORDER BY 1 LIMIT 3;
   rep   | count 
---------+-------
 {r0,s0} |   800
 {r0,s1} |   800
 {r0,s2} |   800
(3 rows)

-- an array that repeats (rep: 15 different arrays in 12,000 rows, mid: 1,100) gets its elements beside the image too: an element test over the image costs the same
-- instructions a row whatever the number of different arrays, the elements about a fiftieth of that. Element tests run on the elements and give the rows of the normal plan
EXPLAIN (COSTS OFF) SELECT count(*) FROM aa WHERE rep @> ARRAY['r2'];
               QUERY PLAN                
-----------------------------------------
 Custom Scan (SiftAgg)
   Index: aa_col
   Filters: EXISTS rep[] (rep[]. = 'r2')
   Pushed Aggregates: count(*)
(4 rows)

CREATE FUNCTION agree(q text) RETURNS boolean LANGUAGE plpgsql AS $$
DECLARE a text; b text;
BEGIN
  PERFORM set_config('sift.enable_agg', 'on', true);
  EXECUTE 'SELECT string_agg(r::text, ''|'' ORDER BY r::text) FROM (' || q || ') r' INTO a;
  PERFORM set_config('sift.enable_agg', 'off', true);
  EXECUTE 'SELECT string_agg(r::text, ''|'' ORDER BY r::text) FROM (' || q || ') r' INTO b;
  RETURN a IS NOT DISTINCT FROM b;
END $$;
SELECT agree(q) AS same_rows, q FROM unnest(ARRAY[
  $q$SELECT count(*) FROM aa WHERE rep @> ARRAY['r2']$q$,
  $q$SELECT count(*) FROM aa WHERE rep && ARRAY['s1', 'r4']$q$,
  $q$SELECT count(*) FROM aa WHERE rep <@ ARRAY['r1', 's0', 's2', 'r3']$q$,
  $q$SELECT count(*) FROM aa WHERE 'n3' = ANY (mid)$q$,
  $q$SELECT count(*) FROM aa WHERE mid @> ARRAY['m7', 'n3']$q$,
  $q$SELECT count(*) FROM aa WHERE ids && ARRAY[5, 99999]$q$,
  $q$SELECT e, count(*) FROM aa, unnest(rep) e GROUP BY 1$q$,
  $q$SELECT rep, count(*) FROM aa GROUP BY 1$q$
]) AS t(q);
 same_rows |                                 q                                  
-----------+--------------------------------------------------------------------
 t         | SELECT count(*) FROM aa WHERE rep @> ARRAY['r2']
 t         | SELECT count(*) FROM aa WHERE rep && ARRAY['s1', 'r4']
 t         | SELECT count(*) FROM aa WHERE rep <@ ARRAY['r1', 's0', 's2', 'r3']
 t         | SELECT count(*) FROM aa WHERE 'n3' = ANY (mid)
 t         | SELECT count(*) FROM aa WHERE mid @> ARRAY['m7', 'n3']
 t         | SELECT count(*) FROM aa WHERE ids && ARRAY[5, 99999]
 t         | SELECT e, count(*) FROM aa, unnest(rep) e GROUP BY 1
 t         | SELECT rep, count(*) FROM aa GROUP BY 1
(8 rows)

-- a table too small for a sample gets neither
CREATE TABLE ab AS SELECT i AS id, ARRAY['a' || i] AS uniq FROM generate_series(1, 500) i;
CREATE INDEX ab_col ON ab USING sift_col (id, uniq);
NOTICE:  sift_col index "ab_col": 1 automatic choice (array_lengths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; array_lengths = uniq
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ab_col');
SELECT leaf, path FROM sift.col_leaves('ab_col') ORDER BY leaf;
 leaf |  path  
------+--------
    0 | id
    1 | uniq
    2 | uniq#1
    3 | uniq#2
(4 rows)

-- a table loaded and indexed before its first ANALYZE or VACUUM (reltuples -1) is sized from its pages, as the planner does: it gets the same choice as the analyzed one
CREATE TABLE ac (id int, uniq text[], rep text[]) WITH (autovacuum_enabled = false);
INSERT INTO ac SELECT i, ARRAY['a' || i, 'b' || i % 50, 'c' || i % 7], ARRAY['r' || i % 5, 's' || i % 3] FROM generate_series(1, 12000) i;
SELECT reltuples FROM pg_class WHERE relname = 'ac';
 reltuples 
-----------
        -1
(1 row)

CREATE INDEX ac_col ON ac USING sift_col (id, uniq, rep);
NOTICE:  sift_col index "ac_col": 2 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; array_lengths = uniq, rep;
array_elements = uniq, rep
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ac_col');
SELECT regexp_replace(reloptions::text, '(auto: )[^,}]*', '\1...') AS options FROM pg_class WHERE relname = 'ac_col';
                      options                      
---------------------------------------------------
 {array_lengths=on,"paths=auto: ..., +rep[] text"}
(1 row)

SELECT leaf, path, type FROM sift.col_leaves('ac_col') ORDER BY leaf;
 leaf |  path  |       type       
------+--------+------------------
    0 | id     | integer
    1 | uniq   | text[]
    2 | uniq#1 | integer (lane 1)
    3 | uniq#2 | integer (lane 2)
    4 | uniq[] | bigint
    5 | uniq[] | text
    6 | rep    | text[]
    7 | rep#1  | integer (lane 1)
    8 | rep#2  | integer (lane 2)
    9 | rep[]  | bigint
   10 | rep[]  | text
(11 rows)

-- the HINT of the NOTICE: paths = 'auto:' keeps the arrays and leaves the elements out (an empty value is auto, which chooses the elements again)
ALTER INDEX aa_col SET (paths = 'auto:');
REINDEX INDEX aa_col;
NOTICE:  sift_col index "aa_col": 1 automatic choice (array_lengths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; array_lengths = uniq, rep, mid, ids
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('aa_col');
SELECT leaf, path FROM sift.col_leaves('aa_col') ORDER BY leaf;
 leaf |  path  
------+--------
    0 | id
    1 | uniq
    2 | uniq#1
    3 | uniq#2
    4 | rep
    5 | rep#1
    6 | rep#2
    7 | mid
    8 | mid#1
    9 | mid#2
   10 | ids
   11 | ids#1
   12 | ids#2
(13 rows)

ALTER INDEX aa_col SET (paths = '');
REINDEX INDEX aa_col;
NOTICE:  sift_col index "aa_col": 2 automatic choices, 1 advice
DETAIL:  advice: track_changes = the table has no one-column primary key; array_lengths = uniq, rep, mid, ids;
array_elements = uniq, rep, mid, ids
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('aa_col');
SELECT leaf, path FROM sift.col_leaves('aa_col') ORDER BY leaf;
 leaf |  path  
------+--------
    0 | id
    1 | uniq
    2 | uniq#1
    3 | uniq#2
    4 | uniq[]
    5 | uniq[]
    6 | rep
    7 | rep#1
    8 | rep#2
    9 | rep[]
   10 | rep[]
   11 | mid
   12 | mid#1
   13 | mid#2
   14 | mid[]
   15 | mid[]
   16 | ids
   17 | ids#1
   18 | ids#2
   19 | ids[]
   20 | ids[]
(21 rows)

DROP FUNCTION agree(text);
DROP TABLE aa, ab, ac;
DROP EXTENSION pg_sift;