-- The same HAVING statements under the conditions that change HOW the scan answers them, not what they ask: from the pre-aggregates of the units or by reading the rows -- (sift.agg_preagg), by one process or by workers, on a table that has taken inserts, updates and deletes since its index was built (the write buffer and the tombstones: a group -- crosses the HAVING bound because of a row that is not in a unit) and after the VACUUM that folds them in, and with parameters (a generic plan has $n in the condition of the -- groups and in the condition of the rows, a custom plan has the values). Each section runs the list and compares every row with sift off. `workers` is the number of workers the plan -- asks for. CREATE EXTENSION pg_sift; SET client_min_messages = warning; SET max_parallel_workers_per_gather = 0; \ir test/agg_having_helpers.sql \set ECHO none CREATE FUNCTION having_workers(q text) RETURNS int LANGUAGE sql AS $$ SELECT coalesce(substring(having_plan(q) from 'Workers Planned: (\d+)')::int, 0) $$; CREATE TABLE q (ord serial, name text, sql text); INSERT INTO q (name, sql) VALUES ('count', $q$SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING count(*) > 2000$q$), ('count at the boundary',$q$SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING count(*) BETWEEN 5 AND 8$q$), ('sum, NULL groups', $q$SELECT tenant_id, sum(counter_a) AS s FROM hv_events GROUP BY 1 HAVING sum(counter_a) IS NULL OR sum(counter_a) >= 5000$q$), ('avg', $q$SELECT tenant_id, avg(counter_a) AS a FROM hv_events GROUP BY 1 HAVING avg(counter_a) < 2$q$), ('max = min', $q$SELECT tenant_id, max(counter_a) AS mx FROM hv_events GROUP BY 1 HAVING max(counter_a) = min(counter_a)$q$), ('count distinct', $q$SELECT category_id, count(DISTINCT counter_a) AS d FROM hv_events GROUP BY 1 HAVING count(DISTINCT counter_a) > 3$q$), ('aggregates not selected', $q$SELECT kind FROM hv_events GROUP BY 1 HAVING max(event_date) > DATE '2026-01-29' AND min(value_num) < 1$q$), ('FILTER', $q$SELECT tenant_id, count(*) FILTER (WHERE status = 1) AS c FROM hv_events GROUP BY 1 HAVING count(*) FILTER (WHERE status = 1) > 700$q$), ('percentile', $q$SELECT category_id FROM hv_events GROUP BY 1 HAVING percentile_cont(0.5) WITHIN GROUP (ORDER BY counter_b) > 3.9$q$), ('a key and an aggregate', $q$SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING tenant_id > 8 AND count(*) > 2000$q$), ('a WHERE and a HAVING',$q$SELECT tenant_id, sum(counter_b) AS s FROM hv_events WHERE counter_b > 4 GROUP BY 1 HAVING sum(counter_b) > 5000$q$), ('a sub-select on the key', $q$SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING tenant_id IN (SELECT id FROM hv_tenants WHERE active) AND count(*) > 1000$q$), ('rollup and grouping()', $q$SELECT category_id, kind, count(*) AS c, grouping(category_id, kind) AS g FROM hv_events GROUP BY ROLLUP (category_id, kind) HAVING grouping(category_id, kind) < 3 AND count(*) > 1000$q$), ('a join to a lookup', $q$SELECT t.tier, count(*) AS c, sum(e.counter_a) AS s FROM hv_events e JOIN hv_tenants t ON t.id = e.tenant_id GROUP BY 1 HAVING sum(e.counter_a) > 1000$q$), ('no GROUP BY', $q$SELECT count(*) AS c, sum(counter_a) AS s FROM hv_events HAVING sum(counter_a) > 100$q$), ('top-N', $q$SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING count(*) > 5 ORDER BY c DESC, 1 LIMIT 4$q$); SELECT 'default' AS mode, q.name, having_workers(q.sql) AS workers, c.* FROM q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | workers | served | verdict | where_in_scan | having_on_groups | rows_same | n ---------+-------------------------+---------+--------+---------+---------------+------------------+-----------+--- default | count | 0 | t | sift | f | t | t | 5 default | count at the boundary | 0 | t | sift | f | t | t | 1 default | sum, NULL groups | 0 | t | sift | f | t | t | 5 default | avg | 0 | t | sift | f | t | t | 4 default | max = min | 0 | t | sift | f | t | t | 4 default | count distinct | 0 | t | sift | f | t | t | 7 default | aggregates not selected | 0 | t | sift | f | t | t | 5 default | FILTER | 0 | t | sift | f | t | t | 3 default | percentile | 0 | t | sift | f | t | t | 5 default | a key and an aggregate | 0 | t | sift | t | t | t | 3 default | a WHERE and a HAVING | 0 | t | sift | t | t | t | 6 default | a sub-select on the key | 0 | t | sift | f | t | t | 5 default | rollup and grouping() | 0 | t | sift | f | t | t | 6 default | a join to a lookup | 0 | t | sift | f | t | t | 3 default | no GROUP BY | 0 | t | sift | f | t | t | 1 default | top-N | 0 | t | sift | f | t | t | 4 (16 rows) SET sift.agg_preagg = off; SELECT 'rows, no pre-aggregates' AS mode, q.name, having_workers(q.sql) AS workers, c.* FROM q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | workers | served | verdict | where_in_scan | having_on_groups | rows_same | n -------------------------+-------------------------+---------+--------+---------+---------------+------------------+-----------+--- rows, no pre-aggregates | count | 0 | t | sift | f | t | t | 5 rows, no pre-aggregates | count at the boundary | 0 | t | sift | f | t | t | 1 rows, no pre-aggregates | sum, NULL groups | 0 | t | sift | f | t | t | 5 rows, no pre-aggregates | avg | 0 | t | sift | f | t | t | 4 rows, no pre-aggregates | max = min | 0 | t | sift | f | t | t | 4 rows, no pre-aggregates | count distinct | 0 | t | sift | f | t | t | 7 rows, no pre-aggregates | aggregates not selected | 0 | t | sift | f | t | t | 5 rows, no pre-aggregates | FILTER | 0 | t | sift | f | t | t | 3 rows, no pre-aggregates | percentile | 0 | t | sift | f | t | t | 5 rows, no pre-aggregates | a key and an aggregate | 0 | t | sift | t | t | t | 3 rows, no pre-aggregates | a WHERE and a HAVING | 0 | t | sift | t | t | t | 6 rows, no pre-aggregates | a sub-select on the key | 0 | t | sift | f | t | t | 5 rows, no pre-aggregates | rollup and grouping() | 0 | t | sift | f | t | t | 6 rows, no pre-aggregates | a join to a lookup | 0 | t | sift | f | t | t | 3 rows, no pre-aggregates | no GROUP BY | 0 | t | sift | f | t | t | 1 rows, no pre-aggregates | top-N | 0 | t | sift | f | t | t | 4 (16 rows) SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET min_parallel_table_scan_size = 0; SET min_parallel_index_scan_size = 0; SET max_parallel_workers_per_gather = 2; SELECT 'two workers' AS mode, q.name, having_workers(q.sql) AS workers, c.* FROM q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | workers | served | verdict | where_in_scan | having_on_groups | rows_same | n -------------+-------------------------+---------+--------+---------+---------------+------------------+-----------+--- two workers | count | 2 | t | sift | f | t | t | 5 two workers | count at the boundary | 2 | t | sift | f | t | t | 1 two workers | sum, NULL groups | 2 | t | sift | f | t | t | 5 two workers | avg | 2 | t | sift | f | t | t | 4 two workers | max = min | 2 | t | sift | f | t | t | 4 two workers | count distinct | 2 | t | sift | f | t | t | 7 two workers | aggregates not selected | 2 | t | sift | f | t | t | 5 two workers | FILTER | 2 | t | sift | f | t | t | 3 two workers | percentile | 2 | t | sift | f | t | t | 5 two workers | a key and an aggregate | 2 | t | sift | t | t | t | 3 two workers | a WHERE and a HAVING | 2 | t | sift | t | t | t | 6 two workers | a sub-select on the key | 2 | t | sift | f | t | t | 5 two workers | rollup and grouping() | 2 | t | sift | f | t | t | 6 two workers | a join to a lookup | 2 | t | sift | f | t | t | 3 two workers | no GROUP BY | 2 | t | sift | f | t | t | 1 two workers | top-N | 2 | t | sift | f | t | t | 4 (16 rows) SET max_parallel_workers_per_gather = 0; RESET sift.agg_preagg; -- inserts, updates and deletes that move groups across the bounds: 15 goes from 5 to 8 rows and gets a value, 16 from 1 to 7, 17 loses a row, 18 is new, the biggest group loses seven rows INSERT INTO hv_events SELECT 40000 + i, 15, 2, 'k2', DATE '2026-02-05', 1, 3, 5, 2.50, 2.5, 'l3', true FROM generate_series(1, 3) i; INSERT INTO hv_events SELECT 41000 + i, 16, 2, 'k2', DATE '2026-02-02', 1, 9, 9, 9.00, 9.0, 'l1', true FROM generate_series(1, 6) i; INSERT INTO hv_events VALUES (42000, 18, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); DELETE FROM hv_events WHERE id = 30007; DELETE FROM hv_events WHERE id IN (SELECT id FROM hv_events WHERE tenant_id = 1 ORDER BY id LIMIT 7); UPDATE hv_events SET tenant_id = 15, counter_a = 11 WHERE id IN (SELECT id FROM hv_events WHERE tenant_id IS NULL ORDER BY id LIMIT 4); UPDATE hv_events SET counter_b = counter_b + 1 WHERE tenant_id = 13 AND id % 2 = 0; SELECT 'after writes' AS mode, q.name, having_workers(q.sql) AS workers, c.* FROM q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | workers | served | verdict | where_in_scan | having_on_groups | rows_same | n --------------+-------------------------+---------+--------+---------+---------------+------------------+-----------+--- after writes | count | 0 | t | sift | f | t | t | 5 after writes | count at the boundary | 0 | t | sift | f | t | t | 1 after writes | sum, NULL groups | 0 | t | sift | f | t | t | 5 after writes | avg | 0 | t | sift | f | t | t | 4 after writes | max = min | 0 | t | sift | f | t | t | 3 after writes | count distinct | 0 | t | sift | f | t | t | 7 after writes | aggregates not selected | 0 | t | sift | f | t | t | 5 after writes | FILTER | 0 | t | sift | f | t | t | 3 after writes | percentile | 0 | t | sift | f | t | t | 5 after writes | a key and an aggregate | 0 | t | sift | t | t | t | 3 after writes | a WHERE and a HAVING | 0 | t | sift | t | t | t | 6 after writes | a sub-select on the key | 0 | t | sift | f | t | t | 5 after writes | rollup and grouping() | 0 | t | sift | f | t | t | 6 after writes | a join to a lookup | 0 | t | sift | f | t | t | 3 after writes | no GROUP BY | 0 | t | sift | f | t | t | 1 after writes | top-N | 0 | t | sift | f | t | t | 4 (16 rows) SET sift.agg_preagg = off; SET max_parallel_workers_per_gather = 2; SELECT 'after writes, two workers, rows' AS mode, q.name, having_workers(q.sql) AS workers, c.* FROM q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | workers | served | verdict | where_in_scan | having_on_groups | rows_same | n ---------------------------------+-------------------------+---------+--------+---------+---------------+------------------+-----------+--- after writes, two workers, rows | count | 2 | t | sift | f | t | t | 5 after writes, two workers, rows | count at the boundary | 2 | t | sift | f | t | t | 1 after writes, two workers, rows | sum, NULL groups | 2 | t | sift | f | t | t | 5 after writes, two workers, rows | avg | 2 | t | sift | f | t | t | 4 after writes, two workers, rows | max = min | 2 | t | sift | f | t | t | 3 after writes, two workers, rows | count distinct | 2 | t | sift | f | t | t | 7 after writes, two workers, rows | aggregates not selected | 2 | t | sift | f | t | t | 5 after writes, two workers, rows | FILTER | 2 | t | sift | f | t | t | 3 after writes, two workers, rows | percentile | 2 | t | sift | f | t | t | 5 after writes, two workers, rows | a key and an aggregate | 2 | t | sift | t | t | t | 3 after writes, two workers, rows | a WHERE and a HAVING | 2 | t | sift | t | t | t | 6 after writes, two workers, rows | a sub-select on the key | 2 | t | sift | f | t | t | 5 after writes, two workers, rows | rollup and grouping() | 2 | t | sift | f | t | t | 6 after writes, two workers, rows | a join to a lookup | 2 | t | sift | f | t | t | 3 after writes, two workers, rows | no GROUP BY | 2 | t | sift | f | t | t | 1 after writes, two workers, rows | top-N | 2 | t | sift | f | t | t | 4 (16 rows) SET max_parallel_workers_per_gather = 0; RESET sift.agg_preagg; VACUUM (ANALYZE) hv_events; SELECT 'after VACUUM' AS mode, q.name, having_workers(q.sql) AS workers, c.* FROM q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | workers | served | verdict | where_in_scan | having_on_groups | rows_same | n --------------+-------------------------+---------+--------+---------+---------------+------------------+-----------+--- after VACUUM | count | 0 | t | sift | f | t | t | 5 after VACUUM | count at the boundary | 0 | t | sift | f | t | t | 1 after VACUUM | sum, NULL groups | 0 | t | sift | f | t | t | 5 after VACUUM | avg | 0 | t | sift | f | t | t | 4 after VACUUM | max = min | 0 | t | sift | f | t | t | 3 after VACUUM | count distinct | 0 | t | sift | f | t | t | 7 after VACUUM | aggregates not selected | 0 | t | sift | f | t | t | 5 after VACUUM | FILTER | 0 | t | sift | f | t | t | 3 after VACUUM | percentile | 0 | t | sift | f | t | t | 5 after VACUUM | a key and an aggregate | 0 | t | sift | t | t | t | 3 after VACUUM | a WHERE and a HAVING | 0 | t | sift | t | t | t | 6 after VACUUM | a sub-select on the key | 0 | t | sift | f | t | t | 5 after VACUUM | rollup and grouping() | 0 | t | sift | f | t | t | 6 after VACUUM | a join to a lookup | 0 | t | sift | f | t | t | 3 after VACUUM | no GROUP BY | 0 | t | sift | f | t | t | 1 after VACUUM | top-N | 0 | t | sift | f | t | t | 4 (16 rows) -- parameters: a generic plan keeps $n in the HAVING (groups) and in the WHERE (rows), and in the sub-select; two calls for each, one that keeps groups and one that keeps none PREPARE h_count(int, bigint) AS SELECT tenant_id, count(*) AS c FROM hv_events WHERE status = $1 GROUP BY 1 HAVING count(*) > $2; PREPARE h_key_sum(int, numeric) AS SELECT tenant_id, sum(counter_b) AS s FROM hv_events GROUP BY 1 HAVING tenant_id > $1 AND sum(counter_b) > $2; PREPARE h_text(text, bigint) AS SELECT kind, count(*) AS c FROM hv_events GROUP BY 1 HAVING kind <> $1 AND count(*) > $2; PREPARE h_date(date, int) AS SELECT kind, max(event_date) AS d FROM hv_events GROUP BY 1 HAVING max(event_date) > $1 AND min(counter_b) = $2; PREPARE h_sub(smallint, bigint) AS SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING tenant_id IN (SELECT id FROM hv_tenants WHERE tier = $1) AND count(*) > $2; PREPARE h_filter(int, bigint) AS SELECT tenant_id, count(*) FILTER (WHERE status = $1) AS c FROM hv_events GROUP BY 1 HAVING count(*) FILTER (WHERE status = $1) > $2; PREPARE h_array(int[]) AS SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING count(*) = ANY ($1); PREPARE h_none(bigint) AS SELECT count(*) AS c, sum(counter_a) AS s FROM hv_events HAVING count(*) > $1; PREPARE h_topn(bigint, bigint) AS SELECT tenant_id, count(*) AS c FROM hv_events GROUP BY 1 HAVING count(*) > $1 ORDER BY c DESC, 1 LIMIT $2; CREATE TABLE qp (ord serial, name text, sql text); INSERT INTO qp (name, sql) VALUES ('count, rows kept', 'EXECUTE h_count(1, 700)'), ('count, none kept', 'EXECUTE h_count(1, 99999)'), ('key and sum, kept', 'EXECUTE h_key_sum(8, 5000)'), ('key and sum, none kept', 'EXECUTE h_key_sum(100, 5000)'), ('text key, kept', 'EXECUTE h_text(''k1'', 1000)'), ('text key, NULL parameter','EXECUTE h_text(NULL, 1000)'), ('max date and min', 'EXECUTE h_date(DATE ''2026-01-29'', 0)'), ('sub-select, kept', 'EXECUTE h_sub(1::smallint, 100)'), ('sub-select, none kept', 'EXECUTE h_sub(9::smallint, 100)'), ('FILTER, kept', 'EXECUTE h_filter(1, 700)'), ('FILTER, none kept', 'EXECUTE h_filter(1, 99999)'), ('ANY of an array', 'EXECUTE h_array(ARRAY[5, 8, 990, 2969])'), ('ANY of an empty array', 'EXECUTE h_array(ARRAY[]::int[])'), ('no GROUP BY, kept', 'EXECUTE h_none(100)'), ('no GROUP BY, dropped', 'EXECUTE h_none(99999)'), ('top-N', 'EXECUTE h_topn(5, 3)'); SET plan_cache_mode = force_generic_plan; SELECT 'generic plan' AS mode, q.name, c.* FROM qp q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | served | verdict | where_in_scan | having_on_groups | rows_same | n --------------+--------------------------+--------+---------+---------------+------------------+-----------+--- generic plan | count, rows kept | t | sift | t | t | t | 3 generic plan | count, none kept | t | sift | t | t | t | 0 generic plan | key and sum, kept | t | sift | t | t | t | 4 generic plan | key and sum, none kept | t | sift | t | t | t | 0 generic plan | text key, kept | t | sift | t | t | t | 4 generic plan | text key, NULL parameter | t | sift | t | t | t | 0 generic plan | max date and min | t | sift | f | t | t | 5 generic plan | sub-select, kept | t | sift | f | t | t | 4 generic plan | sub-select, none kept | t | sift | f | t | t | 0 generic plan | FILTER, kept | t | sift | f | t | t | 3 generic plan | FILTER, none kept | t | sift | f | t | t | 0 generic plan | ANY of an array | t | sift | f | t | t | 5 generic plan | ANY of an empty array | t | sift | f | t | t | 0 generic plan | no GROUP BY, kept | t | sift | f | t | t | 1 generic plan | no GROUP BY, dropped | t | sift | f | t | t | 0 generic plan | top-N | t | sift | f | t | t | 3 (16 rows) SET plan_cache_mode = force_custom_plan; SELECT 'custom plan' AS mode, q.name, c.* FROM qp q, LATERAL having_run(q.sql, q.name = 'top-N') c ORDER BY q.ord; mode | name | served | verdict | where_in_scan | having_on_groups | rows_same | n -------------+--------------------------+--------+-------------+---------------+------------------+-----------+--- custom plan | count, rows kept | t | sift | t | t | t | 3 custom plan | count, none kept | t | sift | t | t | t | 0 custom plan | key and sum, kept | t | sift | t | t | t | 4 custom plan | key and sum, none kept | t | sift | t | t | t | 0 custom plan | text key, kept | t | sift | t | t | t | 4 custom plan | text key, NULL parameter | f | single:cost | | | t | 0 custom plan | max date and min | t | sift | f | t | t | 5 custom plan | sub-select, kept | t | sift | f | t | t | 4 custom plan | sub-select, none kept | t | sift | f | t | t | 0 custom plan | FILTER, kept | t | sift | f | t | t | 3 custom plan | FILTER, none kept | t | sift | f | t | t | 0 custom plan | ANY of an array | t | sift | f | t | t | 5 custom plan | ANY of an empty array | t | sift | f | t | t | 0 custom plan | no GROUP BY, kept | t | sift | f | t | t | 1 custom plan | no GROUP BY, dropped | t | sift | f | t | t | 0 custom plan | top-N | t | sift | f | t | t | 3 (16 rows) RESET plan_cache_mode; DEALLOCATE ALL; DROP TABLE q, qp, hv_events, hv_tenants, hv_empty; DROP FUNCTION having_workers(text), having_plan(text), having_rows(text, bool, bool), having_run(text, bool), having_same(text, text, text); RESET parallel_setup_cost; RESET parallel_tuple_cost; RESET min_parallel_table_scan_size; RESET min_parallel_index_scan_size; DROP EXTENSION pg_sift;