-- A lookup table loaded from the leaves of a sift_col index reads a text column whose dictionary the segment keeps in blocks (many entries, not loaded by the backend)
-- through the string of the entry, not through the arrays of the whole dictionary: the rows are those of the normal plan, with NULLs and repeated strings.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
CREATE EXTENSION pg_sift_helpers;
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;

CREATE TABLE ev (id int, k int, amount int) WITH (autovacuum_enabled = false);
INSERT INTO ev SELECT i, i % 120000, i % 13 FROM generate_series(1, 240000) i;
CREATE INDEX ev_col ON ev USING sift_col (id, k, amount) WITH (point_tid_columns = 'off');
CREATE TABLE dm (k int PRIMARY KEY, county text, kind text) WITH (autovacuum_enabled = false);
INSERT INTO dm SELECT i, CASE WHEN i % 23 = 0 THEN NULL ELSE 'c' || md5((i % 20000)::text) END, 'kind' || i % 4 FROM generate_series(0, 119999) i;
CREATE INDEX dm_col ON dm USING sift_col (k, county, kind);
CREATE TABLE dm2 (LIKE dm INCLUDING ALL) WITH (autovacuum_enabled = false);
INSERT INTO dm2 SELECT * FROM dm;
CREATE INDEX dm2_col ON dm2 USING sift_col (k, county, kind);
VACUUM (FREEZE, ANALYZE) ev, dm, dm2;

CREATE FUNCTION same(q text) RETURNS TABLE (result_rows bigint, vs_normal_plan bigint) LANGUAGE sql AS $$ SELECT * FROM sift_helpers.agg_rows_and_differences(q) $$;

-- the first read of a dictionary is a test of the table's own condition
SELECT * FROM same('SELECT kind, sum(amount), count(*) FROM ev, dm2 WHERE ev.k = dm2.k AND county = ''c'' || md5(''17'') GROUP BY kind');
SELECT * FROM same('SELECT county, sum(amount), count(*) FROM ev, dm WHERE ev.k = dm.k GROUP BY county');
SELECT * FROM same('SELECT county, kind, sum(amount) FROM ev, dm WHERE ev.k = dm.k GROUP BY county, kind');
SELECT * FROM same('SELECT kind, count(DISTINCT county) FROM ev, dm WHERE ev.k = dm.k GROUP BY kind');
SELECT * FROM same('SELECT count(*) FROM ev, dm WHERE ev.k = dm.k AND county = ''c'' || md5(''17'')');

DROP FUNCTION same(text);
DROP TABLE ev, dm, dm2;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;