-- A join to the parent of a connected index that reads no column of the parent (EXISTS, NOT EXISTS, a join that counts) is answered from the flag the segments keep that the
-- parent row was found (docs/COL-CONNECT.md, "Found"), less the keys that changed in the parent since the oldest stamp. Whatever happens to either table the rows are those
-- of the normal plan: every state the flag cannot answer for (a write buffer, a segment without a stamp, a parent row inserted since)
-- is read by the ordinary join, and EXPLAIN says why.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_test;
SET max_parallel_workers_per_gather = 0;
SET jit = off;
SET sift.agg_force = on;
SET sift.agg_lookup_cache_kb = 0;
SET sift.agg_lookup_shared_kb = 0;
-- the answer under the agg scan and under the normal plan, and what the scan did: proofs used, keys outside the proof, why not, parent leaf reads and heap fetches
CREATE FUNCTION found_run(q text, OUT same boolean, OUT proofs int, OUT keys int, OUT why text, OUT leaf_reads int, OUT heap_fetches int, OUT workers int)
LANGUAGE plpgsql AS $$
DECLARE
	p jsonb;
	a text;
	b text;
BEGIN
	SET LOCAL sift.enable_agg = on;
	EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF, FORMAT JSON) ' || q INTO p;
	EXECUTE 'SELECT coalesce(json_agg(t)::text, '''') FROM (' || q || ') t' INTO a;
	SET LOCAL sift.enable_agg = off;
	EXECUTE 'SELECT coalesce(json_agg(t)::text, '''') FROM (' || q || ') t' INTO b;
	SET LOCAL sift.enable_agg = on;
	same := a = b;
	proofs := coalesce(jsonb_path_query_first(p, '$.**."Found Proofs"')::text::int, 0);
	keys := coalesce(jsonb_path_query_first(p, '$.**."Found Keys Outside The Proof"')::text::int, 0);
	why := jsonb_path_query_first(p, '$.**."Found Fallback"') #>> '{}';
	leaf_reads := coalesce(jsonb_path_query_first(p, '$.**."Parent Leaf Reads"')::text::int, 0);
	heap_fetches := coalesce(jsonb_path_query_first(p, '$.**."Parent Heap Fetches"')::text::int, 0);
	workers := coalesce(jsonb_path_query_first(p, '$.**."Workers Launched"')::text::int, 0);
END $$;
CREATE TABLE dim_a (id int PRIMARY KEY, label text, region int) WITH (autovacuum_enabled = false);
INSERT INTO dim_a SELECT i, 'l' || i, i % 7 FROM generate_series(1, 200) i;
CREATE INDEX dim_a_col ON dim_a USING sift_col (id, label, region);
NOTICE:  sift_col index "dim_a_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('dim_a_col');
-- no foreign key: rows with a key the parent has no row for (999) and with a NULL key
CREATE TABLE events (id int PRIMARY KEY, dim_a_id int, v int) WITH (autovacuum_enabled = false);
INSERT INTO events SELECT i, CASE WHEN i % 97 = 0 THEN NULL WHEN i % 89 = 0 THEN 999 ELSE 1 + i % 200 END, i % 10 FROM generate_series(1, 3000) i;
CREATE INDEX events_col ON events USING sift_col (id, dim_a_id, v) WITH (connect = 'dim_a_id -> dim_a(id)');
NOTICE:  sift_col index "events_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('events_col');
CREATE TABLE shapes (n int, sql text);
INSERT INTO shapes VALUES
 (1, $q$SELECT count(*) FROM events e JOIN dim_a d ON d.id = e.dim_a_id$q$),
 (2, $q$SELECT count(*) FROM events e WHERE EXISTS (SELECT 1 FROM dim_a d WHERE d.id = e.dim_a_id)$q$),
 (3, $q$SELECT count(*) FROM events e WHERE NOT EXISTS (SELECT 1 FROM dim_a d WHERE d.id = e.dim_a_id)$q$),
 (4, $q$SELECT count(DISTINCT d.id) FROM events e JOIN dim_a d ON d.id = e.dim_a_id$q$),
 (5, $q$SELECT e.v, count(*) FROM events e WHERE e.id > 500 AND EXISTS (SELECT 1 FROM dim_a d WHERE d.id = e.dim_a_id) GROUP BY 1 ORDER BY 1$q$);
CREATE FUNCTION phase() RETURNS TABLE (n int, same boolean, proofs int, keys int, why text) LANGUAGE sql AS $$
	SELECT s.n, r.same, r.proofs, r.keys, r.why FROM shapes s, LATERAL found_run(s.sql) r ORDER BY s.n
$$;
-- the journal of the parent starts after the first build: the segments have no stamp
SELECT * FROM phase();
 n | same | proofs | keys |          why          
---+------+--------+------+-----------------------
 1 | t    |      0 |    0 | segment without stamp
 2 | t    |      0 |    0 | segment without stamp
 3 | t    |      0 |    0 | segment without stamp
 4 | t    |      0 |    0 | segment without stamp
 5 | t    |      0 |    0 | segment without stamp
(5 rows)

SELECT sift.col_maintain('events_col') AS rewritten;
 rewritten 
-----------
 t
(1 row)

SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

-- parent rows deleted: their keys are outside the proof, the events of them are orphans now
DELETE FROM dim_a WHERE id IN (7, 8);
SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    2 | 
 2 | t    |      1 |    2 | 
 3 | t    |      1 |    2 | 
 4 | t    |      1 |    2 | 
 5 | t    |      1 |    2 | 
(5 rows)

-- a parent row written (a new version is an insert): the flag does not tell whether another key appeared
UPDATE dim_a SET label = 'changed' WHERE id = 9;
SELECT * FROM phase();
 n | same | proofs | keys |                 why                  
---+------+--------+------+--------------------------------------
 1 | t    |      0 |    0 | parent rows inserted since the stamp
 2 | t    |      0 |    0 | parent rows inserted since the stamp
 3 | t    |      0 |    0 | parent rows inserted since the stamp
 4 | t    |      0 |    0 | parent rows inserted since the stamp
 5 | t    |      0 |    0 | parent rows inserted since the stamp
(5 rows)

-- changed keys at the share of the setting: the segments are written again under a stamp after the changes
SET sift.col_connect_refresh_pct = 1;
SELECT sift.col_maintain('events_col') AS rewritten;
 rewritten 
-----------
 t
(1 row)

SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

RESET sift.col_connect_refresh_pct;
-- events written after the stamp are in the write buffer: a row of a parent that is there, one that is not, a NULL key, a key whose row is deleted, a row whose key changed
INSERT INTO events VALUES (4001, 5, 1), (4002, 999, 2), (4003, NULL, 3), (4004, 7, 4), (4005, 150, 5);
UPDATE events SET dim_a_id = 7 WHERE id = 10;
UPDATE events SET dim_a_id = 11 WHERE id = 20;
DELETE FROM events WHERE id = 30;
SELECT * FROM phase();
 n | same | proofs | keys |     why      
---+------+--------+------+--------------
 1 | t    |      0 |    0 | write buffer
 2 | t    |      0 |    0 | write buffer
 3 | t    |      0 |    0 | write buffer
 4 | t    |      0 |    0 | write buffer
 5 | t    |      0 |    0 | write buffer
(5 rows)

SELECT sift.col_promote('events_col') >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

-- a parent row inserted after the stamp, for a key the events have rows of
INSERT INTO dim_a VALUES (999, 'late', 3);
SELECT * FROM phase();
 n | same | proofs | keys |                 why                  
---+------+--------+------+--------------------------------------
 1 | t    |      0 |    0 | parent rows inserted since the stamp
 2 | t    |      0 |    0 | parent rows inserted since the stamp
 3 | t    |      0 |    0 | parent rows inserted since the stamp
 4 | t    |      0 |    0 | parent rows inserted since the stamp
 5 | t    |      0 |    0 | parent rows inserted since the stamp
(5 rows)

DELETE FROM dim_a WHERE id = 999;
INSERT INTO dim_a VALUES (8, 'back', 1);
SELECT * FROM phase();
 n | same | proofs | keys |                 why                  
---+------+--------+------+--------------------------------------
 1 | t    |      0 |    0 | parent rows inserted since the stamp
 2 | t    |      0 |    0 | parent rows inserted since the stamp
 3 | t    |      0 |    0 | parent rows inserted since the stamp
 4 | t    |      0 |    0 | parent rows inserted since the stamp
 5 | t    |      0 |    0 | parent rows inserted since the stamp
(5 rows)

SET sift.col_connect_refresh_pct = 1;
SELECT sift.col_maintain('events_col') AS rewritten;
 rewritten 
-----------
 t
(1 row)

RESET sift.col_connect_refresh_pct;
SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

-- segments merged: the stamps are those of the merge
SELECT sift.col_merge('events_col', true) >= 0 AS merged;
 merged 
--------
 t
(1 row)

SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

VACUUM dim_a, events;
SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

-- the rows of a transaction that has not committed: the reader's own writes are in the write buffer
BEGIN;
INSERT INTO events VALUES (4101, 12, 1), (4102, 4242, 2);
DELETE FROM dim_a WHERE id = 12;
SELECT * FROM phase();
 n | same | proofs | keys |     why      
---+------+--------+------+--------------
 1 | t    |      0 |    0 | write buffer
 2 | t    |      0 |    0 | write buffer
 3 | t    |      0 |    0 | write buffer
 4 | t    |      0 |    0 | write buffer
 5 | t    |      0 |    0 | write buffer
(5 rows)

ROLLBACK;
SELECT * FROM phase() WHERE n = 1;
 n | same | proofs | keys |     why      
---+------+--------+------+--------------
 1 | t    |      0 |    0 | write buffer
(1 row)

SELECT sift.col_promote('events_col') >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

-- the setting is the switch
SET sift.agg_connect_found = off;
SELECT * FROM phase() WHERE n = 1;
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      0 |    0 | 
(1 row)

RESET sift.agg_connect_found;
-- a parent whose keys (60,000 of them 1,000 apart, 9 bytes each) need more than half of maintenance_work_mem is looked up by its btree: filled, and the flags answer the join
CREATE TABLE dim_big (id int PRIMARY KEY, label text) WITH (autovacuum_enabled = false);
INSERT INTO dim_big SELECT i * 1000, repeat('x', 40) || i FROM generate_series(1, 60000) i;
CREATE INDEX dim_big_col ON dim_big USING sift_col (id, label) WITH (order_array_columns = 'off');
NOTICE:  sift_col index "dim_big_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('dim_big_col');
CREATE TABLE big_events (id int PRIMARY KEY, dim_big_id int NOT NULL) WITH (autovacuum_enabled = false);
INSERT INTO big_events SELECT i, 1000 * (1 + i % 60000) FROM generate_series(1, 150000) i;
SET maintenance_work_mem = '1MB';
CREATE INDEX big_events_col ON big_events USING sift_col (id, dim_big_id) WITH (connect = 'dim_big_id -> dim_big(id)', key_cut = 'off', order_array_columns = 'off');
NOTICE:  sift_col index "big_events_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('big_events_col');
SELECT fill FROM sift.col_connections WHERE child = 'big_events_col'::regclass;
   fill   
----------
 unfilled
(1 row)

SELECT sift.col_maintain('big_events_col') AS rewritten;
 rewritten 
-----------
 t
(1 row)

SELECT fill FROM sift.col_connections WHERE child = 'big_events_col'::regclass;
  fill  
--------
 filled
(1 row)

SELECT * FROM found_run($q$SELECT count(*) FROM big_events e JOIN dim_big d ON d.id = e.dim_big_id$q$);
 same | proofs | keys | why | leaf_reads | heap_fetches | workers 
------+--------+------+-----+------------+--------------+---------
 t    |      1 |    0 |     |          0 |            0 |       0
(1 row)

RESET maintenance_work_mem;
-- a parent that is written all the time, and children with a parent row for every key (a foreign key keeps them so): a parent row inserted or written again after the stamp changes no flag,
-- only the keys that ended are outside the proof
CREATE TABLE dim_c (id int PRIMARY KEY, label text, region int, note int) WITH (autovacuum_enabled = false, fillfactor = 50);
INSERT INTO dim_c SELECT i, 'l' || i, i % 7, 0 FROM generate_series(1, 300) i;
CREATE INDEX dim_c_col ON dim_c USING sift_col (id, label, region);
NOTICE:  sift_col index "dim_c_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('dim_c_col');
CREATE TABLE facts (id int PRIMARY KEY, dim_c_id int, v int) WITH (autovacuum_enabled = false);
INSERT INTO facts SELECT i, CASE WHEN i % 53 = 0 THEN NULL ELSE 1 + i % 300 END, i % 10 FROM generate_series(1, 3000) i;
CREATE INDEX facts_col ON facts USING sift_col (id, dim_c_id, v) WITH (connect = 'dim_c_id -> dim_c(id)');
NOTICE:  sift_col index "facts_col": 2 automatic choices (key_columns, track_changes)
DETAIL:  key_columns = dim_c_id; track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_col');
CREATE TABLE shapes2 (n int, sql text);
INSERT INTO shapes2 SELECT n, replace(replace(replace(sql, 'events', 'facts'), 'dim_a_id', 'dim_c_id'), 'dim_a', 'dim_c') FROM shapes;
-- probes: the keys the scan looked up in the btree of the parent (a key that only updates ended is held without one)
CREATE FUNCTION phase2() RETURNS TABLE (n int, same boolean, proofs int, keys int, probes int, why text) LANGUAGE sql AS $$
	SELECT s.n, r.same, r.proofs, r.keys, r.heap_fetches, r.why FROM shapes2 s, LATERAL found_run(s.sql) r ORDER BY s.n
$$;
SELECT sift.col_maintain('facts_col') AS rewritten;
 rewritten 
-----------
 t
(1 row)

SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |    0 |      0 | 
 2 | t    |      1 |    0 |      0 | 
 3 | t    |      1 |    0 |      0 | 
 4 | t    |      1 |    0 |      0 | 
 5 | t    |      1 |    0 |      0 | 
(5 rows)

-- an update that keeps the key: a new version of the row, its key is outside the proof and the parent holds it
UPDATE dim_c SET label = 'changed' WHERE id <= 5;
SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |    5 |      0 | 
 2 | t    |      1 |    5 |      0 | 
 3 | t    |      1 |    5 |      0 | 
 4 | t    |      1 |    5 |      0 | 
 5 | t    |      1 |    5 |      0 | 
(5 rows)

-- the same, a heap-only update (no indexed column changes)
UPDATE dim_c SET note = note + 1 WHERE id BETWEEN 6 AND 10;
SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |    5 |      0 | 
 2 | t    |      1 |    5 |      0 | 
 3 | t    |      1 |    5 |      0 | 
 4 | t    |      1 |    5 |      0 | 
 5 | t    |      1 |    5 |      0 | 
(5 rows)

-- an update that assigns the key column the same value is not known to keep it: its keys are probed
UPDATE dim_c SET id = id WHERE id BETWEEN 50 AND 52;
SELECT * FROM phase2() WHERE n = 1;
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |    8 |      3 | 
(1 row)

-- a new key, a deleted key, a key deleted and inserted again, a key changed
INSERT INTO dim_c VALUES (1000, 'new', 1), (1001, 'new', 2);
DELETE FROM dim_c WHERE id = 20;
DELETE FROM dim_c WHERE id = 21;
INSERT INTO dim_c VALUES (21, 'again', 3);
UPDATE dim_c SET id = 1002 WHERE id = 22;
SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |   11 |      6 | 
 2 | t    |      1 |   11 |      6 | 
 3 | t    |      1 |   11 |      6 | 
 4 | t    |      1 |   11 |      6 | 
 5 | t    |      1 |   11 |      6 | 
(5 rows)

-- the writes of a transaction that has not committed are the reader's own
BEGIN;
UPDATE dim_c SET label = 'mine' WHERE id BETWEEN 30 AND 40;
DELETE FROM dim_c WHERE id = 41;
INSERT INTO dim_c VALUES (1003, 'mine', 1);
SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |   23 |      7 | 
 2 | t    |      1 |   23 |      7 | 
 3 | t    |      1 |   23 |      7 | 
 4 | t    |      1 |   23 |      7 | 
 5 | t    |      1 |   23 |      7 | 
(5 rows)

ROLLBACK;
SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |   11 |      6 | 
 2 | t    |      1 |   11 |      6 | 
 3 | t    |      1 |   11 |      6 | 
 4 | t    |      1 |   11 |      6 | 
 5 | t    |      1 |   11 |      6 | 
(5 rows)

VACUUM dim_c;
SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |   11 |      6 | 
 2 | t    |      1 |   11 |      6 | 
 3 | t    |      1 |   11 |      6 | 
 4 | t    |      1 |   11 |      6 | 
 5 | t    |      1 |   11 |      6 | 
(5 rows)

-- more keys deleted than are worth a probe each: the join reads the parent
CREATE TABLE dim_p (id int PRIMARY KEY, label text) WITH (autovacuum_enabled = false);
INSERT INTO dim_p SELECT i, 'l' || i FROM generate_series(1, 6000) i;
CREATE INDEX dim_p_col ON dim_p USING sift_col (id, label);
NOTICE:  sift_col index "dim_p_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('dim_p_col');
CREATE TABLE facts_p (id int PRIMARY KEY, dim_p_id int) WITH (autovacuum_enabled = false);
INSERT INTO facts_p SELECT i, 1 + i % 6000 FROM generate_series(1, 6000) i;
CREATE INDEX facts_p_col ON facts_p USING sift_col (id, dim_p_id) WITH (connect = 'dim_p_id -> dim_p(id)');
NOTICE:  sift_col index "facts_p_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_p_col');
SELECT sift.col_maintain('facts_p_col') AS rewritten;
 rewritten 
-----------
 t
(1 row)

DELETE FROM dim_p WHERE id <= 2000;
SELECT same, proofs, keys, why FROM found_run($q$SELECT count(*) FROM facts_p f WHERE EXISTS (SELECT 1 FROM dim_p d WHERE d.id = f.dim_p_id)$q$);
 same | proofs | keys | why 
------+--------+------+-----
 t    |      1 | 2000 | 
(1 row)

DELETE FROM dim_p WHERE id BETWEEN 2001 AND 2100;
SELECT same, proofs, keys, why FROM found_run($q$SELECT count(*) FROM facts_p f WHERE EXISTS (SELECT 1 FROM dim_p d WHERE d.id = f.dim_p_id)$q$);
 same | proofs | keys |               why                
------+--------+------+----------------------------------
 t    |      0 |    0 | too many keys deleted or changed
(1 row)

-- the key types of a connection
DO $$
DECLARE
	t text;
BEGIN
	FOREACH t IN ARRAY ARRAY['int2', 'int4', 'int8'] LOOP
		EXECUTE format('CREATE TABLE p_%1$s (id %1$s PRIMARY KEY, x int) WITH (autovacuum_enabled = false)', t);
		EXECUTE format('INSERT INTO p_%s SELECT i, i FROM generate_series(1, 300) i', t);
		EXECUTE format('CREATE INDEX p_%1$s_col ON p_%1$s USING sift_col (id, x)', t);
		EXECUTE format('CREATE TABLE c_%1$s (id int PRIMARY KEY, k %1$s) WITH (autovacuum_enabled = false)', t);
		EXECUTE format('INSERT INTO c_%s SELECT i, CASE WHEN i %% 50 = 0 THEN NULL ELSE 1 + i %% 320 END FROM generate_series(1, 4000) i', t);
		EXECUTE format('CREATE INDEX c_%1$s_col ON c_%1$s USING sift_col (id, k) WITH (connect = ''k -> p_%1$s(id)'')', t);
	END LOOP;
END $$;
NOTICE:  sift_col index "p_int2_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('p_int2_col');
NOTICE:  sift_col index "c_int2_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('c_int2_col');
NOTICE:  sift_col index "p_int4_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('p_int4_col');
NOTICE:  sift_col index "c_int4_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('c_int4_col');
NOTICE:  sift_col index "p_int8_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('p_int8_col');
NOTICE:  sift_col index "c_int8_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('c_int8_col');
SELECT sift.col_maintain(c) FROM unnest(ARRAY['c_int2_col', 'c_int4_col', 'c_int8_col']) c;
 col_maintain 
--------------
 t
 t
 t
(3 rows)

CREATE TABLE tshapes (t text, n int, sql text);
INSERT INTO tshapes SELECT t, n, format(q, t, t) FROM unnest(ARRAY['int2', 'int4', 'int8']) t, (VALUES
 (1, 'SELECT count(*) FROM c_%s e JOIN p_%s d ON d.id = e.k'),
 (2, 'SELECT count(*) FROM c_%s e WHERE EXISTS (SELECT 1 FROM p_%s d WHERE d.id = e.k)'),
 (3, 'SELECT count(*) FROM c_%s e WHERE NOT EXISTS (SELECT 1 FROM p_%s d WHERE d.id = e.k)')) s(n, q);
CREATE FUNCTION tphase() RETURNS TABLE (t text, n int, same boolean, proofs int, keys int, why text) LANGUAGE sql AS $$
	SELECT s.t, s.n, r.same, r.proofs, r.keys, r.why FROM tshapes s, LATERAL found_run(s.sql) r ORDER BY s.t, s.n
$$;
SELECT * FROM tphase();
  t   | n | same | proofs | keys | why 
------+---+------+--------+------+-----
 int2 | 1 | t    |      1 |    0 | 
 int2 | 2 | t    |      1 |    0 | 
 int2 | 3 | t    |      1 |    0 | 
 int4 | 1 | t    |      1 |    0 | 
 int4 | 2 | t    |      1 |    0 | 
 int4 | 3 | t    |      1 |    0 | 
 int8 | 1 | t    |      1 |    0 | 
 int8 | 2 | t    |      1 |    0 | 
 int8 | 3 | t    |      1 |    0 | 
(9 rows)

DELETE FROM p_int2 WHERE id = 3;
DELETE FROM p_int4 WHERE id = 3;
DELETE FROM p_int8 WHERE id = 3;
SELECT * FROM tphase();
  t   | n | same | proofs | keys | why 
------+---+------+--------+------+-----
 int2 | 1 | t    |      1 |    1 | 
 int2 | 2 | t    |      1 |    1 | 
 int2 | 3 | t    |      1 |    1 | 
 int4 | 1 | t    |      1 |    1 | 
 int4 | 2 | t    |      1 |    1 | 
 int4 | 3 | t    |      1 |    1 | 
 int8 | 1 | t    |      1 |    1 | 
 int8 | 2 | t    |      1 |    1 | 
 int8 | 3 | t    |      1 |    1 | 
(9 rows)

-- the same shapes in parallel scans: each participant makes the proof on the directory of the leader
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
SELECT * FROM tphase();
  t   | n | same | proofs | keys | why 
------+---+------+--------+------+-----
 int2 | 1 | t    |      1 |    1 | 
 int2 | 2 | t    |      1 |    1 | 
 int2 | 3 | t    |      1 |    1 | 
 int4 | 1 | t    |      1 |    1 | 
 int4 | 2 | t    |      1 |    1 | 
 int4 | 3 | t    |      1 |    1 | 
 int8 | 1 | t    |      1 |    1 | 
 int8 | 2 | t    |      1 |    1 | 
 int8 | 3 | t    |      1 |    1 | 
(9 rows)

SELECT * FROM phase();
 n | same | proofs | keys | why 
---+------+--------+------+-----
 1 | t    |      1 |    0 | 
 2 | t    |      1 |    0 | 
 3 | t    |      1 |    0 | 
 4 | t    |      1 |    0 | 
 5 | t    |      1 |    0 | 
(5 rows)

SELECT * FROM phase2();
 n | same | proofs | keys | probes | why 
---+------+--------+------+--------+-----
 1 | t    |      1 |   11 |      6 | 
 2 | t    |      1 |   11 |      6 | 
 3 | t    |      1 |   11 |      6 | 
 4 | t    |      1 |   11 |      6 | 
 5 | t    |      1 |   11 |      6 | 
(5 rows)

SELECT bool_or(workers > 0) AS parallel FROM shapes s, LATERAL found_run(s.sql) r;
 parallel 
----------
 t
(1 row)

RESET max_parallel_workers_per_gather;
-- the parent columns a join reads (not carried) come from the leaves of the parent's index, not its heap
CREATE TABLE tp (id int PRIMARY KEY, c_int2 int2, c_int4 int4, c_int8 int8, c_date date, c_ts timestamp, c_tstz timestamptz, c_bool boolean, c_text text,
				 c_varchar varchar(20), c_f8 float8, c_f4 float4, c_uuid uuid, c_time time, c_interval interval, c_num numeric(12, 2), c_bignum numeric(30, 2))
	WITH (autovacuum_enabled = false);
INSERT INTO tp SELECT i, i % 100, i % 1000, i * 10000000000, date '2026-01-01' + i % 60, timestamp '2026-01-01' + i * interval '1 hour',
				timestamptz '2026-01-01' + i * interval '1 hour', i % 2 = 0, 't' || i % 30, 'v' || i % 40, i / 4.0, i / 8.0,
				('00000000-0000-0000-0000-' || lpad(i::text, 12, '0'))::uuid, time '00:00' + i * interval '1 minute', i * interval '1 minute', i / 3.0, i * 1000000000000000000000.5
	FROM generate_series(1, 120) i;
CREATE INDEX tp_col ON tp USING sift_col (id, c_int2, c_int4, c_int8, c_date, c_ts, c_tstz, c_bool, c_text, c_varchar, c_f8, c_f4, c_uuid, c_time, c_interval, c_num,
										   c_bignum, (c_int4 * 2), (c_text IS NOT NULL));
NOTICE:  sift_col index "tp_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('tp_col');
CREATE TABLE tc (id int PRIMARY KEY, tp_id int) WITH (autovacuum_enabled = false);
INSERT INTO tc SELECT i, 1 + i % 120 FROM generate_series(1, 5000) i;
CREATE INDEX tc_col ON tc USING sift_col (id, tp_id) WITH (connect = 'tp_id -> tp(id)');
NOTICE:  sift_col index "tc_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('tc_col');
VACUUM (FREEZE, ANALYZE) tp, tc;
CREATE TABLE cols (n int, expr text);
INSERT INTO cols VALUES (1, 'p.c_int2'), (2, 'p.c_int4'), (3, 'p.c_int8'), (4, 'p.c_date'), (5, 'p.c_ts'), (6, 'p.c_tstz'), (7, 'p.c_bool'), (8, 'p.c_text'),
 (9, 'p.c_varchar'), (10, 'p.c_f8'), (11, 'p.c_f4'), (12, 'p.c_uuid'), (13, 'p.c_time'), (14, 'p.c_interval'), (15, 'p.c_num'), (16, 'p.c_bignum'),
 (17, 'p.c_int4 * 2'), (18, '(p.c_text IS NOT NULL)');
SELECT c.n, replace(c.expr, 'p.', '') AS read, r.same, r.leaf_reads, r.heap_fetches
	FROM cols c, LATERAL found_run(format('SELECT %s AS x, count(*) FROM tc JOIN tp p ON p.id = tc.tp_id GROUP BY 1 ORDER BY 1', c.expr)) r ORDER BY c.n;
 n  |         read         | same | leaf_reads | heap_fetches 
----+----------------------+------+------------+--------------
  1 | c_int2               | t    |          1 |            0
  2 | c_int4               | t    |          1 |            0
  3 | c_int8               | t    |          1 |            0
  4 | c_date               | t    |          1 |            0
  5 | c_ts                 | t    |          1 |            0
  6 | c_tstz               | t    |          1 |            0
  7 | c_bool               | t    |          1 |            0
  8 | c_text               | t    |          1 |            0
  9 | c_varchar            | t    |          1 |            0
 10 | c_f8                 | t    |          1 |            0
 11 | c_f4                 | t    |          1 |            0
 12 | c_uuid               | t    |          1 |            0
 13 | c_time               | t    |          1 |            0
 14 | c_interval           | t    |          1 |            0
 15 | c_num                | t    |          1 |            0
 16 | c_bignum             | t    |          1 |            0
 17 | c_int4 * 2           | t    |          1 |            0
 18 | (c_text IS NOT NULL) | t    |          1 |            0
(18 rows)

DROP TABLE facts_p, dim_p, tc, tp, big_events, dim_big, events, dim_a, facts, dim_c, shapes, shapes2, tshapes, cols CASCADE;
DROP TABLE c_int2, c_int4, c_int8, p_int2, p_int4, p_int8 CASCADE;
DROP FUNCTION found_run(text), phase(), phase2(), tphase();
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;