-- Custom Scan (SiftAgg) prunes the units of an OR by the zones: each disjunct is tested on its own (an AND of comparisons, a range, an IN list), and a unit
-- is read when any disjunct may match. An OR with a disjunct no zone can refuse (a text test, an arithmetic on the leaf) prunes nothing.
-- Every statement is run with the pushdown and with sift.enable_agg = off: the rows must be the same.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET default_statistics_target = 10000;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 10000; SET random_page_cost = 10000;
-- events in day order, and an owner that changes every 25000 events: both leaves have narrow zones; the padding makes a unit of about 8000 rows
CREATE TABLE ev (id int PRIMARY KEY, set_id int, day date, kind text, amount int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO ev
SELECT i, 1 + (i - 1) / 25000, date '2020-01-01' + (i - 1) / 300, (ARRAY['a', 'b', 'c', 'd'])[1 + i % 4], i % 100, repeat('x', 200)
FROM generate_series(1, 300000) i;
CREATE INDEX ev_col ON ev USING sift_col (set_id, day, kind, amount);
NOTICE:  sift_col index "ev_col": 1 automatic choice (rollup_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the index lacks the primary key column; rollup_columns = set_id, kind
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ev_col');
VACUUM (FREEZE, ANALYZE) ev;
-- the units a statement reads, and those it prunes ('normal' when SiftAgg does not run it)
CREATE FUNCTION units_of(q text) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.agg_units_read(q) $$;
-- the plan, and whether its rows are those of the normal plan
CREATE FUNCTION same(cond text) RETURNS TABLE (plan text, result_rows bigint, vs_normal_plan bigint) LANGUAGE plpgsql AS $$
DECLARE q text := format('SELECT set_id, count(*) AS n, sum(amount) AS s FROM ev WHERE %s GROUP BY set_id', cond);
BEGIN
	plan := units_of(q);
	EXECUTE format('CREATE TEMP TABLE r_on ON COMMIT DROP AS %s', q);
	SET LOCAL sift.enable_agg = off;
	EXECUTE format('CREATE TEMP TABLE r_off ON COMMIT DROP AS %s', q);
	RESET sift.enable_agg;
	result_rows := (SELECT count(*) FROM r_on);
	vs_normal_plan := (SELECT count(*) FROM ((SELECT * FROM r_on EXCEPT ALL SELECT * FROM r_off) UNION ALL (SELECT * FROM r_off EXCEPT ALL SELECT * FROM r_on)) d);
	RETURN NEXT;
END $$;
-- the units of one window, and of two (a range and the same one a year later): the second adds its own units, and the ones between are pruned
SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 2 pruned 35 |           2 |              0
(1 row)

SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR day >= '2020-09-01' AND day <= '2020-09-30'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 5 pruned 32 |           4 |              0
(1 row)

SELECT * FROM same($$(day >= '2020-03-01' AND day <= '2020-03-31') OR (day >= '2020-09-01' AND day <= '2020-09-30') OR (day >= '2021-01-01' AND day < '2021-01-15')$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 6 pruned 31 |           5 |              0
(1 row)

-- BETWEEN, IN lists and equalities are disjuncts too
SELECT * FROM same($$day BETWEEN '2020-03-01' AND '2020-03-31' OR day BETWEEN '2021-03-01' AND '2021-03-31'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 4 pruned 33 |           3 |              0
(1 row)

SELECT * FROM same($$day IN ('2020-02-10', '2021-02-10') OR day = '2020-12-25'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 3 pruned 34 |           2 |              0
(1 row)

SELECT * FROM same($$day IN ('2020-02-10', '2020-02-11') OR day IN ('2021-02-10', '2021-02-11')$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 2 pruned 35 |           2 |              0
(1 row)

-- a disjunct that matches nothing adds no unit
SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR day > '2030-01-01'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 2 pruned 35 |           2 |              0
(1 row)

SELECT * FROM same($$day >= '2020-03-31' AND day <= '2020-03-01' OR day >= '2020-09-01' AND day <= '2020-09-30'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 3 pruned 34 |           2 |              0
(1 row)

-- other leaves: a disjunct on the owner and one on the day
SELECT * FROM same($$set_id = 2 OR day >= '2021-03-01' AND day <= '2021-03-31'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 6 pruned 31 |           2 |              0
(1 row)

SELECT * FROM same($$set_id IN (1, 12) OR set_id = 6$$);
           plan            | result_rows | vs_normal_plan 
---------------------------+-------------+----------------
 SiftAgg read 37 pruned 25 |           3 |              0
(1 row)

-- an IN list with gaps travels with its zone test up to 65,536 members (the units of the owners between 1 and 12 are pruned, however long the list: the point directory
-- of the source needs the members of a long one)
SELECT * FROM same(format('set_id IN (1, 12, %s)', (SELECT string_agg(g::text, ', ') FROM generate_series(100, 120) g)));
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 8 pruned 29 |           2 |              0
(1 row)

SELECT * FROM same(format('set_id IN (1, 12, %s)', (SELECT string_agg(g::text, ', ') FROM generate_series(100, 400) g)));
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 8 pruned 29 |           2 |              0
(1 row)

-- an AND of ORs, and an OR of ANDs of two leaves: every conjunct, every disjunct is tested on its own
SELECT * FROM same($$(day < '2020-06-01' OR day > '2021-06-01') AND (set_id = 1 OR set_id = 12)$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 8 pruned 29 |           2 |              0
(1 row)

SELECT * FROM same($$(set_id = 1 AND day < '2020-02-01') OR (set_id = 12 AND day > '2021-03-01')$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 6 pruned 31 |           2 |              0
(1 row)

SELECT * FROM same($$(set_id = 1 OR set_id = 12) AND (day < '2020-02-01' OR day > '2021-03-01') AND amount > 50$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 6 pruned 31 |           2 |              0
(1 row)

-- the conjuncts of the query narrow every disjunct
SELECT * FROM same($$day >= '2020-03-01' AND (day <= '2020-03-31' OR day >= '2020-09-01' AND day <= '2020-09-30')$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 5 pruned 32 |           4 |              0
(1 row)

-- a disjunct no zone can refuse leaves every unit: a test of the text leaf, of another leaf that is no zone leaf, an arithmetic on the leaf
SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR kind = 'a'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 37 pruned 0 |          12 |              0
(1 row)

SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR amount > 98$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 37 pruned 0 |          12 |              0
(1 row)

SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR day + 1 > '2021-03-01'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 37 pruned 0 |           9 |              0
(1 row)

-- the windows come from parameters of a prepared statement, tested when it runs
PREPARE w(date, date, date, date) AS SELECT set_id, count(*) FROM ev WHERE day >= $1 AND day <= $2 OR day >= $3 AND day <= $4 GROUP BY set_id ORDER BY 1;
SET plan_cache_mode = force_generic_plan;
SELECT units_of($$EXECUTE w('2020-03-01', '2020-03-31', '2021-03-01', '2021-03-31')$$);
         units_of         
--------------------------
 SiftAgg read 4 pruned 33
(1 row)

EXECUTE w('2020-03-01', '2020-03-31', '2021-03-01', '2021-03-31');
 set_id | count 
--------+-------
      1 |  7000
      2 |  2300
      6 |  9300
(3 rows)

SET sift.enable_agg = off;
EXECUTE w('2020-03-01', '2020-03-31', '2021-03-01', '2021-03-31');
 set_id | count 
--------+-------
      1 |  7000
      2 |  2300
      6 |  9300
(3 rows)

RESET sift.enable_agg;
RESET plan_cache_mode;
-- rows written after the index was built are in the write buffer, and a row deleted from a unit stays out of the result
INSERT INTO ev SELECT 300000 + i, 13, date '2020-03-15', 'a', 1, 'x' FROM generate_series(1, 50) i;
DELETE FROM ev WHERE id BETWEEN 5000 AND 5100;
SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR day >= '2021-03-01' AND day <= '2021-03-31'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 5 pruned 33 |           4 |              0
(1 row)

-- rows moved by UPDATE leave a dead version in an old unit and a new one elsewhere: both sides of an OR see only the live row
UPDATE ev SET day = date '2021-03-10' WHERE id BETWEEN 100000 AND 100200;
UPDATE ev SET day = date '2020-01-02' WHERE day >= '2020-03-10' AND day <= '2020-03-12';
SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR day >= '2021-03-01' AND day <= '2021-03-31'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 6 pruned 33 |           6 |              0
(1 row)

SELECT * FROM same($$day >= '2020-01-01' AND day <= '2020-01-05' OR day = '2021-03-10'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 4 pruned 35 |           4 |              0
(1 row)

-- a NULL day matches no disjunct, and a disjunct of IS NULL leaves every unit
INSERT INTO ev SELECT 400000 + i, 14, NULL, 'b', 2, 'x' FROM generate_series(1, 30) i;
SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR day >= '2021-03-01' AND day <= '2021-03-31'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 6 pruned 33 |           6 |              0
(1 row)

SELECT * FROM same($$day >= '2020-03-01' AND day <= '2020-03-31' OR day IS NULL$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 4 pruned 35 |           4 |              0
(1 row)

-- units answered from the pre-aggregates (a grouped dictionary leaf, no test) and the OR that scans: the same rows
VACUUM (FREEZE, ANALYZE) ev;
SELECT units_of($$SELECT kind, count(*), sum(amount) FROM ev GROUP BY kind$$) ~ 'SiftAgg' AS grouped_by_siftagg;
 grouped_by_siftagg 
--------------------
 t
(1 row)

CREATE FUNCTION same_kind(cond text) RETURNS TABLE (plan text, result_rows bigint, vs_normal_plan bigint) LANGUAGE plpgsql AS $$
DECLARE q text := format('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE %s GROUP BY kind', cond);
BEGIN
	plan := units_of(q);
	EXECUTE format('CREATE TEMP TABLE k_on ON COMMIT DROP AS %s', q);
	SET LOCAL sift.enable_agg = off;
	EXECUTE format('CREATE TEMP TABLE k_off ON COMMIT DROP AS %s', q);
	RESET sift.enable_agg;
	result_rows := (SELECT count(*) FROM k_on);
	vs_normal_plan := (SELECT count(*) FROM ((SELECT * FROM k_on EXCEPT ALL SELECT * FROM k_off) UNION ALL (SELECT * FROM k_off EXCEPT ALL SELECT * FROM k_on)) d);
	RETURN NEXT;
END $$;
SELECT * FROM same_kind($$day >= '2020-03-01' AND day <= '2020-03-31' OR day >= '2021-03-01' AND day <= '2021-03-31'$$);
           plan           | result_rows | vs_normal_plan 
--------------------------+-------------+----------------
 SiftAgg read 6 pruned 33 |           4 |              0
(1 row)

SELECT * FROM same_kind($$set_id < 3 OR set_id > 11$$);
           plan            | result_rows | vs_normal_plan 
---------------------------+-------------+----------------
 SiftAgg read 13 pruned 26 |           4 |              0
(1 row)

SELECT * FROM same_kind($$(set_id < 3 OR set_id > 11) AND day >= '2020-01-01'$$);
           plan            | result_rows | vs_normal_plan 
---------------------------+-------------+----------------
 SiftAgg read 13 pruned 26 |           4 |              0
(1 row)

DROP FUNCTION same_kind(text), same(text), units_of(text);
DROP TABLE ev;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;