-- The column source of sift_colstub: per unit dictionaries (codes in first-seen order, not comparable between units), zone
-- prune, counters, bounded memory and the statistics of the planner.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_test;
-- about six superblocks of 256 pages; the id grows with the position in the heap
CREATE TABLE u (id int, b int8, d date, f float8, seg text, kind text, pad text) WITH (fillfactor = 10, autovacuum_enabled = false);
INSERT INTO u
SELECT i,
	   CASE WHEN i <= 6000 THEN NULL ELSE i * 10000000000::int8 END,
	   date '2024-01-01' + i / 100,
	   i / 10.0,
	   -- the first value of every unit differs: 'a' + (the position) and then the three others in an order that depends on it
	   chr(97 + (i / 2500 + i % 3) % 4),
	   'k' || (i % 5),
	   NULL
FROM generate_series(1, 16000) i;
CREATE INDEX u_col ON u USING sift_colstub (id, b, d, f, seg, kind);
ANALYZE u;
SELECT count(*) AS units_total FROM sift._test_aggstub_units('u_col') \gset
-- dictionaries: one per unit and text leaf, with an id of its own; a value has one code in a unit, which differs between units
SELECT count(*) = :units_total AS one_per_unit, count(DISTINCT id) = count(*) AS ids_are_unique
FROM sift._test_aggstub_dicts('u_col', 4);
 one_per_unit | ids_are_unique 
--------------+----------------
 t            | t
(1 row)

SELECT count(*) = :units_total * 2 AS two_text_leaves_per_unit, count(DISTINCT id) = count(*) AS ids_unique_over_leaves
FROM sift._test_aggstub_dicts('u_col');
 two_text_leaves_per_unit | ids_unique_over_leaves 
--------------------------+------------------------
 t                        | t
(1 row)

SELECT unit, n, nbytes FROM sift._test_aggstub_dicts('u_col', 4) ORDER BY unit;
 unit | n | nbytes 
------+---+--------
    0 | 4 |      4
    1 | 4 |      4
    2 | 4 |      4
    3 | 4 |      4
    4 | 4 |      4
    5 | 3 |      3
(6 rows)

SELECT unit, n, nbytes FROM sift._test_aggstub_dicts('u_col', 5) WHERE unit < 2 ORDER BY unit;
 unit | n | nbytes 
------+---+--------
    0 | 5 |     10
    1 | 5 |     10
(2 rows)

-- dense codes, assigned in the order the values are first seen in the unit
SELECT count(*) AS codes_not_first_seen FROM (
	SELECT code, coalesce(max(code) OVER (PARTITION BY unit ORDER BY ord ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), -1) AS before
	FROM sift._test_aggstub_dump('u_col', 4)) x WHERE code > before + 1;
 codes_not_first_seen 
----------------------
                    0
(1 row)

SELECT count(*) AS value_with_two_codes_in_a_unit FROM (
	SELECT unit, val FROM sift._test_aggstub_dump('u_col', 4) GROUP BY unit, val HAVING count(DISTINCT code) > 1) x;
 value_with_two_codes_in_a_unit 
--------------------------------
                              0
(1 row)

SELECT count(*) AS code_with_two_values_in_a_unit FROM (
	SELECT unit, code FROM sift._test_aggstub_dump('u_col', 4) GROUP BY unit, code HAVING count(DISTINCT val) > 1) x;
 code_with_two_values_in_a_unit 
--------------------------------
                              0
(1 row)

-- ... and the same value has different codes in different units
SELECT val, count(DISTINCT code) > 1 AS codes_differ_between_units, array_agg(code ORDER BY unit) AS code_per_unit
FROM (SELECT DISTINCT unit, val, code FROM sift._test_aggstub_dump('u_col', 4) WHERE val IS NOT NULL) d GROUP BY val ORDER BY val;
 val | codes_differ_between_units | code_per_unit 
-----+----------------------------+---------------
 a   | t                          | {2,3,2,1,3,2}
 b   | t                          | {0,2,3,2,2}
 c   | t                          | {1,0,0,0,0,0}
 d   | t                          | {3,1,1,3,1,1}
(4 rows)

SELECT unit, (array_agg(val ORDER BY ord))[1] AS first_value, (array_agg(code ORDER BY ord))[1] AS its_code
FROM sift._test_aggstub_dump('u_col', 4) GROUP BY unit ORDER BY unit;
 unit | first_value | its_code 
------+-------------+----------
    0 | b           |        0
    1 | c           |        0
    2 | c           |        0
    3 | c           |        0
    4 | c           |        0
    5 | c           |        0
(6 rows)

-- zone prune: a unit with no value of the leaf in [lo, hi] is skipped (the ids of a unit are a range)
SELECT array_agg(sb ORDER BY sb) AS all_sbs FROM sift._test_aggstub_units('u_col');
    all_sbs    
---------------
 {0,1,2,3,4,5}
(1 row)

SELECT min(id)::int AS lo2, max(id)::int AS hi2 FROM (SELECT val::int AS id FROM sift._test_aggstub_dump('u_col', 0, 2, 3)) x \gset
SELECT array_agg(sb ORDER BY sb) AS sbs_of_sb2 FROM sift._test_aggstub_units('u_col', 0, NULL, 0, :lo2, :hi2);
 sbs_of_sb2 
------------
 {2}
(1 row)

SELECT array_agg(sb ORDER BY sb) AS sbs_around_sb2 FROM sift._test_aggstub_units('u_col', 0, NULL, 0, :lo2 - 1, :hi2 + 1);
 sbs_around_sb2 
----------------
 {1,2,3}
(1 row)

SELECT array_agg(sb ORDER BY sb) AS sbs_edge_low FROM sift._test_aggstub_units('u_col', 0, NULL, 0, :lo2 - 100, :lo2);
 sbs_edge_low 
--------------
 {1,2}
(1 row)

SELECT array_agg(sb ORDER BY sb) AS sbs_edge_high FROM sift._test_aggstub_units('u_col', 0, NULL, 0, :hi2, :hi2 + 100);
 sbs_edge_high 
---------------
 {2,3}
(1 row)

SELECT count(*) AS none_in_range FROM sift._test_aggstub_units('u_col', 0, NULL, 0, 100000, 200000);
 none_in_range 
---------------
             0
(1 row)

SELECT count(*) AS empty_range FROM sift._test_aggstub_units('u_col', 0, NULL, 0, 5, 4);
 empty_range 
-------------
           0
(1 row)

SELECT count(*) AS all_of_them FROM sift._test_aggstub_units('u_col', 0, NULL, 0, -1000, 1000000);
 all_of_them 
-------------
           6
(1 row)

-- an int8 leaf with NULLs: the units with only NULLs hold no value in any range
SELECT count(*) < :units_total AS units_with_values_fewer, count(*) > 0 AS units_with_values FROM sift._test_aggstub_units('u_col', 0, NULL, 1, -9223372036854775808, 9223372036854775807);
 units_with_values_fewer | units_with_values 
-------------------------+-------------------
 t                       | t
(1 row)

SELECT count(*) = (SELECT count(DISTINCT unit) FROM sift._test_aggstub_dump('u_col', 1) WHERE val IS NOT NULL) AS as_the_dump
FROM sift._test_aggstub_units('u_col', 0, NULL, 1, -9223372036854775808, 9223372036854775807);
 as_the_dump 
-------------
 t
(1 row)

-- a date leaf (int32 days)
SELECT count(*) AS units_in_the_date_range FROM sift._test_aggstub_units('u_col', 0, NULL, 2, (date '2024-02-01' - date '2000-01-01'), (date '2024-02-15' - date '2000-01-01'));
 units_in_the_date_range 
-------------------------
                       2
(1 row)

-- a leaf that is not an integer cannot prune: every unit comes
SELECT count(*) AS float_leaf_no_prune FROM sift._test_aggstub_units('u_col', 0, NULL, 3, 0, 0);
 float_leaf_no_prune 
---------------------
                   6
(1 row)

SELECT count(*) AS text_leaf_no_prune FROM sift._test_aggstub_units('u_col', 0, NULL, 4, 0, 0);
 text_leaf_no_prune 
--------------------
                  6
(1 row)

-- counters: units returned, units skipped by the zone, rows of the units returned, bytes decoded
SELECT units = :units_total AS units, units_pruned, rows = (SELECT count(*) FROM u) AS rows, bytes > 0 AS bytes
FROM sift._test_aggstub_counters('u_col');
 units | units_pruned | rows | bytes 
-------+--------------+------+-------
 t     |            0 | t    | t
(1 row)

SELECT units, units_pruned, rows = (SELECT count(*) FROM sift._test_aggstub_dump('u_col', 0, 2, 3)) AS rows_of_sb2, bytes > 0 AS bytes
FROM sift._test_aggstub_counters('u_col', 0, NULL, 0, :lo2, :hi2);
 units | units_pruned | rows_of_sb2 | bytes 
-------+--------------+-------------+-------
     1 |            5 | t           | t
(1 row)

SELECT units, units_pruned, rows, bytes > 0 AS bytes FROM sift._test_aggstub_counters('u_col', 0, NULL, 0, 100000, 200000);
 units | units_pruned | rows | bytes 
-------+--------------+------+-------
     0 |            6 |    0 | t
(1 row)

-- memory is bounded by one unit: reading all units holds about what reading one holds
SELECT units AS units_one, peak_bytes AS peak_one, max_rows AS rows_one FROM sift._test_aggstub_memory('u_col', 1, 2) \gset
SELECT units AS units_all, peak_bytes AS peak_all FROM sift._test_aggstub_memory('u_col') \gset
SELECT :units_one = 1 AS one_unit, :units_all = :units_total AS all_units, :peak_all < :peak_one * 1.5 AS bounded,
	   :peak_one / :rows_one < 2000 AS bytes_per_row_small;
 one_unit | all_units | bounded | bytes_per_row_small 
----------+-----------+---------+---------------------
 t        | t         | t       | t
(1 row)

-- statistics of the planner: pg_class plus a sample of two units
SELECT leaf, nrows::int AS nrows, nunits::int AS nunits, allvisible_frac, ndistinct::int AS ndistinct, min::numeric(30, 0) AS min, max::numeric(30, 0) AS max
FROM sift._test_aggstub_stats('u_col') ORDER BY leaf;
 leaf | nrows | nunits | allvisible_frac | ndistinct |       min       |       max       
------+-------+--------+-----------------+-----------+-----------------+-----------------
    0 | 16000 |      6 |               0 |     16000 |               1 |           12864
    1 | 16000 |      6 |               0 |      2816 | 100490000000000 | 128640000000000
    2 | 16000 |      6 |               0 |        39 |            8766 |            8894
    3 | 16000 |      6 |               0 |     16000 |               0 |            1286
    4 | 16000 |      6 |               0 |         4 |               0 |               0
    5 | 16000 |      6 |               0 |         5 |               0 |               0
(6 rows)

-- before ANALYZE there is no reltuples: estimated from the sample (CREATE INDEX leaves it unknown on 18, and sets it on 17)
CREATE TABLE u2 (id int, seg text) WITH (fillfactor = 10, autovacuum_enabled = false);
INSERT INTO u2 SELECT i, 'x' || i % 3 FROM generate_series(1, 4000) i;
CREATE INDEX u2_col ON u2 USING sift_colstub (id, seg);
UPDATE pg_class SET reltuples = -1 WHERE oid = 'u2'::regclass;
SELECT (SELECT reltuples FROM pg_class WHERE relname = 'u2') AS reltuples;
 reltuples 
-----------
        -1
(1 row)

SELECT leaf, abs(nrows - 4000) < 400 AS nrows_close, nunits::int, ndistinct::int, min, max FROM sift._test_aggstub_stats('u2_col') ORDER BY leaf;
 leaf | nrows_close | nunits | ndistinct | min | max  
------+-------------+--------+-----------+-----+------
    0 | t           |      1 |      4000 |   1 | 4000
    1 | t           |      1 |         3 |   0 |    0
(2 rows)

VACUUM u2;
SELECT leaf, nrows::int, allvisible_frac FROM sift._test_aggstub_stats('u2_col') ORDER BY leaf;
 leaf | nrows | allvisible_frac 
------+-------+-----------------
    0 |  4000 |               1
    1 |  4000 |               1
(2 rows)

-- an empty table, a table of NULLs
CREATE TABLE u3 (id int, seg text, js jsonb);
CREATE INDEX u3_col ON u3 USING sift_colstub (id, seg, js) WITH (paths = 'js: k int4, a[]{x text}');
SELECT leaf, nrows, nunits, ndistinct, min, max, elems_per_row FROM sift._test_aggstub_stats('u3_col') ORDER BY leaf;
 leaf | nrows | nunits | ndistinct | min | max | elems_per_row 
------+-------+--------+-----------+-----+-----+---------------
    0 |     0 |      0 |         0 |   0 |   0 |             0
    1 |     0 |      0 |         0 |   0 |   0 |             0
    2 |     0 |      0 |         0 |   0 |   0 |             0
    3 |     0 |      0 |         0 |   0 |   0 |             0
    4 |     0 |      0 |         0 |   0 |   0 |             0
(5 rows)

SELECT count(*) AS units FROM sift._test_aggstub_units('u3_col');
 units 
-------
     0
(1 row)

INSERT INTO u3 SELECT NULL, NULL, NULL FROM generate_series(1, 10);
SELECT leaf, nrows, nunits, ndistinct, min, max, elems_per_row FROM sift._test_aggstub_stats('u3_col') ORDER BY leaf;
 leaf | nrows | nunits | ndistinct | min | max | elems_per_row 
------+-------+--------+-----------+-----+-----+---------------
    0 |    10 |      1 |         0 |   0 |   0 |             0
    1 |    10 |      1 |         0 |   0 |   0 |             0
    2 |    10 |      1 |         0 |   0 |   0 |             0
    3 |    10 |      1 |         0 |   0 |   0 |             0
    4 |    10 |      1 |         0 |   0 |   0 |             0
(5 rows)

SELECT count(*) AS units, sum(nrow) AS rows FROM sift._test_aggstub_units('u3_col');
 units | rows 
-------+------
     1 |   10
(1 row)

-- the source reads heap pages: a table of another access method is refused
CREATE TABLE u4 (id int, seg int) USING sift_table;
CREATE INDEX u4_col ON u4 USING sift_colstub (id, seg);
ERROR:  sift_colstub indexes require a heap table
HINT:  Index a heap table.
DROP TABLE u, u2, u3, u4;
-- the shared database of a plain installcheck goes on with the next test
SET client_min_messages = warning;
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT USAGE ON SCHEMA public TO PUBLIC;
RESET client_min_messages;
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;