-- The statistics the sift_col source reports to the planner without reading a chunk: distinct values
-- from the dictionary of a text leaf and the range of an integer leaf, the range, and the elements per row of a group.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_test;
SET sift.async_maintenance = off;
CREATE TABLE s (id int, k int, flag bool, t text, f float8, items jsonb);
INSERT INTO s SELECT i, i % 7, i % 2 = 0, 'v' || (i % 5), i / 3.0,
	jsonb_build_array(jsonb_build_object('c', 'x' || (i % 3), 'q', i % 4), jsonb_build_object('c', 'y', 'q', 10 + i % 2))
FROM generate_series(1, 5000) i;
UPDATE s SET t = NULL WHERE id % 10 = 0;
CREATE INDEX s_col ON s USING sift_col (id, k, flag, t, f, items) WITH (paths = 'items[]{c text, q int4}');
NOTICE:  sift_col index "s_col": 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('s_col');
-- leaf, path, distinct values, range, elements per row
SELECT l.leaf, l.path, round(st.ndistinct) AS ndistinct, st.min, st.max, st.elems_per_row
FROM sift.col_leaves('s_col') l JOIN sift._test_col_leaf_stats('s_col') st USING (leaf) ORDER BY leaf;
 leaf |     path      | ndistinct | min | max  | elems_per_row 
------+---------------+-----------+-----+------+---------------
    0 | id            |      5000 |   1 | 5000 |             0
    1 | k             |         7 |   0 |    6 |             0
    2 | flag          |         2 |   0 |    1 |             0
    3 | t             |         5 |   0 |    0 |             0
    4 | f             |      5000 |   0 |    0 |             0
    5 | items[]       |         0 |   0 |    0 |             2
    6 | items[].c     |         4 |   0 |    0 |             0
    7 | items[].q     |        12 |   0 |   11 |             0
    8 | items#present |         1 |   1 |    1 |             0
(9 rows)

-- rows with other values in a second segment: the text leaf reports its largest dictionary, the ranges widen (the rows still in a write buffer are not counted)
INSERT INTO s SELECT 5000 + i, 100 + i % 3, true, 'w' || (i % 40), 1.5, '[]'::jsonb FROM generate_series(1, 2000) i;
SELECT sift.col_promote('s_col') > 0 AS promoted;
 promoted 
----------
 t
(1 row)

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

SELECT l.leaf, l.path, round(st.ndistinct) AS ndistinct, st.min, st.max, st.elems_per_row
FROM sift.col_leaves('s_col') l JOIN sift._test_col_leaf_stats('s_col') st USING (leaf) WHERE l.leaf IN (0, 1, 3, 6, 7) ORDER BY leaf;
 leaf |   path    | ndistinct | min | max  | elems_per_row 
------+-----------+-----------+-----+------+---------------
    0 | id        |      7000 |   1 | 7000 |             0
    1 | k         |       103 |   0 |  102 |             0
    3 | t         |        45 |   0 |    0 |             0
    6 | items[].c |         4 |   0 |    0 |             0
    7 | items[].q |        12 |   0 |   11 |             0
(5 rows)

-- a sift_table reports what ANALYZE found for its columns (a share of the rows when negative), nothing before
CREATE TABLE t (a int, b text, c float8) USING sift_table;
INSERT INTO t SELECT i, 'v' || (i % 9), i FROM generate_series(1, 3000) i;
SELECT leaf, ndistinct FROM sift._test_col_leaf_stats('t') ORDER BY leaf;
 leaf | ndistinct 
------+-----------
    0 |         0
    1 |         0
    2 |         0
(3 rows)

ANALYZE t;
SELECT leaf, round(ndistinct) AS ndistinct FROM sift._test_col_leaf_stats('t') ORDER BY leaf;
 leaf | ndistinct 
------+-----------
    0 |      3000
    1 |         9
    2 |      3000
(3 rows)

RESET sift.async_maintenance;
DROP TABLE t;
DROP TABLE s;
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;