-- The joined table of a lookup join of Custom Scan (SiftAgg) is loaded for the scan: the rows its own conditions (group_id = 3, country = 5) cannot match are
-- not read (a btree on the leading columns) or not kept (the filter runs while loading), sift.agg_lookup_pushdown (docs/SET-FILTERS.md). Every statement is
-- run with the pushdown, without it, and with sift.enable_agg = off (the normal plan): the rows must be the same.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET default_statistics_target = 10000;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;

CREATE TABLE ev (id int PRIMARY KEY, dim_a_id int, day date, kind text, amount numeric(10, 2)) WITH (autovacuum_enabled = false);
INSERT INTO ev
SELECT i, CASE WHEN i % 997 = 0 THEN NULL ELSE 1 + (i::bigint * 7919) % 30000 END, date '2024-01-01' + i % 700,
       (ARRAY['a', 'b', 'c', 'd'])[1 + i % 4], (i % 1000) / 10.0
FROM generate_series(1, 300000) i;
CREATE INDEX ev_col ON ev USING sift_col (dim_a_id, day, kind, amount);
VACUUM (FREEZE, ANALYZE) ev;

CREATE TABLE dim_b (group_id int, dim_a_id int, deleted_at timestamptz, role int);
INSERT INTO dim_b SELECT g, 1 + (g * 1000 + i * 37) % 30000, CASE WHEN i % 10 = 0 THEN now() END, i % 3
FROM generate_series(1, 40) g, generate_series(1, 500) i;
INSERT INTO dim_b VALUES (3, NULL, NULL, 0), (3, 11, NULL, 0), (3, 11, NULL, 1);
CREATE INDEX members_group ON dim_b (group_id, role, dim_a_id);
CREATE TABLE members_bare (group_id int, dim_a_id int, deleted_at timestamptz);
INSERT INTO members_bare SELECT group_id, dim_a_id, deleted_at FROM dim_b;
CREATE TABLE dims (id int PRIMARY KEY, country int, tier int2, active boolean);
INSERT INTO dims SELECT i, i % 60, (i % 5)::int2, i % 7 <> 0 FROM generate_series(1, 30000) i;
CREATE INDEX dims_country ON dims (country);
ANALYZE dim_b; ANALYZE members_bare; ANALYZE dims;

-- whether the plan is the pushed one, and what the load of the lookup tables read, kept and used
CREATE FUNCTION plan_of(q text) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.plan_siftagg_with_lookup_counts(q) $$;

CREATE FUNCTION same(q text) RETURNS TABLE (plan text, result_rows bigint, vs_no_pushdown bigint, vs_normal_plan bigint) LANGUAGE plpgsql AS $$
BEGIN
	plan := plan_of(q);
	EXECUTE format('CREATE TEMP TABLE r_on ON COMMIT DROP AS %s', q);
	SET LOCAL sift.agg_lookup_pushdown = off;
	EXECUTE format('CREATE TEMP TABLE r_no ON COMMIT DROP AS %s', q);
	RESET sift.agg_lookup_pushdown;
	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_no_pushdown := (SELECT count(*) FROM ((TABLE r_on EXCEPT TABLE r_no) UNION ALL (TABLE r_no EXCEPT TABLE r_on)) d);
	vs_normal_plan := (SELECT count(*) FROM ((TABLE r_on EXCEPT TABLE r_off) UNION ALL (TABLE r_off EXCEPT TABLE r_on)) d);
	RETURN NEXT;
END $$;

-- a group of an indexed membership table: the btree gives its rows; the same group through a table without index is filtered while loading
SELECT * FROM same('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM dim_b WHERE group_id = 7) GROUP BY kind');
SELECT * FROM same('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM dim_b WHERE group_id = 7 AND deleted_at IS NULL) GROUP BY kind');
SELECT * FROM same('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM dim_b WHERE group_id = 7 AND role = 1) GROUP BY kind');
SELECT * FROM same('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM members_bare WHERE group_id = 7 AND deleted_at IS NULL) GROUP BY kind');
SELECT * FROM same('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE EXISTS (SELECT 1 FROM dim_b m WHERE m.dim_a_id = ev.dim_a_id AND m.group_id = 7) GROUP BY kind');
SELECT * FROM same('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE NOT EXISTS (SELECT 1 FROM dim_b m WHERE m.dim_a_id = ev.dim_a_id AND m.group_id = 7) GROUP BY kind');
-- a group with a NULL key and a duplicate, two groups (a list on the leading column is an index scan too), a group with no rows
SELECT * FROM same('SELECT kind, count(*) AS n FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM dim_b WHERE group_id = 3) GROUP BY kind');
SELECT * FROM same('SELECT kind, count(*) AS n FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM dim_b WHERE group_id IN (3, 4)) GROUP BY kind');
SELECT * FROM same('SELECT kind, count(*) AS n FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM dim_b WHERE group_id = 999) GROUP BY kind');
-- a value join on an attribute table
SELECT * FROM same('SELECT d.tier, count(*) AS n, sum(amount) AS s FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country = 5 GROUP BY d.tier ORDER BY 1');
SELECT * FROM same('SELECT d.tier, count(*) AS n, sum(amount) AS s FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country = 5 AND d.active GROUP BY d.tier ORDER BY 1');
-- no index on the condition: the filter runs while the table loads, and the answer is the normal plan's
SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.tier = 2 GROUP BY d.country ORDER BY 1');

-- a table read whole (no condition of its own) is loaded to the rows the statistics give, its integer keys take the slots of the key dictionary by blocks of sixteen, and a
-- by-reference column value is found where it is and copied when new: keys that follow each other, are multiples of sixteen, negative or wider than int4, values that are short
-- (a one byte header), long, compressed, empty and NULL
CREATE TABLE lk_dim (k bigint, label text, tag varchar(40), note text) WITH (autovacuum_enabled = false);
INSERT INTO lk_dim SELECT k, CASE WHEN k % 17 = 0 THEN NULL ELSE 'label ' || k % 23 END, CASE WHEN k % 5 = 0 THEN '' ELSE 'tag' || k % 4 END,
       CASE k % 4 WHEN 0 THEN 'x' WHEN 1 THEN repeat('long value of one hundred and thirty bytes, so that its header is four bytes ... ', 2) WHEN 2 THEN repeat('compressible ', 400) ELSE NULL END
FROM (SELECT g AS k FROM generate_series(1, 6000) g
      UNION ALL SELECT g * 16 + 100000 FROM generate_series(1, 4000) g
      UNION ALL SELECT -g * 3 FROM generate_series(1, 2000) g
      UNION ALL SELECT 5000000000 + g FROM generate_series(1, 500) g
      UNION ALL SELECT 9223372036854775807 - g FROM generate_series(1, 300) g) q;
ANALYZE lk_dim;
CREATE TABLE lk_fact (id int, k bigint, amount numeric(10, 2)) WITH (autovacuum_enabled = false);
INSERT INTO lk_fact SELECT i, (ARRAY[1, 17, 16, 2000, 100016, 164000, -3, -6000, 5000000001, 5000000500, 9223372036854775800, 0, 99999999])[1 + i % 13] + i % 5, (i % 1000) / 10.0
FROM generate_series(1, 60000) i;
CREATE INDEX lk_fact_col ON lk_fact USING sift_col (k, amount);
VACUUM (FREEZE, ANALYZE) lk_fact;
SELECT * FROM same('SELECT d.label, d.tag, count(*) AS n, sum(amount) AS s FROM lk_fact f JOIN lk_dim d ON d.k = f.k GROUP BY d.label, d.tag ORDER BY 1, 2');
SELECT * FROM same('SELECT d.note, count(*) AS n, sum(amount) AS s FROM lk_fact f JOIN lk_dim d ON d.k = f.k GROUP BY d.note ORDER BY 1');
SELECT * FROM same('SELECT length(d.note) AS len, d.tag, count(*) AS n FROM lk_fact f LEFT JOIN lk_dim d ON d.k = f.k GROUP BY 1, 2 ORDER BY 1, 2');
DROP TABLE lk_dim, lk_fact;

-- the value of the condition is a parameter, NULL included (no row matches NULL), and a generic plan
SET plan_cache_mode = force_generic_plan;
PREPARE by_group(int) AS SELECT kind, count(*) AS n FROM ev WHERE dim_a_id IN (SELECT dim_a_id FROM dim_b WHERE group_id = $1) GROUP BY kind ORDER BY kind;
EXECUTE by_group(7);
EXECUTE by_group(NULL);
EXECUTE by_group(999);
SET sift.enable_agg = off;
EXECUTE by_group(7);
RESET sift.enable_agg;
RESET plan_cache_mode;

DEALLOCATE ALL;
DROP FUNCTION same(text);
DROP FUNCTION plan_of(text);
DROP TABLE ev, dim_b, members_bare, dims;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;