-- The joined table of a lookup join of Custom Scan (SiftAgg) is read through a btree for its own `column IN (list)` conditions as for `column = value` -- (sift.agg_lookup_pushdown): a list that keeps a small share of the table is an index scan, a list that keeps a large share, NOT IN and a table without -- an index are read by the heap scan, with the same rows. Every statement is run with the pushdown, without it and with sift.enable_agg = off. 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; SET seq_page_cost = 10000; SET random_page_cost = 10000; -- the statements check the lookup tables the scan loads (the plans with probes have their own test, agg_probe_rewrite) SET sift.agg_probe_rewrite = off; -- the ordered index-only scan of the key joined by a memoized lookup costs few pages and would take the plan over from the pushed one at these page costs: the statements check the pushed plan SET sift.enable_col_scan = off; 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; -- 100 countries (a list of a few keeps a few percent of the table), a city name that repeats, a tier, and the same rows without an index CREATE TABLE dims (id int PRIMARY KEY, country int, tier int2, city text, active boolean); INSERT INTO dims SELECT i, i % 100, (i % 5)::int2, 'c' || i % 700, i % 7 <> 0 FROM generate_series(1, 30000) i; INSERT INTO dims VALUES (30001, NULL, 1, NULL, true), (30002, 5, 1, 'c5', true); CREATE INDEX dims_country_tier ON dims (country, tier); CREATE INDEX dims_city ON dims (city); CREATE TABLE dims_bare (id int, country int, tier int2); INSERT INTO dims_bare SELECT id, country, tier FROM dims; ANALYZE dims; ANALYZE dims_bare; -- 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); DROP TABLE r_on, r_no, r_off; RETURN NEXT; END $$; -- a short list of an indexed column: an inner join that returns a column, a semi join, an EXISTS, ANY of an array SELECT * FROM same('SELECT d.country, count(*) AS n, sum(amount) AS s FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (3, 17, 41) GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,900 rows read,900 rows loaded,1 index scans | 3 | 0 | 0 (1 row) SELECT * FROM same('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id IN (SELECT id FROM dims WHERE country IN (3, 17, 41)) GROUP BY kind'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,900 rows read,900 rows loaded,1 index scans | 2 | 0 | 0 (1 row) SELECT * FROM same('SELECT kind, count(*) AS n FROM ev WHERE EXISTS (SELECT 1 FROM dims d WHERE d.id = ev.dim_a_id AND d.country = ANY (ARRAY[3, 17, 41])) GROUP BY kind'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,900 rows read,900 rows loaded,1 index scans | 2 | 0 | 0 (1 row) -- a text list, the duplicates and a NULL of a list, values no row has, one value SELECT * FROM same('SELECT d.city, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.city IN (''c5'', ''c6'', ''c699'') GROUP BY d.city'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,129 rows read,129 rows loaded,1 index scans | 3 | 0 | 0 (1 row) SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (3, 3, NULL, 17) GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,600 rows read,600 rows loaded,1 index scans | 2 | 0 | 0 (1 row) SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (500, 501) GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -------------------------------------------------+-------------+----------------+---------------- SiftAgg,0 rows read,0 rows loaded,1 index scans | 0 | 0 | 0 (1 row) SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (5) GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,301 rows read,301 rows loaded,1 index scans | 1 | 0 | 0 (1 row) -- the index (country, tier): a list on the first column and a value on the second, and a condition that is not a key SELECT * FROM same('SELECT d.country, d.tier, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (3, 17, 41) AND d.tier = 2 GROUP BY 1, 2'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,300 rows read,300 rows loaded,1 index scans | 1 | 0 | 0 (1 row) SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (3, 17, 41) AND d.active GROUP BY 1'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,900 rows read,771 rows loaded,1 index scans | 3 | 0 | 0 (1 row) SELECT * FROM same('SELECT d.tier, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.tier IN (1, 2) AND d.country IN (3, 17) GROUP BY 1'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,600 rows read,300 rows loaded,1 index scans | 1 | 0 | 0 (1 row) -- a LEFT join keeps the events without a match; the list is a condition of the ON clause SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev LEFT JOIN dims d ON d.id = ev.dim_a_id AND d.country IN (3, 17, 41) GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,900 rows read,900 rows loaded,1 index scans | 4 | 0 | 0 (1 row) -- a list that keeps half of the table is read by the heap scan, as a table with no index and NOT IN are SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49) GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -------------------------------------------+-------------+----------------+---------------- SiftAgg,30002 rows read,15001 rows loaded | 50 | 0 | 0 (1 row) SELECT * FROM same('SELECT b.country, count(*) AS n FROM ev JOIN dims_bare b ON b.id = ev.dim_a_id WHERE b.country IN (3, 17, 41) GROUP BY b.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------+-------------+----------------+---------------- SiftAgg,30002 rows read,900 rows loaded | 3 | 0 | 0 (1 row) SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country NOT IN (3, 17, 41) AND d.tier <> 4 GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -------------------------------------------+-------------+----------------+---------------- SiftAgg,30002 rows read,23101 rows loaded | 77 | 0 | 0 (1 row) SELECT * FROM same('SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country IN (3, 17, 41) AND ev.day >= ''2025-01-01'' GROUP BY d.country'); plan | result_rows | vs_no_pushdown | vs_normal_plan -----------------------------------------------------+-------------+----------------+---------------- SiftAgg,900 rows read,900 rows loaded,1 index scans | 3 | 0 | 0 (1 row) -- a list that is a parameter: the generic plan reads the array when the scan starts SET plan_cache_mode = force_generic_plan; PREPARE by_countries(int[]) AS SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country = ANY ($1) GROUP BY d.country ORDER BY 1; EXECUTE by_countries(ARRAY[3, 17, 41]); country | n ---------+------ 3 | 2997 17 | 2997 41 | 2997 (3 rows) EXECUTE by_countries(ARRAY[3, NULL]); country | n ---------+------ 3 | 2997 (1 row) EXECUTE by_countries('{}'); country | n ---------+--- (0 rows) EXECUTE by_countries(NULL); country | n ---------+--- (0 rows) -- a cached generic plan does not follow the setting: prepare it again with the pushdown off SET sift.enable_agg = off; DEALLOCATE by_countries; PREPARE by_countries(int[]) AS SELECT d.country, count(*) AS n FROM ev JOIN dims d ON d.id = ev.dim_a_id WHERE d.country = ANY ($1) GROUP BY d.country ORDER BY 1; EXECUTE by_countries(ARRAY[3, 17, 41]); country | n ---------+------ 3 | 2997 17 | 2997 41 | 2997 (3 rows) RESET sift.enable_agg; RESET plan_cache_mode; DEALLOCATE ALL; DROP FUNCTION same(text); DROP FUNCTION plan_of(text); DROP TABLE ev, dims, dims_bare; DROP EXTENSION pg_sift_helpers; DROP EXTENSION pg_sift;