-- count(x) of a column of a joined table needs whether x is NULL, not the id of its value: the scan keeps a flag for each joined tuple, with no table of the values -- (a column with a value in every row made a table of the size of the lookup table's rows). The aggregates that do need the ids (array_agg, count(DISTINCT); not min, max and sum, which are added tuple by tuple) of such a -- column cost more than those of a column of a few values. The answers are those of the normal plan, the rows of the parents with no child and the NULLs included. count(x + y) has PostgreSQL evaluate the sum for each joined tuple, which costs more than count(x). CREATE EXTENSION pg_sift; SET max_parallel_workers_per_gather = 0; SET jit = off; CREATE TABLE parent (id int PRIMARY KEY, region int) WITH (autovacuum_enabled = false); INSERT INTO parent SELECT g, g % 11 FROM generate_series(1, 30000) g; CREATE TABLE child (id int PRIMARY KEY, parent_id int NOT NULL REFERENCES parent, v int, tag text) WITH (autovacuum_enabled = false); -- the parents 24001 to 30000 have no child; v is NULL in one row of seven, and has 1000 values INSERT INTO child SELECT g, 1 + (g::bigint * 7919) % 24000, CASE WHEN g % 7 = 0 THEN NULL ELSE g % 1000 END, CASE WHEN g % 5 = 0 THEN NULL ELSE 't' || g % 13 END FROM generate_series(1, 300000) g; CREATE INDEX parent_col ON parent USING sift_col (id, region); NOTICE: sift_col index "parent_col": 1 automatic choice (track_changes) DETAIL: track_changes = id HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('parent_col'); CREATE INDEX child_col ON child USING sift_col (id, parent_id, v, tag); NOTICE: sift_col index "child_col" connected to parent(id) through foreign key "child_parent_id_fkey" HINT: connect = 'off' on the index turns it off. NOTICE: sift_col index "child_col": 4 automatic choices DETAIL: key_columns = parent_id; key_cut = parent_id; order_array_columns = v; track_changes = id HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('child_col'); NOTICE: sift_col index "child_col" is column format minor 38, which a library before 3.7 cannot read HINT: SET sift.col_format_minor = 14 before the build to write a format the older library reads. VACUUM (FREEZE, ANALYZE) parent, child; CREATE TABLE shapes (n int, sql text); INSERT INTO shapes SELECT row_number() OVER (), format(q, a) FROM (VALUES ('count(c.id)'), ('count(c.v)'), ('count(c.tag)'), ('count(c.v + 1)'), ('count(c.tag) FILTER (WHERE c.v > 500)')) a (a), (VALUES ($q$SELECT p.region, %s FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1$q$), ($q$SELECT p.id, %s FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 10$q$), ($q$SELECT p.region, %s FROM child c JOIN parent p ON c.parent_id = p.id WHERE p.id > 100 GROUP BY 1$q$)) q (q); CREATE FUNCTION answer(sql text, force bool) RETURNS text LANGUAGE plpgsql AS $$ DECLARE r text; BEGIN PERFORM set_config('sift.agg_force', force::text, true); EXECUTE 'SELECT string_agg(t::text, '';'' ORDER BY t::text) FROM (' || sql || ') t' INTO r; RETURN r; END $$; CREATE FUNCTION plan(sql text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE l text; r text := 'normal'; BEGIN PERFORM set_config('sift.agg_force', 'true', true); FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || sql LOOP IF l ~ 'Custom Scan \(SiftAgg\)' THEN r := 'SiftAgg'; END IF; END LOOP; RETURN r; END $$; CREATE FUNCTION scan_cost(agg text) RETURNS float8 LANGUAGE sql AS $$ SELECT min(scan_cost) FROM sift.explain_why(format($q$SELECT p.id, %s FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 10$q$, agg)) WHERE arm = 'single' $$; -- the lookup join is what is priced: the child grouped by its key first (sift.agg_child_first) is another plan of these statements SET sift.agg_child_first = off; SELECT n, sql, plan(sql), answer(sql, true) = answer(sql, false) AS same_answers FROM shapes ORDER BY n; n | sql | plan | same_answers ----+-------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------- 1 | SELECT p.region, count(c.id) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 | SiftAgg | t 2 | SELECT p.id, count(c.id) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 10 | SiftAgg | t 3 | SELECT p.region, count(c.id) FROM child c JOIN parent p ON c.parent_id = p.id WHERE p.id > 100 GROUP BY 1 | SiftAgg | t 4 | SELECT p.region, count(c.v) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 | SiftAgg | t 5 | SELECT p.id, count(c.v) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 10 | SiftAgg | t 6 | SELECT p.region, count(c.v) FROM child c JOIN parent p ON c.parent_id = p.id WHERE p.id > 100 GROUP BY 1 | SiftAgg | t 7 | SELECT p.region, count(c.tag) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 | SiftAgg | t 8 | SELECT p.id, count(c.tag) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 10 | SiftAgg | t 9 | SELECT p.region, count(c.tag) FROM child c JOIN parent p ON c.parent_id = p.id WHERE p.id > 100 GROUP BY 1 | SiftAgg | t 10 | SELECT p.region, count(c.v + 1) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 | SiftAgg | t 11 | SELECT p.id, count(c.v + 1) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 10 | SiftAgg | t 12 | SELECT p.region, count(c.v + 1) FROM child c JOIN parent p ON c.parent_id = p.id WHERE p.id > 100 GROUP BY 1 | SiftAgg | t 13 | SELECT p.region, count(c.tag) FILTER (WHERE c.v > 500) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 | SiftAgg | t 14 | SELECT p.id, count(c.tag) FILTER (WHERE c.v > 500) FROM parent p LEFT JOIN child c ON c.parent_id = p.id GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 10 | SiftAgg | t 15 | SELECT p.region, count(c.tag) FILTER (WHERE c.v > 500) FROM child c JOIN parent p ON c.parent_id = p.id WHERE p.id > 100 GROUP BY 1 | SiftAgg | t (15 rows) -- the scan's price: a count needs no ids, and the ids of 300,000 values cost more than those of 1,000 SELECT scan_cost('count(DISTINCT c.id)') > 1.5 * scan_cost('count(DISTINCT c.v)') AS wide_arg_costs_more, scan_cost('count(c.id)') < scan_cost('array_agg(c.id)') / 2 AS count_costs_less, scan_cost('count(c.v + c.id)') > 1.5 * scan_cost('count(c.v)') AS expr_count_costs_more; wide_arg_costs_more | count_costs_less | expr_count_costs_more ---------------------+------------------+----------------------- t | t | t (1 row) DROP FUNCTION plan(text), answer(text, bool), scan_cost(text); DROP TABLE shapes, child, parent; DROP EXTENSION pg_sift;