Aggregate pushdown

How Custom Scan (SiftAgg) handles lookup joins, partitions, partial indexes, sub-selects, ctid ranges and distinct counts, and how the differential suite checks it.

Joins to plans

The right side of a lookup join may be a table or a plan: a materialized CTE, a VALUES list, a function (generate_series), or a sub-select PostgreSQL keeps (GROUP BY, LIMIT, DISTINCT, a set operation, a ranked scan with LIMIT) in an inner, LEFT, IN or NOT EXISTS join. UNION [ALL] of sub-selects, a statement-level CTE’s sub-select and a nested WITH count too, and so does the WITH of a derived table (a ranked top-N over an aggregate) while the derived table reads nothing of the queries around it; a CTE of it that several aggregates read is planned inline like the statement’s own (the cheaper plan wins). A lateral sub-select that reads the row around it keeps the normal plan. Test agg_nested_ctes, fixtures nf_nested_cte_*, row J32.

PostgreSQL’s plan of the right side is the child of the node (EXPLAIN: Lookup Joins: s (subquery)). The scan runs it once at start with the query’s snapshot; its rows are the lookup table, so duplicate keys multiply rows and a NULL key matches nothing, as in the normal plan (EXPLAIN ANALYZE: Lookup Rows Read). A rescan replays the groups; prepared-statement parameters are read by the child plan as usual.

The normal plan stays when the right side reads a volatile function, the join has no condition (ON true) and is not lateral, the right side is estimated above sift.agg_lookup_max_kb (rows times width; for a lateral one, its rows per distinct input of the scanned table, together), or its plan (an Append) outputs only the relation’s columns and a condition reads another. A scan with such a join runs without workers.

Lateral right sides that read the joined row (LEFT JOIN LATERAL (SELECT .. LIMIT 1), UNION [ALL] arms, generate_series, unnest of an array column, a set-returning function) keep PostgreSQL’s parameterized plan as the child. The scan runs it once per distinct value of the columns it reads (lookup keys of the table or another joined table’s columns) and multiplies rows by what it returns, duplicates and NULL-extended rows included.

  • A call on a document column that cannot raise (jsonb_exists(doc, 'k'), doc ? 'k', doc ->> 'k', jsonb_typeof(doc)) passes its value as the parameter if the index holds it as an expression column spelled like the query’s. jsonb_exists(doc, 'k') gives key presence for any JSON root (a path leaf cannot tell a missing key from a JSON null).
  • The scan runs the child for the tuples that reach its join, PostgreSQL’s plan in another order. A child that can raise (a division, a text cast, a sub-select) is therefore joined only when the query joins nothing else (the scanned table’s conditions come first in both plans); else the normal plan stays, as for a child that reads the whole document or a column of a query above the aggregate’s own (a sub-select of an outer query: its value changes per row of that query).
  • A jsonb_each member r.value reaches the child as the jsonb its stored float stands for (the number, or a JSON null when absent), so CAST(r.value AS integer) works; the cast runs only for members of rows that reach the child and raises PostgreSQL’s own error (integer out of range). A document the index cannot hold as numbers (a string or object member) keeps the normal plan.
  • A path declared float8 carries no scale: a child reading the member otherwise (a cast to numeric or boolean, r.value itself, as text) would see 12.5 for 12.50, so it keeps the normal plan (test agg_child_member_value).
  • A lateral sub-select PostgreSQL does not pull up because its query is on the nullable side of an outer join (dates LEFT JOIN cte; below RIGHT or FULL the normal plan stays) is pulled up within its own query level by the planner hook (agg/flat/), which plans both ways and keeps the flat plan when it runs in the scan and costs less. A sub-select with a join of its own attaches through its leftmost table, as (a JOIN jsonb_each(a.doc) r) LEFT JOIN m ON ...
  • A GROUP BY over a UNION ALL of selects (FROM (SELECT t0 AS ts, 1 AS q FROM events WHERE .. UNION ALL SELECT t1, -1 FROM events WHERE ..) ev GROUP BY date_bin(..., ev.ts, ...)) is planned both ways like a pulled-up lateral: each arm grouped by the keys with the partial aggregates of its groups (count, sum of smallint, integer, bigint and numeric, min, max), added up above the union, and the plan with the arms that run in the scan grouped and the others as written when it costs less (agg/flat/pushagg.c). avg, DISTINCT, FILTER, an ordered aggregate, a float sum, UNION without ALL, an arm with LIMIT, ORDER BY, DISTINCT or an aggregate of its own, and a join next to the union keep the normal plan (test agg_union_partial_agg).

LEFT JOIN LATERAL jsonb_each(doc) r ON r.key = 'k' with a constant key is member k of each row (one member per key; a row without it stays, with NULL columns). The scan reads it at row level as (doc ->> 'k')::float8 and unnests nothing. A correlated child reads r.value only through casts of jsonb to an integer or float (a missing member and a JSON null are the same NULL; a JSON null casts to NULL in PostgreSQL 18); before PostgreSQL 18, with another condition, or with r.key read, the normal plan stays.

LEFT JOIN LATERAL over jsonb_to_recordset or jsonb_array_elements of a list of the scanned row, ON true or with its own conditions (sub-select WHERE, ON), is answered too: the conditions say which elements exist, and a row none of whose elements passes (no list, empty, all failing) is one tuple with NULL element columns, found before the snapshot check so a row the snapshot does not see is no tuple. The normal plan stays for a WHERE above the join that PostgreSQL could not turn into an inner join (p.n IS NULL sees the NULL tuple), a lateral condition that may raise, a lateral reading the row’s other lists, and other outer joins of an unnested JSON function; sift.agg_left_lateral off keeps it for all of them. Test agg_left_lateral, fixtures j_left_lateral*, fuzz family leftlateral.

Other rules:

  • Predicate-only joins (inequalities, range containment) test every candidate row; overlapping and duplicate matches multiply the inputs.
  • Lookup keys, grouping expressions and predicates may read earlier lookup outputs; duplicates and NULL-extended rows stay distinct inputs. Inner lookups are ordered by dependency within each join boundary; statement-level materialized CTEs can be referenced from nested queries.
  • Aggregate FILTER predicates may read lookup columns and source values together. They run per surviving joined tuple after all joins; an excluded tuple never evaluates a filter expression that could raise.
  • A condition PostgreSQL pushed into a sub-select (the run condition of a ranking window, rn <= 3) is enforced by its plan, which then does not output the column the condition read; the scan does not test it again (a column the query reads keeps its place and the condition is tested as usual). Test agg_pushed_conditions, fixtures nf_pushed_*, row J33.

Tests agg_correlated_children, agg_correlated_fanout, agg_derived_correlation, agg_append_children, agg_raw_value_children, agg_lateral_flatten, agg_left_each, agg_left_unnest, agg_plan_tables, agg_child_predicates, agg_lookup_dependencies, agg_statement_cte_children; fixtures pt_*.

Aggregates over CASE

sum, avg, min, max and count of CASE WHEN p THEN x [ELSE NULL] END, sum of CASE WHEN p THEN x ELSE 0 END, count of CASE WHEN p THEN x ELSE k END (k not NULL), min and max (integers, float4, float8) of CASE WHEN p THEN x ELSE k END and avg (integers and numerics with any ELSE constant, float4 and float8 with ELSE 0) of CASE WHEN p THEN x ELSE k END are planned as the aggregate of x with a filter on p (sift.agg_case_filter, on by default): the scan computes x on its kernels under a test of p, where the CASE would be evaluated for every row on the generic path. The rows do not change: a sum with ELSE 0 is still 0 for a group where p never holds, NULL for an empty input and for values that are all NULL (it is read as CASE WHEN count(*) FILTER (WHERE p IS NOT TRUE) > 0 THEN coalesce(sum(x) FILTER (WHERE p), 0) ELSE sum(x) FILTER (WHERE p) END). The other aggregates over an ELSE value are made of the aggregates of the rows where p holds and the number of rows where it does not (n0 = count(*) FILTER (WHERE p IS NOT TRUE)): count is count(x) FILTER (WHERE p) + n0, min is CASE WHEN n0 > 0 THEN least(min(x) FILTER (WHERE p), k) ELSE min(x) FILTER (WHERE p) END (greatest for max; k a constant of the type of x; not a numeric, as two equal numerics that differ in scale or sign tie in an order that depends on the rows, and not a float zero with a sign), and avg is CASE WHEN n > 0 THEN (coalesce(sum(x) FILTER (WHERE p), 0)::numeric + k * n0) / n::numeric END with n = count(x) FILTER (WHERE p) + n0 and the same division of the same sum and count as the final function of avg, NULL for an empty input, k for a group where p never holds (a k with decimals is added only when a row gives it, as the scale of the sum is the largest of the values added). avg of a float8 is the same with the sum and the division in float8, and avg of a float4 with sum(x::float8) (float4_accum adds in float8), for ELSE 0 only: k * n0 is no sum of k (the rounding depends on where the k rows sit: 23.852380952380507 as written, 23.852380952380948 as (sum + 0.1 * n0) / n on 3,000 rows). DISTINCT, several WHEN branches, a sub-select, a volatile or set-returning function in the condition or the value stay as written. A statement the scan does not run keeps the plan of the CASE as written. test/sql/agg_case_filter.sql and test/sql/agg_case_filter_else.sql check the shapes against the plan without the scan.

Join key filters and shared lookups

An inner or semi lookup join whose key is one integer (int8 too) or date leaf of the index (JOIN stores s ON s.id = o.tenant_id, IN (SELECT ..), EXISTS) needs a row of the table with that key. The table’s keys, loaded at scan start with its own conditions applied, therefore become a scan conjunct leaf IN (keys): a byte table over the key range (a hash table above 4 million values), with the same test, zone pruning and source filters as an IN list of constants. A unit whose zone misses the range is not read (Units Pruned); other rows are tested as a set before a tuple is made, where the join would resolve every tuple through the combo cache. A multi-column join adds one conjunct per integer key.

An ORDER BY .. LIMIT scan (Top-N) takes the same joins, and by a text key too (text, varchar, an inline or dictionary leaf): the keys of the loaded table that pass its own conditions become the exact conjunct leaf = ANY(keys) of the scan, evaluated once per distinct string of a unit like a constant list, so no join is left for the rows of the heap. An inner join needs a unique index on the key (a duplicate key would repeat the row); a semi join (IN, EXISTS) does not. An anti join by a text key, a column of the joined table in the select list and a nondeterministic collation keep the normal plan. 600,000 rows, 200 keys: ORDER BY dim_a_id DESC, id LIMIT 10 takes 118 M instructions and 188 buffer accesses where the nested loop took 6,461 M and 1.8 M; test agg_topn_join_text.

  • A join keyed by a column of that table (JOIN ids ON ids.id = d.id after JOIN d ON d.id = f.d_id, inner or semi, no condition on the scan’s own columns) drops the rows of d it has no match for, so only the others' keys enter the conjunct; no key at all (empty selection) reads no unit. The chain is also probed from the scanned row’s column inside the rows of a calendar LEFT JOIN: the keys of B are a conjunct there too, and a join no tuple of the conjuncts fails is not looked up per tuple.
  • Two tables keyed by the same scanned column give one conjunct, the narrower: IN (A) with A within IN (B) is IN (A), and the pass over the rows for B is not made.
  • A table joined by the column that an IN list of the scan names (an inner or semi join, EXISTS; the scan’s = ANY of constants or a parameter) is read by one scan of its btree on that column, with the list as the scan keys and the join’s column = value conditions on the next columns of the index as further keys, while that costs less than the table read whole (a descent for each key, a heap fetch for each entry found and the pages visited: sift_agg_keyed_ops against sift_agg_whole_ops; the same price decides the executor’s load and the planner’s). A dependent lookup’s keys (above) take the same path. A table read by conditions of its own (the semi-joined rows of one list, through its btree) is such a dependent too: a report of one list reads the parent table for the list’s keys alone, where it classed or loaded the whole table (20,000,000 events by 7,600,000 parents, 800 keys: 548 ms to 39 ms). Test agg_lookup_scoped; off with sift.agg_lookup_pushdown = off. When the btree holds the columns the join reads (key columns or INCLUDE), the rows of an all-visible page come from the entries (heap fetches counts the others), and the index with the cheapest entries per key is the one used (sift_agg_entry_ops); NOT EXISTS and LEFT JOIN keyed by the IN list are priced like the inner and semi join. Test agg_lookup_in_keyed, bench cases lookup_in_keyed, lookup_index_only. Test agg_lookup_in_keyed, bench case lookup_in_keyed.
  • Distinct counts of one unique key of a joined table under FILTERs of that table’s own columns (count(DISTINCT p.id) and count(DISTINCT p.id) FILTER (WHERE nullif(btrim(p.a), '') IS NOT NULL), the key being the join’s) are planned also as one scan that groups by the outcome of each filter and counts the ids once, under a query that adds up the counts of the groups each filter keeps (the key fixes the row of the table, so an id is in one group of a group key). The other aggregates are computed in the scan for each group and added up. The planner keeps this plan where it costs less than the plan with one set of ids for each count. The answers are exact.
  • A join to a large table by a btree column, or an EXISTS, NOT EXISTS or IN over one, where the scan’s keys are not a list it names, is planned a second time as the lateral child that has the same rows (JOIN LATERAL (SELECT .. FROM big WHERE k = x.k AND .. OFFSET 0) ON true; LIMIT 1 for the semi and anti joins), which the scan probes in the btree once for each distinct key it meets (Lookup Joins: sift_probe, agg/flat/probe.c). The plan with the probes is priced by what a probe examines (the entries of the key that the conditions of the index let through, from the index alone or the heap) and kept where it costs less than the table read whole, the keyed load and the ordinary plan; sift.agg_probe_rewrite = off plans the statement as written. The forms taken: inner and left joins (JOIN, or tables of the FROM list tied by the WHERE), EXISTS, NOT EXISTS, IN, and NOT IN where neither side can be NULL; a sub-select, lateral entry, whole-row Var or named join that reads the table is renumbered with it. Not taken: a system column of the table, a lateral entry beside a table of the FROM list (the table goes last in the list), a NOT IN that a NULL could change, a sub-select in the join’s own condition. The table the scan reads is not probed, and the child of a probe has no bitmap scan (the scan walks an Index or Index Only Scan).
  • A table loaded and not yet vacuumed or analyzed has pg_class.reltuples -1 and relallvisible 0: the rewrite takes the planner’s own estimate of its rows (the pages and the row width, estimate_rel_size), so a bulk load plans the same as a vacuumed table, and the direct probe takes an integer key of another integer type and refuses an index entry its child’s filter rejects before the heap is asked about it (the heap is asked for every entry of a page that is not all-visible: 200 keys of 67 entries, 13,399 fetches and 14,206 blocks, 698 after). The other reads of pg_class in the scan are not sizes taken as none: the epoch of a verdict (verdict.c) is a change detector (an ANALYZE or VACUUM moves it from -1), the text-key trial of a join (init_table.c) has a floor of 256 keys, the top-N group walk (topn/gcond.c) a default of 100 rows a page, and the reserve of a map (lookup/load.c) is skipped; map_probe (lookup/load.c) takes the planner’s estimate of the rows too (it took Max(reltuples, 1), which for a unique key of a table without statistics made the keyed read win by accident), and counts the entries of a few of the keys in the index where the statistics say nothing about the rows a key has (the keyed price, below).
  • EXPLAIN ANALYZE: Join Key Filters; sift.agg_join_filter = off restores the plain join.
  • When the key is a column a partitioned table is split by, the keys also drop partitions none can be in (Partitions Removed): a range or list bound by the key range and, with few keys, by each key; a hash level by the keys' remainders; no key reads no partition.
  • Rows equal the normal plan’s, and a tuple the join drops never reaches an expression (a conjunct on the fact rows that may raise runs on rows whose key the table holds, as in a plan that joins before filtering).
  • A text key (text, varchar, a deterministic collation) of a scalar leaf, or of a text field of the elements of an array (FROM t, jsonb_to_recordset(t.col->'items') AS o(sub text) JOIN keys ON keys.k = o.sub), gives a source test instead: the table’s distinct keys are tested on the encoded form of each unit, which prunes the units whose dictionary or prefix zone holds none and keeps the rows (for an element leaf, the rows with an element) that hold one. The strings of an inline unit that no key matches are never interned or hashed, and a dictionary unit looks up the keys, not every row. The list is tried when it has at most 65,536 keys and a sixteenth of the table’s rows (256 where the rows are not known): the source looks each key up in each unit, and a longer list costs more than the join it saves; a test that keeps more than half of the rows of its first four units is dropped for the rest of the scan. EXPLAIN ANALYZE: Join Text Filters; the same switch turns it off. A key list wider than the join needs is safe (the join still drops the tuples without a match), so the rows are the same. A per-unit presence set for the strings of an element leaf was measured and not built: a unit can only be skipped when it holds none of the keys, and 10 keys that occur once each in 375,000 elements of 38 units leave 10 units (30.7 M instructions and 397 buffer accesses become 11.5 M and 134 when only those units are read), 200 keys leave 37 units and 10 frequent keys all of them. A bloom of 8 bits an element would be 2.3% of a 16 MB index, an exact directory of the strings about 11%, for a gain that only a handful of rare keys reach.
  • No conjunct for LEFT, RIGHT, FULL and anti joins, element-level integer keys and top-N scans (a top-N takes only integer and date joins). A plan with a preserved side still gets the conjuncts of its inner and semi joins (a tuple failing one never matches a preserved row, and unmatched preserved rows remain the NULL rows of the groups).
  • When the join returns no column of the table, the filter holds the keys and an inner join’s keys are unique, tuples the filter passed are not looked up again: Join Key Passes.
  • Keys spread over more than 4 million values also skip units whose zone holds none. Two sets on one leaf (an IN list or a dimension’s keys, plus a join’s keys) are met before the zones are tested: a unit is read when its zone holds a value of both (test agg_zone_sets).
  • Cost keeps the units the keys' zones leave (the leaf’s mean zone width is stored with the index; keys count as points).

Tests agg_join_filter, agg_join_filter_text, agg_join_filter_elem_text, agg_join_key_types, isolation spec agg_join_filter, fixtures j_join_filter_*, row J28.

A combo of one input (a join key, a one-leaf grouping expression) finds the entry of an id below 65,536 by array index; larger or negative ids and multi-input combos use the hash table. A combo whose inputs are columns of one earlier lookup’s output row (and the one leaf that lookup is keyed by, as the month calendar of a period join) finds its entry by that row: the row names the leaf’s value, so a tuple costs one array read.

Sharing. UNION ALL arms (or a CTE read several times) of one statement that join the same table with the same key, output columns and conditions read the same rows in one snapshot: the first node loads them into a statement context and the others reuse the table (sift.agg_share_lookups, Shared Lookup Tables). Arms with other columns or conditions, a plan as table, a preserved side, and parallel workers load their own. Test agg_share_lookups, isolation spec agg_share_lookups, fixture j_share_arms, row J29.

Classes of keys. A lookup table of one int4 key whose other columns, as the query reads them, are leaves of a sift_col index of it (integers, dates, times, timestamps, booleans, floats, numerics, strings through the dictionary of the unit, and the expressions the index holds) is not hashed row by row when its keys are unique and the tuples of values of those columns are few (at most 4,096, 2,048 by the statistics at plan time): the load gives each key the class of its tuple in an array over the keys (2 bytes a key, within sift.agg_lookup_max_kb), and the map keeps one row for each class. The scan resolves a tuple by the class of its key, so a combo makes an entry for each class (a few dozen), where it made one for each distinct key, with the hash insert and the evaluation of the expressions of its table (millions, for a fact table’s parent). The columns that tell the classes apart can be tests of the table’s columns (FILTER (WHERE dd.a = 1)): the load computes each test for every row of the table from the leaves of the columns it reads, and a table whose tests take three outcomes at most is classed by them, however many tuples of values the columns take; a test that may raise an error, a test in the ON clause of the table and a query with a preserving join are not computed at the load. An inner or left join with nothing else in its ON clause, whose combo reads the columns and not the key; a key out of line (negative, repeated, a span over eight times the rows) or a tuple past the 4,096th keeps the table whole, as does sift.agg_lookup_columns = off. The workers read the array from the shared memory like the rows; the table is kept for the next statement like any other (sift.agg_lookup_cache_kb, sift.agg_lookup_shared_kb), under its own entry: a join that reads the key as a column loads the rows. A key filter of the join is not built from it. The classes of a big table are made by workers together: when the statement is a read-only SELECT outside parallel mode, max_parallel_workers_per_gather is above 0 and the index holds at least 8 times sift.agg_par_min_rows rows (640,000 by default; the scan’s own plan need not be parallel), the leader starts up to 14 workers that take the index’s superblocks as they come and class their rows, the leader reading with them; each gives its keys the class of their tuple in one array over the keys (the first writer wins, so a key twice is still a table that keeps its rows), and the leader puts the workers' classes in the order of their first rows, which is the order one process meets them in: the classes, their numbers and the array are those of a load by one process (checked by a checksum of all three), so nothing downstream differs. A key beyond the largest the index says (rows written since its statistics) or more strings in the classes than a worker hands over (512 kB) is read by the leader alone; a column that is a computed test is classed by the leader alone (the outcomes of the tests are found once for each tuple of values they read, and kept for the next row with it: 4,000,000 keys and three tests take 195 ms, where evaluating them for each row took 495). On 5,000,000 keys and four columns (two of them text and numeric) the load takes 138 ms in one process (minimum of 7), 87 ms with four workers and the leader and 84 with eight: the array of the keys and the workers' start cost about 35 ms whatever their number. EXPLAIN ANALYZE: rows classed, class workers. Test agg_lookup_dense, agg_lookup_dense_kept, agg_lookup_tests, agg_lookup_gang, bench cases lookup_dense_classes, lookup_dense_kept, lookup_dense_tests.

Expressions of a lookup table. An expression of the columns of an inner-joined table that a sift_col index of it holds as a column (CREATE INDEX .. USING sift_col (id, presence(name, city, ..)), the query writing the same call or operator) is one column of the lookup table: the load reads its leaf, where it read the columns the expression reads and the expression was evaluated for each distinct tuple of them (a presence mask of seven text columns of 7.6 million rows: 58 s of the original query, 0.25 s grouped by the mask). A table without the leaf evaluates the expression for each row loaded; a left join keeps the expression over its columns (it is not NULL for a row nothing matched). The expression may be a test or a conjunction of tests (nullif(btrim(name), '') IS NOT NULL, lat IS NOT NULL AND lon IS NOT NULL as a boolean leaf: the query’s IS NULL of the same text, or lat IS NULL OR lon IS NULL, is its complement, on the scan’s leaves as on the lookup table’s), and the key of an inner join is the leaf it equals, so count(DISTINCT parent.id) of parent.id = child.parent_id reads no column of the table. A table with such a column is not kept in the caches below. The planner prices the classes of a column of the lookup table by the range and NULLs of its leaf in the index (a bound the index always knows) and by PostgreSQL’s statistics where they say fewer, so a table that was never analyzed is taken as classes too; the groups of a key computed from those classes are no more than they.

Types read from the leaves. The columns of a lookup table that one sift_col index of it holds as whole leaves are read from them, not from the heap, when they are boolean, int2, int4, int8, date, time, timestamp(tz), float4, float8, numeric of at most 18 digits, or a text type (the dictionary entry of a value is made into a text once for each unit); only the integer, date, time, timestamp and boolean ones tell classes apart. A column of another type (uuid, interval, numeric of more digits), a leaf that is a path or lossy, or columns spread over several indexes send the whole table to the heap: EXPLAIN (VERBOSE) prints Lookup Heap Reason with the column and the index, and DEBUG1 logs it. Measured per row of a 100,000-row table joined from 1,000,000 rows, one backend, instructions (perf stat, the parent 100,000 rows less 25,000 over 75,000): leaves 2,250 (int8, timestamp, float8, time), 2,400 to 2,700 (text), 2,900 to 3,000 (numeric); heap 2,800 to 3,250; classes 147 to 173.

Per-backend cache. A backend keeps such a table’s rows for its next statement (sift.agg_lookup_cache_kb, Cached Lookup Tables), since loading a mapping table of half a million rows is the fixed cost of every query joining it. Rows are kept under the table’s conditions while every heap page is visible to all transactions (vacuumed) and has the LSN it had at load. A write in flight, committed or rolled back, a vacuum that rewrites a page and a table rewrite all force a reload. Never kept: a table with a page not yet visible to all (rows not vacuumed since a write), a non-constant condition, a preserved side, a plan or non-heap table, and parallel workers. Test agg_lookup_cache, isolation spec agg_lookup_cache.

Cross-backend cache (sift.agg_lookup_shared_kb, on by default; superuser; -1 is the smaller of sift.agg_lookup_cache_kb and 5% of shared_buffers, 0 turns it off). A backend that loads a table writes its rows, in the flat form of a parallel scan, into a dynamic shared memory segment and lists it in a directory of 64 entries reachable by name from every backend (the DSM registry: no restart, no preload). A backend that finds the key’s entry with the pages it sees now (same LSNs, all visible, as above) attaches and reads in place, so a one-query connection pays the attach, not the load.

  • The segment holds key and pages and they are compared in full; the directory only has hashes. Rows are per role, as in a backend’s cache.
  • A write moves the LSNs; the old state’s entry goes when the key is kept again.
  • Entries together take up to the setting (least recently used first; a table over half of it is not kept). A segment dropped from the directory stays until its readers finish. A crash restart discards registry and segments.
  • A backend loading while others load the same table (cold start) loads for itself.

Test agg_lookup_shared, isolation spec agg_lookup_shared, TAP test 018_lookup_shared.

Dimension map (sift.agg_dimmap_kb, 64 MB by default; superuser; 0 turns it off). The classes of the keys of a dimension (the table of Classes of keys above: an int4 key, columns of any type that table lists, at most 4096 distinct tuples of them) are kept in a dynamic shared memory segment of their own (two bytes a key and a row for each class) by the first backend that reads them from the leaves of the dimension’s sift_col index; every later statement of any backend reads them in place. Unlike the classes of the cross-backend cache, which are the table’s only while every page of it is visible to every transaction, a map is valid by one of two rules:

  • the dimension’s index keeps a journal of its changed keys (a child sift_col index is connected to it, WITH (connect = 'fk -> dim(key)'), the journal on the key the join is by): the map is stamped with the horizon of the snapshot it was read under (sift_conn_take before the leaves are read) and a statement reads it when the journal vouches for its snapshot, no row was inserted into the dimension since (sift_conn_inserted_since; an update inserts a version) and no key was changed in it (sift_conn_changed; the statement’s own changes count, so a writer’s map is its own and is not kept). A write to the dimension makes the next statement read the leaves again and keep a map under a later horizon: no vacuum is needed and the size of the table does not matter. Switching the connection off (ALTER INDEX child SET (connect = 'off')) changes the column layout of the child index: REINDEX INDEX child follows it, and the journal of the dimension and its stamp stay. A snapshot older than the horizon of the map (a REPEATABLE READ transaction that began before it) makes its own map, which is not kept;
  • otherwise the pages of the table, as for the cross-backend cache: every page visible to every transaction, the LSN of each page the same before and after the read and when the map is used.

A map that does not fit half of the budget is not kept (the statement reads the classes itself); entries go least recently used first; a key read as a column of the dimension is not a map. On 1,000,000 facts over 100,000 keys, the grouped count and sum by an attribute of 60 values takes 112 million instructions without the map and 91 with it when the dimension was written since its last vacuum, and 105 and 90 when it is vacuumed (the classes of the lookup caches; the three-column dimension of the bench case dimmap_shared_classes takes 76 either way); by 3,000 values 135 and 113, count(DISTINCT) of the dimension’s key by 60 values 136 and 115; three FILTER aggregates over attributes (classes by the outcome of each test) take 222 either way, the map saving the 290 blocks the first statement of a backend reads. EXPLAIN ANALYZE shows Dimension Maps Shared and Dimension Maps Built. Tests agg_dimmap, agg_dimmap_conn, agg_dimmap_multi (a child index connected to two dimensions: each has its own journal and maps), agg_dimmap_types (every kind of parent column, serial and with workers, before and after writes to the parent and the child: all are maps), agg_dimmap_values (the specials of floats, numerics and strings), agg_dimmap_fuzz (random writes against the normal plan), isolation specs agg_dimmap, agg_dimmap_conn, TAP test 023_dimmap (crash recovery, a budget too small, twelve sessions making one map, a standby).

Semi/anti trees. The right side of a semi or anti join may be a tree of joined tables (IN (SELECT .. FROM a JOIN b ..), EXISTS (SELECT 1 FROM a JOIN b ON .. WHERE a.k = o.k), or the coalesce of the LEFT JOIN PostgreSQL makes of p.id IN (SELECT k FROM (SELECT coalesce(a.k, b.k) AS k FROM a LEFT JOIN b ..) t)). The tree is PostgreSQL’s join of those tables; the scan runs its cheapest serial plan once at start, as for a sub-select, and looks keys up. The scan-side condition reads one table of the tree (the key); ON conditions on the tree’s own columns are already in its plan. The normal plan stays for a tree that reads the query’s other tables, has only a parameterized or parallel plan, or has more keys than sift.agg_lookup_max_kb allows, and for a key that is an expression of two tables of the tree straight under the scan (o.k IN (SELECT coalesce(a.k, b.k) FROM a LEFT JOIN b ..); from_shape). Test agg_semi_tree, fixtures j_semi_tree*.

Self-join elimination (PostgreSQL 18). WHERE id IN (SELECT id FROM t WHERE ..) over the table’s own unique key is merged by PostgreSQL 18 into one scan of the table that holds both conditions, and the conditions are no longer in the FROM tree the scan reads. The scan cannot tell which ones apply, so the query keeps the normal plan (sift.explain_why: slug from_shape, “PostgreSQL merged a joined table into another scan of the same table”); PostgreSQL 17 and 19 keep the join and the scan answers it. What the normal plan costs, measured once on PostgreSQL 18.6 (not from the records; ev of 300,000 rows with 50 keys, warm, 11 statements in one session, perf stat instructions:u of the server processes): the merged statement 258.3 M instructions per execution, the same statement written as one table with the condition (WHERE amount > 500) 6.0 M with the scan and 258.2 M without. Write the condition on the table, without the self IN, to get the scan.

Outer joins that keep the other table

A RIGHT or FULL JOIN to a table, and a LEFT JOIN whose right side is the indexed table, aggregate over the table that keeps its rows (EXPLAIN: Lookup Joins: stores (preserved) or (full)). The scan reads that table at start and marks the rows the index’s tuples match; when the index is read, unmatched rows become tuples of NULL leaves going through the same keys, combos and aggregates (count(o.id) adds 0, count(*) 1, FILTER (WHERE o.id IS NULL) is true). A FULL JOIN also keeps tuples with no match, with NULL columns. ON only decides matches: a condition on the table’s own columns, a NULL key or a duplicate key leave rows unmatched or multiply tuples, as in the normal plan. When both tables of a LEFT JOIN have a sift_col index, the planner prices both scans, the parent (the preserved table, with the child as its lookup) and the child (with the preserved parent as its lookup, loaded as classes of its keys), and runs the cheaper: a count of the children per parent, or a sum with conditions in the ON clause, over 1,000,000 children of 100,000 parents takes between a third and two thirds of the time of the parent scan with the child scan. sift.agg_child_first = off plans the parent scan only. Test agg_left_child_scan (answers equal to the normal plan, serial and with workers, after writes to either table).

  • The normal plan stays when a WHERE conjunct over only the indexed table must be evaluated after preservation.
  • The indexed table may be partitioned: the preserved table is read whole at start, whichever partitions are read. The preserved or looked-up table may be partitioned too (its plan runs once at start). A FULL JOIN to a view or sub-select with conditions keeps the rows they leave out out of the join; a FULL JOIN to a view left of the scanned table stays plain.
  • A nullable indexed input may contain ordinary lookup joins and expand one JSON object or array before joining the preserved side. Empty preserved buckets contribute one NULL fact input, NULL lookup columns and expressions included.
  • ON conjuncts of such a LEFT JOIN that read only the indexed table (and rows it unnests) decide which of its rows can match, so they are scan conditions: an EXISTS over array elements (a sub-select PostgreSQL pulls up) or a jsonb_path_exists runs there, and so does an aggregate filter on a test computed inside the nullable input. The test of an absent input (CASE WHEN absent THEN NULL ELSE x END) is read as x only when x is not true for the NULL leaves an empty bucket contributes (comparisons, IS NOT NULL, EXISTS); x IS NULL keeps the normal plan.
  • A scan with such a join runs without workers: one scan sees every row.
  • A sub-select in the select list or the HAVING of a grouped query (SELECT kind_id, count(*), (SELECT name FROM kinds k WHERE k.id = events.kind_id) FROM events GROUP BY kind_id, HAVING count(*) > (SELECT count(*) FROM members m WHERE m.kind_id = events.kind_id)) is evaluated by the node that gives the groups, once for each group, with the group keys and the aggregates it reads as its parameters, as above PostgreSQL’s aggregate (EXPLAIN: the SubPlan under the Custom Scan (SiftAgg), the condition as its Filter). The sub-select runs the normal plan of its own table (an index lookup or a sequential scan for each group, whatever the planner chose), so a query whose cost is that sub-select costs what it did; one that reads a column that is not a group key or an aggregate (a Var, a window function) stays out of the scan (output_expr). The statement is planned without workers. Test agg_output_subplan, bench case agg_output_subplan.
  • A HAVING condition column IN (SELECT ..) or EXISTS (SELECT ..) of a plain GROUP BY (GROUP BY kind_id HAVING kind_id IN (SELECT id FROM kinds WHERE enabled), HAVING EXISTS (SELECT 1 FROM kinds k WHERE k.id = events.kind_id AND ..)) is a condition of the rows, since every row of a group has its key. PostgreSQL moves a HAVING condition to WHERE itself only without a sub-select (it keeps the sub-select above the aggregate, run for each group), so the planner hook plans the statement with the condition in WHERE, where the sub-select is a semi join the scan answers and the rows of the other keys are not grouped, and takes that plan without a comparison with the one that tests the groups: it read less in all of 40 measured cells (0.09 to 0.78 of the cycles, 300,000 rows, 100 to 200,000 groups, lists of 1% and 50% of the keys, with and without workers). It moves the shapes where that holds: an IN on the column as it is (or a varchar relabelled to text) whose sub-select does not read the query, whatever it holds (the planner makes a semi join of every uncorrelated IN in WHERE; in HAVING it is a sub-plan of the groups, hashed or, when the list does not fit work_mem, run for each group), and EXISTS of a sub-select without aggregate, grouping, limit, set operation or CTE, with no aggregate of the query in the condition and nothing volatile. NOT IN and NOT EXISTS (the rows form is slower in some cells and faster in others), an expression or a cast of the key (amount::int IN (..) is a function of the key that equal groups may not agree on), a correlated IN, a HAVING with grouping sets (a key is NULL in the rows of a super-aggregate) and one without GROUP BY (one group even for no row) keep the HAVING and the plan chosen by cost; the other conditions of a HAVING of several move on their own. EXPLAIN shows the join under the Custom Scan (SiftAgg), no Filter above it. Test agg_having_where, bench case agg_having_where.
  • A table LEFT JOINed to a join of two or more tables (FROM kinds k CROSS JOIN regions r LEFT JOIN events e ON e.kind_id = k.id AND e.region = r.region GROUP BY k.name, r.region: a row for every pair, zero for the pairs of no event) is not one preserved table, so the planner hook also plans it as events GROUP BY kind_id, region joined to the pairs, and keeps the cheaper plan. The groups are made in the scan (EXPLAIN: Subquery Scan on e over a Custom Scan (SiftAgg)); a pair’s count is its group’s (0 for a pair of none, count(*) counts the row of a pair of none once) and a sum of integers or numerics, min and max its group’s (NULL for none). The join conditions are equalities of a column of the table with an expression of the other tables, the conditions over the table alone are conditions of the groups, the ones over the other tables alone stay in the join. A float sum, avg, count(DISTINCT), an aggregate FILTER, a column of the table outside the aggregates and a WHERE on it keep the join as it is (agg_left_partial).
  • Statements driven by the parent with a condition on its children and a GROUP BY of a parent column, measured once on PostgreSQL 18 (5,000,000 children of 250,000 parents, a sift_col index on both, a btree on the child’s kind, about 1% of children without a parent and 1% with a NULL key; EXPLAIN ANALYZE, minimum of four, warm; milliseconds with workers / without): the LEFT JOIN ... WHERE c.id IS NULL anti join, the LEFT JOIN with count(c.id) and count(DISTINCT p.id), and the RIGHT JOIN count run as SiftAgg by default and beat the normal plan (anti 247 / 604 against 444 / 655; left count 425 / 138 against 661 / 1,754; with a sum and a DISTINCT 177 / 145 against 1,470 / 1,368; right join 383 / 1,038 against 1,206 / 2,936). A FULL JOIN of the two tables keeps the normal plan, which is the faster one with workers (788 against 2,136 forced; 2,972 against 3,001 without). EXISTS (..) GROUP BY and IN (..) GROUP BY keep the normal plan, which the model prices as the cheaper one; forced, the scan is not slower on this data and reads far fewer blocks (EXISTS 415 / 435 ms and 1,450 buffer hits against 606 / 433 and 5,710; IN 387 / 291 and 2,223 against 497 / 667 and 227,196), while other data shapes measured elsewhere run slower forced, so the plan is left to the model until the price of the child’s load is calibrated on both. Test agg_parent_exists_shapes (the eight shapes, forced, answers equal to the normal plan for children with a NULL key, children without a parent and parents without children).

Fixtures ps_*, j_dim_partitioned*, j_full_*, nf_*, test agg_nullable_filters, rows J06, J07, J30, J31 of the coverage table.

An EXISTS over array elements may read a column of a joined table, or look each element up in a mapping table (LEFT JOIN LATERAL (SELECT .. LIMIT 1) m ON true, keyed by the element and a joined column) before testing the result. The lookup is PostgreSQL’s plan, run once per distinct combination of the values it reads, and a row’s answer is kept while its elements and joined values repeat. NOT EXISTS, EXISTS inside an aggregate FILTER and an expression that can raise keep the normal plan. Fixtures ee_*, row J34.

Partitioned tables

A partitioned table is one source of Custom Scan (SiftAgg) when every partition left after PostgreSQL’s plan-time pruning (at every level) is a sift_table or has a sift_col index, all of one kind, of the same layout: the same columns and expressions in order, the same paths, the same pre-aggregate options. Attribute numbers may differ (a dropped column), the order of the columns of a sift_table may not; sub-partitioned branches, default and unlogged partitions are partitions like the rest. The query is compiled once against the partitioned table’s columns; the scan reads each partition’s units (segment by superblock) one after the other, each with its own dictionaries, so groups, DISTINCT sets and parallel workers (which take units across all partitions) work as on one table, with the normal plan’s result. EXPLAIN shows Index: (partitions) and Partitions: a, b, ..; cost is the sum of the partitions' pages and rows. Pre-aggregates answer per unit as on a plain table. Privileges and row security are checked on the partitioned table, as in PostgreSQL (a role without access to partitions reads through the parent).

Per-partition finalization. When the group key holds every column of the partition key (the column itself, not an expression) and the partitions are direct children, a partition’s groups are final when its units end. A scan without workers and with a count(DISTINCT) (the aggregate whose per-group sets make the table large; other aggregates keep the single table) then reads a partition as one item, emits its finished groups (with integer sums and averages, counts, minima and maxima beside the sets) into the result tuples and restarts the group table. The table holds one partition’s groups, not all, and does not spill at work_mem. EXPLAIN: Groups Finished: per partition; cost is that of one partition’s table. The single table stays for a key without the partition key, a partition of a partition, an expression as partition key, grouping sets, DISTINCT ON, ordered-set aggregates, a scan with workers, and a float sum or average (its lanes add in an order partitions would change). Test agg_part_final.

Workers split the groups. A scan with workers splits the rows by default (each participant takes superblocks and builds a table of the groups it meets; the leader adds the tables). With many groups that costs the leader a merge of every table and each participant a table of nearly every group (1,000,000 rows, 100,000 groups: 19.8 ms alone, 34.0 ms with 4 workers). When the plan expects thousands of groups, the leader scans as a participant and a key of the group by is an integer, a date or a timestamp, the planner also prices the plan that splits the groups: each participant reads every unit and aggregates only the rows whose key hashes to its share, so the tables are disjoint and small and the leader only adds them to its own (EXPLAIN: Groups Split By: <key>). 3,000,000 rows, 100,000 groups: 40.6 ms alone, 59.7 ms with 4 workers splitting the rows, 35.2 ms splitting the groups; 300,000 groups: 99.3, 207.4 and 86.8 ms; 1,000,000 rows and 100,000 groups, where the split loses (19.8 against 23.8 ms alone), the plan stays in one process. The rows and the float sums are the same to the bit as with the rows split, and a worker that does not start leaves its share to the leader. These keep the rows split: a text key (every participant would hash the string of every row), a bigint key (a numeric key), a float key, an expression, grouping sets, a two-level query, a top-N, a search, a correlated join, a partitioned table, unnested elements and a scan whose units are answered from pre-aggregates. Test agg_par_split.

Workers split the keys of a join. A scan with workers that joins one table by a plain key (an integer, a date or a timestamp column of the scan, no expression) makes each participant resolve every key it meets (its id and its rows in the joined table), so the work of the keys grows with the participants and the workers shorten nothing of it when keys repeat. The planner also prices the plan that splits the rows by that key: each participant reads every unit and takes the rows whose key is in its share, so each key is resolved once (EXPLAIN: Rows Split By: the key of a lookup join). 5,000,000 children of 100,000 parents, a count per attribute of the parent: the user instructions of all participants with two workers are 1.11 times those of one process (2.04 times with the rows split), with four workers 1.21 times; 116 ms alone, 62 ms with two workers, 58 ms with four. The read of the key column that every participant repeats and the merge of the leader are what the instructions above one process are; below a few million rows the rows split is cheaper and stays. The rows are those of the other plans (NULL keys, children without a parent, parents without children and keys that repeat among them). These keep the rows split: a key of several columns or an expression, a bigint key, a join with parameters, a preserved join under pre-aggregation. Test agg_par_join_split.

The normal plan stays when:

  • a partition has no sift_col index or one with other columns or options, is a foreign table, partitions are of both kinds (sift_table and heap with an index), is read with ONLY, or the table is an inheritance parent (a mixed tree too);
  • a parameter is compared with a hash-level column other than by an equality of the column’s type (k > $1, a value of another type, an expression key: the normal plan prunes the remainders);
  • the scan reads one index for the shape (a ranked search with sift.score, a text search, a time series of unions).

Run-time pruning and limits

  • A condition on a partition-key column whose operand is a generic-plan parameter or a stable function (d >= $1, k = ANY ($1), ts >= now() - interval '30 days') prunes at plan start as in PostgreSQL: the scan evaluates the operand at start and skips partitions whose bounds it refutes (EXPLAIN ANALYZE: Partitions Removed); a default partition stays unless the others' bounds refute it.
  • A hash level is pruned by remainders: an equality or = ANY on every column of its key (parameter, stable function or constant) gives the values, the key’s hash function their remainders, and a partition none of them hits is not read (k = $1, k = ANY ($1), a = $1 AND b = $2; a NULL value or empty list reads no partition). Remainders are computed once per level for the key values (up to 1,048,576 combinations), not per partition.
  • A hash level’s partitions are found from the remainders by the bounds PostgreSQL keeps (one lookup for an equality, as a range key is bisected), and the value of an enclosing query’s row (LATERAL .. WHERE e.k = d.id) prunes the partitions of each probe. A top-N that finds one partition left walks that partition’s order array and fetches the rows from its heap (Global Order); more than one, or a partition whose columns have other attribute numbers than the first’s, reads the units.
  • A plan reads up to 32,768 partitions. The planner reads each index’s layout, statistics and pre-aggregates once and keeps them in the backend (a few kB each) until the index’s directory, its table’s pages or visibility map, or the catalog entry of the index or table change. A table with more partitions, before run-time pruning, keeps the normal plan and sift.explain_why names partition_count with the number.
  • The scan opens only the partitions pruning leaves (the whole query over 5,000 partitions of 6 rows each runs 312 million instructions, the normal plan 300 million) and releases one’s pages before reading the next, so a statement pruning thousands of partitions to a few opens only those (range-key bounds are bisected, not proved per partition).
  • PostgreSQL takes two relation locks per partition its plan keeps (heap and index), and the scan’s plan takes no more: max_locks_per_transaction needs about 2 x the plan’s partitions whichever plan runs (the default 64 x max_connections covers about 3,200).
  • A plan is invalidated when a partition or its index is attached, detached or dropped. CREATE INDEX on the parent makes one per partition (also for partitions attached later).

A top-N of a partitioned table (ORDER BY .. LIMIT, docs/COL-SEARCH.md) keeps its best rows with their partition (number in the high bits of the tie-break key, TID in the low ones), reads the zones of all partitions' units as one range of superblocks, and fetches kept rows from their partition’s heap by the partitioned table’s column names. An unindexed table written first in FROM is joined to the partitioned table the scan reads (inner joins). Fixtures pa_*, topn_part_*, t_partitioned, test agg_partitions.

Partial indexes

A sift_col index with a WHERE predicate holds only rows passing it, so the planner takes it only when the query proves the predicate (PostgreSQL’s predOK: the table’s conditions imply it, as for a partial btree or BM25 index). A query that may need a row outside the predicate keeps the normal plan, as does a generic plan of a prepared statement whose parameter the proof needs. Rows an UPDATE moves in or out of the predicate are inserted into or deleted from the index like any change. Among usable indexes the cheaper wins (a partial one is smaller; likewise an index cut by a key (key_cut) against one cut by block range), whatever the creation order. Every index that gives a plan is planned and priced, about one plan of a single-index table (0.05 to 0.1 ms) per extra index. Fixtures pi_*, t_partial, test agg_partial_index.

Uncorrelated sub-selects

col NOT IN (SELECT ...), and col IN (SELECT ...) under OR or NOT, are hashed SubPlans in PostgreSQL (no semi join is possible); Custom Scan (SiftAgg) takes them as a lookup table over the leaf. PostgreSQL’s own SubPlan answers once per distinct column value, so the NULL rule of IN/NOT IN is PostgreSQL’s (one NULL in the sub-select makes every NOT IN NULL, no row; an empty sub-select makes it true for a NULL value too), and the test composes with AND, OR, NOT, CASE and FILTER. The sub-select runs once at scan start with the query’s snapshot; EXPLAIN shows a SubPlan under the node. An integer column whose values span more than 4 million (statistics may not show it) is tested row by row instead of through a table. A rescan keeps the answers; prepared-statement parameters are read by the sub-select as usual.

No lookup table is built for a test of two columns ((a, b) NOT IN (SELECT x, y ...)), of a column the table does not hold (an int8 column), or inside an expression that reads the rows of an outer join’s nullable input (a group key CASE WHEN country IN (SELECT ..) THEN jsonb_build_object(..) END): the scan asks the same SubPlan for each tuple that reaches it.

The normal plan stays when the sub-select is correlated or reads an outer-query parameter, is not hashed (too large for work_mem and hash_mem_multiplier), reads a volatile function, locks rows (FOR UPDATE), or holds a sub-select of its own or an expression that may raise (a division, a cast through text). A scan with such a test runs without parallel workers: the leader runs the sub-plans. Fixtures t_not_in_subquery*, t_in_subquery_or, tests agg_subplan_lut, agg_semi_lut, agg_nullable_input_keys.

ctid ranges

ctid compared with a constant TID (=, <, <=, >, >=, either side, BETWEEN) is a scan predicate when it is a top-level WHERE conjunct, on a sift_col or sift_table. The conjuncts make one half-open TID range (keys block << 16 | offset, in PostgreSQL’s TID order). The block part is a range of superblocks (256 pages, or about 12000 rows of a sift_table): units outside are not read (Units: in EXPLAIN ANALYZE), units inside are read whole, and rows of the first and last block are tested one by one. A heap index stores the TID of a HOT chain’s root while ctid is the visible version’s, so those rows are looked up in the heap through the snapshot (HOT keeps versions in their page, so other blocks need no lookup). An empty or crossed range, or one beyond the table’s end, reads no unit; an invalid TID constant is PostgreSQL’s error. Cost keeps the fraction of the range’s pages.

The normal plan stays for <>, ctid in an expression, in GROUP BY or compared with a column, an OR, IN/= ANY, a generic-plan parameter (a custom plan folds it), a partitioned table (TIDs are per partition) and top-N shapes (their probes read zones outside the range). PostgreSQL plans a tid range scan for a narrow range (enable_tidscan = off shows the scan). Fixtures tr_*, test agg_tid_range.

json_build_object above the scan

A statement that reads a table with a sift_col index and builds a JSON object per output row (json_build_object in the select list of a window, a sub-select, a CTE or the top level) calls sift.json_build_object where it names the core function: the planner hook changes the call in the query tree it plans, so EXPLAIN (VERBOSE) shows sift.json_build_object. The text is the core function’s (test json_build_object compares both byte for byte for every type of argument, key and error). What changes is that the category and the output function of each argument are looked up once for the call site, not for every argument of every row: json_categorize_type and the type cache were 5% to 13% of the instructions of five grouped statements over 300,000 rows that build 5 to 12 objects a row above 10,000 to 60,000 groups, and the statements run 4.9% to 16.3% fewer instructions (a grouped object of six arguments over 60,000 groups: 1,836 to 1,558 million; a nested object with lag() over a window: 2,130 to 1,766 million).

A call with no argument, jsonb_build_object, a statement that reads no table with a column index, and a role that may not execute sift.json_build_object keep the core function. sift.enable_agg = off stops the change with the rest of the hook; there is no setting for it alone. Test agg_json_swap.

Memory of the group table

The planner prices a scan’s group table (expected groups times bytes per group, about 185 for one key and five aggregates) against hash_mem; past it the scan is charged the spill, and the arm keeps the normal plan with slug group_memory when that makes it dearer.

The executor holds the same budget. When the table of an aggregate made of count, sum, avg, min and max passes it at the end of a unit, the groups go to disk hash partitions as partial blobs (the form workers send the leader); partitions merge one by one at the end, each split again by another hash while it does not fit, up to group_spill_depth levels (group_spill_parts partitions at most). A scan that must spill runs without workers. Float sums add in partition-merge order, so last digits can differ from an unbounded table’s.

  • count(DISTINCT) and distinct sum/avg of numbers spill with their sets, united when partitions merge. Each set is walked once for all partitions of a spill, and a count(DISTINCT) whose bitmaps held less than a value per 128 bytes of chunks at spill time starts the next tables as a hash set.
  • The set of a column whose values fall in a window of at most 512 (dist_narrow_words words of 64 bits) keeps a few words per group instead of a directory and a 1,024-byte chunk, becoming a bitmap when a value falls outside; an integer column whose zones span more than the window starts as a bitmap.
  • Passes. A serial scan whose table first passes hash memory (not in the order of its group keys, not with pre-aggregates, a join with parameters, a float sum whose order matters, or workers) starts again as passes over the units (agg/spill/pass.c): the keys of the first group column that is an integer, a date, a timestamp or a text are cut into 32 shares by hash, and a pass reads every unit for some of them (the rows of the other groups are dropped before they reach the table, as the participants of a parallel scan split the groups). The first pass takes the shares the table fitted in by the groups the planner expects and the memory used when it first passed hash memory, each next pass as many as the memory of the last held; a pass whose table passes it again is read again with fewer shares, and when one share does not fit the shares left are read together and spill as above (the same temporary blocks as the spill, up to 6% more instructions for the pass that was stopped); a table the estimate puts above about 25 times hash memory is spilled from the start. The groups of a pass are final when it ends: they go to the tuples of the result at once, with no partial state written or merged, so a count(DISTINCT) over a million pairs that wrote 45 MB of temporary files at 4 MB of work_mem writes none. Group Passes in EXPLAIN ANALYZE says how many; there is no setting.
  • A scan that gives its groups in an order (ORDER BY the group keys) spills in ranges of the first key when it is an integer, a date or a timestamp, merged in that order (docs/COL-PLAN.md, “Groups in the order the parent sorts by”); another first key keeps the scan until the table is group_nospill_factor times hash memory.
  • A scan of that kind whose first key is the value of a leaf or a date_trunc of a timestamp leaf, over superblocks whose zones leave most groups final before the end (agg/spill/stream.c), does not spill: it reads the superblocks in the order of the zones of the key (the lowest place in the order of the result that a group of each can have; a superblock with rows of a write buffer has no zone and is read first), and after a superblock that leaves the table past hash memory the groups below the zone of the next one are sorted and returned as a batch (Group Stream Batches), the others dumped and loaded again for the next. A NULL key is at the end of the order, and with NULLs first no group is final before the superblocks that may hold a NULL are done. When no group is final and the table keeps growing (the estimate from the zones was wrong), the rest of the scan spills as above, after the batches it has given. A float sum, a count(DISTINCT), a join, an arm or a match set keep the spill. Test agg_group_stream.
  • Shapes that cannot spill (arms of a two-level aggregate, grouping sets, set keys, top-N, ordered-set and generic aggregates, and sum/min/max over combos such as numeric and text columns) keep the scan until the table is group_nospill_factor times hash memory (8 MB at least), then the normal plan.

Tests agg_distinct_narrow, agg_group_memory, agg_group_passes, agg_group_spill, agg_group_spill_fuzz, agg_group_spill_limits, agg_group_spill_order.

DISTINCT ON

SELECT DISTINCT ON (keys) ... ORDER BY keys, sort is a group by the ON columns keeping the first row’s columns per ORDER BY. Each such column is a “first row” aggregate (first_row(x ORDER BY ...) in EXPLAIN), not the array_agg(...)[1] rewrite (which sorts the groups' rows): it keeps each group’s best row, compared with the ORDER BY’s operator, collation and NULLS FIRST/LAST. The query’s ORDER BY, LIMIT and OFFSET run above it. With workers each keeps its best rows and the leader merges them with the same comparison; several select-list columns share one comparison per row.

Rows of a group may tie in the ORDER BY; SQL leaves the row DISTINCT ON keeps unspecified, so the scan keeps the first one it meets, the same row for every select-list column (with workers, the leader keeps the better best row of the group and, on a tie, its own). The scan is planned for at most 4 sort columns after the ON columns; a plan with a unique column in the ORDER BY gives the same row as every other plan.

array_agg(x ORDER BY o) that the query reads only through a slice a[1:n] or an element a[n] of a constant n (in the select list, the sort and the HAVING, up to 1,000 rows; read whole anywhere it is the sort of the group’s rows) keeps the n best rows of each group in memory as the rows pass and gives them, in order, to the aggregate’s own functions when the scan has ended (EXPLAIN shows array_agg(x ORDER BY o)[1:n]): a row that comes after the group’s n-th is dropped after the compare of its sort keys, and the others are read and placed by a binary search, after the rows they tie with. Every array_agg of the slice has its own rows. The groups' rows are held until the scan ends, as the DISTINCT ON rows are, so a query whose groups would not fit the memory of a hash aggregate is refused (first_memory). row_number() OVER (PARTITION BY k ORDER BY o) <= N (N > 1) is written this way before it is planned (agg/latest.c). rank() and dense_rank() <= N (N up to 1,000) are written with the arrays not cut and a mark on each array_agg that only the scan reads (EXPLAIN shows [rank <= N] or [dense_rank <= N]): a group keeps the rows of rank <= N (the N best and the rows that tie with the N-th) or of the N best values of the sort keys, and the window function and its condition run above them, so the ties are the window’s own. A group whose rows all tie keeps them all.

The normal plan stays when a sort or select-list expression is volatile, a value is an array or a joined table’s column, or estimated groups times bytes of best rows exceed hash-aggregate memory (work_mem times hash_mem_multiplier): groups are not spilled. A volatile select-list function PostgreSQL evaluates after the rows are picked does not matter. Fixtures do_*, t_distinct_on, pa_distinct_on, test agg_distinct_on.

Counting distinct values over the rows of a scan

A report that aggregates per-row aggregates (GROUP BY id, key, a UNION ALL of such arms) and counts count(DISTINCT id) per group above them reads their rows through a join (a spine of ranges) and a sort of every row on group columns and id. When the plan reads Custom Scan (SiftAgg) nodes and an aggregate counts distinct int2, int4 or int8 values, Custom Scan (SiftAggRows) takes the rows instead:

  • a hash table of groups (the first spelling of a key is shown) with count, sum and avg per group (PostgreSQL’s own transition and final functions: same types and errors);
  • per count(DISTINCT x) argument, one set of (group, value) pairs with a bit for every aggregate of that argument that took the row, so FILTERs share it. The set is bounded by hash_mem: bitmaps while the values are dense in their range, a hash set when they are spread over it (a bitmap that costs over 128 bytes a value is given up for one), and once the hash set holds 262,144 pairs, or hash_mem is full, the pairs are sorted as 8-byte keys (an array of hash_mem, sorted by radix, with sorted runs on disk past it; a set past the cache misses on every pair, and the sort of 20 million pairs took half the time of the hash set in the measurement; the array is stripped of its repeats once at a million pairs and, when they were a quarter of it, each time it fills, so a column of repeats does not grow it to hash_mem; there is no switch: hash_mem bounds the sort as before) and equal pairs are counted at the end (Distinct Sets Spilled in EXPLAIN ANALYZE).

The child plan’s projection evaluates group expressions, arguments and FILTERs, so any expression is allowed. Grouping sets, ordered or DISTINCT aggregates other than count, and aggregates other than count, sum, avg keep the normal plan. Float sums and averages add in arrival order (the sort adds in id order), so last digits can differ, as between the scan’s aggregates and the normal plan’s. sift.agg_rows = off restores the sort.

Streaming count(DISTINCT x) of an integer column. When a count(DISTINCT x) scan’s sets would outgrow hash_mem and the column’s superblocks hold x ranges that overlap little (a column growing with the heap: a time, a serial id, a key loaded in order), the scan visits superblocks in order of zone minimum of x and, before each, drops from the sets the values below the minimum of the superblocks still to come: no later row can hold them, so they are not counted twice, and the group’s count already includes them. The answer is the plain scan’s exact one. The sweep runs in one process and does not spill the group table (a spill would drop pairs from the sets it dumps): when that table would outgrow hash_mem, or a unit has rows the zones do not bound (a write buffer, read first), the scan restarts in plain order. EXPLAIN ANALYZE: Distinct Stream Superblocks, and Distinct Stream Given Up after a restart. A column in random order within every superblock, two sets, or a grouping set keeps the plain scan. sift.agg_dist_stream = off restores it.

Workload shapes

Shapes of an event analytics workload (an item table with jsonb metrics, a document with an array of parts, entity and mapping tables) are in the synthetic generator test/col/workload.sql and bench/sql/workload/shapes/g*.sql (bench/sql/workload/run.sh build|plans|time; each file’s first line is the shape’s status). Regress test agg_workload_shapes, fixtures wl_*, coverage rows WL01 to WL09. Times: serial plan at 300,000 items, best of 3, with sift.enable_agg off and on.

Shape Status Off On
exists-over-parts: EXISTS over jsonb_to_recordset(expr -> 'parts') of an index expression (was a plan error, invalid attnum) pushed 1.64 s 3 ms
exists-over-parts: nested jsonpath $.parts[*] ? (@.category == "x" && @.sign == "y") pushed 1.52 s 3 ms
key-limit-and-subselect: x = ANY (ARRAY (SELECT ...)), eight keys pushed 116 ms 2 ms
fused-arms-astext-leaf: per-item arms, avg(n::float8) over a text leaf, count of the items fused 2.83 s 0.07 s
two-distincts-in-fused-arms: a second count(DISTINCT) over another column of the row arms one by one follow-up
selective-fused-arms: fused arms for a selective scan (one entity) not offered follow-up
lookup-keyed-by-join: lookup keyed by a column of another joined table pushed (regression and coverage verified)
jsonb-exists: jsonb_exists / ? on an object normal plan follow-up
jsonb-int-cast: (doc -> 'k')::int normal plan follow-up
dirty-unit-flag: one string in a typed leaf flags its unit pushed for jsonb_to_recordset columns, the flagged rows read again from the heap; other reads: normal plan, leaf named at DEBUG1 design note

What the pushed shapes do:

  • Text-valued leaves (astext) in paths and recordsets. jsonb_to_recordset(expr -> 'a') AS o(k text) reads the leaf of a list of objects whose member is declared astext. A text literal tests it for equality (=, IN) only when no number or boolean prints as that text (it does not start with a digit or - and is not true/false), so the leaf text of the number 7 is never taken for the string '7'. A number or boolean literal against such a leaf, a strict path and a NOT of a test that may be NULL keep the normal plan (plain traps in wl_path_parts_*).
  • EXISTS over an index expression’s array. The planner deparses the expression with the query’s own range table (the same expression in a sub-select’s range table is what raised invalid attnum). One element’s members are tested together, as for a column.
  • x = ANY (ARRAY (SELECT ...)). An uncorrelated, stable sub-select that cannot raise is an InitPlan; the scan runs it once at start and its rows are the leaf’s lookup table (as for a hashed sub-plan: no parallel workers). A sub-select that reads the row, is volatile or may raise keeps the normal plan (wl_any_array_subselect_*).
  • A sum of a numeric column times a value asked once. sum(amount * (SELECT count(*) FROM kinds)) and sum(amount * $1) (a generic plan) multiply each row by the value of a sub-select or a parameter, which the numeric programs of the scan (constants known when the plan is made) do not hold. The planner rewrites the aggregate to the product of the sum of the column and that value, made once for each group above the scan: numeric multiplication is exact, the display scale, NULL, NaN and the infinities come out the same. It applies to sum of a numeric product with a factor that reads no column (no correlated sub-select, nothing volatile), in the select list or the HAVING; an avg, a DISTINCT sum and a product of two columns stay as they were. The plan is taken where its cost is below half that of the plan as written.
  • A scan with a scalar sub-select that is a constant runs in workers. The sub-select that gives one row whatever the data (an aggregate with no GROUP BY, a row by its whole unique key) is run by the leader when the scan starts, and the workers get its value with the spec: a condition x > (SELECT avg(x) FROM t) or an aggregate’s argument over such a value is read by the workers as a constant. The rows of an ARRAY sub-select are the leader’s lookup table and keep the scan in one process.
  • A sum of a numeric column times a value of a table joined by its unique key. sum(e.amount * k.rate) .. JOIN kinds k ON k.id = e.kind_id GROUP BY t.region multiplies every row by the rate of its kind, which the numeric programs of the scan do per row. The kind of a row has one rate, so the rows of a kind and a group add up to sum(e.amount) times it: the planner groups the tables but the lookup by the keys of the query and the kind, adds up the amounts in the scan, joins the groups to the lookup and adds up the products (a few hundred groups where the rows are hundreds of thousands). Numeric multiplication is exact, so the display scale, NULL and NaN come out the same. It applies to inner joins, sum of a numeric product of a factor of the other tables and a value of the lookup alone, in the select list or the HAVING; a sum of a numeric column and count(*) or count(x) beside the products are added up in the sub-select as they are (the sum of the counts is cast back to the bigint, so the query has a GROUP BY: a count of no rows is 0, the sum of no counts NULL); an avg, DISTINCT, a FILTER, a float sum, a column of the lookup in a group or a condition, a column compared with a constant and a lookup that is not read by its whole unique key keep the plan as written. The plan is taken where its cost is below half that of the plan as written.
  • Fused arms over a text leaf. The inner sum/avg of a float8 expression the scan evaluates (a cast, CASE) is accumulated per row like typed leaves. The expression runs for every item the scan reads, so the arms must share one WHERE (the scan’s) and have no inner FILTER; an arm’s own WHERE keeps the arms one by one. The plain cast raises for a non-numeric score, in the scan as in the heap plan.

Differential suite

scripts/coldiff.sh referees the aggregate pushdown: it runs a corpus of plain SQL aggregate queries in one session with sift.enable_agg = off (PostgreSQL’s plan without sift), again with the pushdown on, compares both results inside the database, and reads the plans to report which queries really used Custom Scan (SiftAgg). Result equality and plan shape are reported separately, so the harness works before the planner exists (both runs then use the same plan; scripts/coldiff.sh --no-index is the harness self-check).

scripts/pgtest.sh install && scripts/pgtest.sh start       # a cluster (SIFT_TMP, SIFT_PORT)
scripts/coldiff.sh                                         # both suites, the scenarios, every variant
scripts/coldiff.sh --suite fixtures --corpus '^s7_'        # the fixture queries whose name matches
scripts/coldiff.sh --suite analytics --rows 200000         # the corpus of bench/sql/analytics on a larger table
scripts/coldiff.sh --bench --suite analytics --rows 1200000 --runs 5
scripts/coldiff.sh --selftest                              # the harness against injected differences

make check runs the jobs difftest-col (small fixtures, a subset of variants and scenarios), difftest-col-tbl (fixtures and scenarios on a sift_table, --table) and difftest-col-selftest (scripts/ci.sh); the full tier uses larger tables and every scenario. Without the column index (no sift_col access method, no sift_colstub (the planner-track stub) in a SIFT_TESTING build, or a failing CREATE INDEX) the suites print coldiff: SKIP: ... with the reason and exit 0.

Suites

suite data queries
fixtures test/col/fixture.sql: an orders table of --fixture-rows rows (default 4000, --seed), hash generated, with NULLs, skews and a fill factor that allows HOT updates; helper tables; edge-case tables (t_int, t_float, t_text, orders_odd, orders_part) test/col/fixtures/*.sql
analytics the generator of bench/sql/analytics (SIFT_ANALYTICS_DIR, --rows, --stores), its indexes and col_index.sql its queries/*.sql (--queries GLOB), the pg section of each file

Each suite has its own database (sift_coldiff_fix, sift_coldiff_ana; COLDIFF_DB is the prefix, --keep keeps them) with conventional indexes (primary key, btree, GIN, and a BM25 index on the fixture’s memo column), then the column index, then VACUUM (FREEZE, ANALYZE) (every heap page all-visible). The fixtures' index DDL is in scripts/coldiff.sh (--col-am, --col-paths); the analytics index is the statement in its col_index.sql (--col-index FILE), with USING sift_col replaced by --col-am when the stub is the only access method.

Variants

The reference run is off: sift.enable_agg = off, max_parallel_workers_per_gather = 0. Every other run is compared with it (--variants):

variant sift.enable_agg parallel workers sift.simd
off-par off 2 (free parallel costs) auto
on on 0 auto
on-par on 2 auto
on-scalar on 0 scalar
on-par-scalar on 2 scalar

off-par checks the tolerance below against PostgreSQL’s own parallel plans. Each variant also runs through EXPLAIN (COSTS OFF) to record whether the plan has Custom Scan (SiftAgg) and Gather.

Random queries (test/fuzz/diff_fuzz.py)

The command line is test/fuzz/diff_fuzz.py; the code is the package test/fuzz/diff_fuzz/: the families in fam_*.py (a generator and a renderer each), their table in registry.py, the DML in dml.py, a run in run.py, the shrinking in shrink.py.

A seed gives a small database (test/fuzz/diff_fuzz_data.sql: NULLs, duplicates, a sift_col index on each fact table, a hash partitioned table, lookup tables) and a script of episodes: a few UPDATE, DELETE, INSERT and VACUUM statements (dirty rows, write-buffer rows), then random queries of the pushdown shapes: first-row lookups, two-level aggregates with spines, partition pruning, pre-aggregates (FILTER on a cube dimension, int8 arithmetic, per-group min/max), jsonb member casts, EXISTS over element lists and jsonb_each members, UNION ALL arms, top-N, and several lookups of one table in every join kind. Each query runs as the reference and as variants (pre-aggregates, scan alone, arms alone, parallel workers, scalar SIMD, no lookup pushdown) and rows are compared by the contract below. A divergence is shrunk (query parts, then preceding statements) to a reproducer file; a dying worker or backend is reported with its query. scripts/ci.sh runs fixed seeds (difftest-fuzz, seconds), and again with --table (difftest-fuzz-tbl): the fact tables are sift_tables with their jsonb paths declared, the statements include rolled back writes, VACUUM FULL and sift.table_maintain, and the families that need an option of the column index (key_cut, order_columns, the point directory) are skipped; test/fuzz/diff_fuzz_search.sh runs a long search in capped slices.

--families having runs only the HAVING family (fam_having.py; --having-weight N adds it to the others with weight N, the default is 0, so the seeds of the other families are unchanged): conditions on aggregates (count, sum, avg, min, max, count(DISTINCT ..), aggregates with FILTER, several of them, ones the select list does not name), on the keys, mixed with WHERE, over keys and values with NULLs, groups that are all empty, ROLLUP, CUBE and GROUPING SETS with grouping() in HAVING, IN and EXISTS sub-selects, top-N, DISTINCT, a window over the groups, UNION ALL of two grouped arms, a grouped sub-query that an outer query filters, joins, and prepared statements (a PREPARE and an EXECUTE in one query, the generic and the custom plan, DISCARD PLANS between queries). It also runs with two parallel workers (hpar2, hscan2). A reference that raises SQLSTATE class 22 (a cast of a jsonb member after the DML) is a result like any other; any other error of the reference is a fault of the generator. Findings, 2026-11-06: an index scan that PostgreSQL runs again for each row of an outer query returned rows of the write buffer twice (seed 71; every release since 4.0.0, fixed in 4.0.5, 4.1.4 and 4.2, col_wbuf_lookup_rescan), and HAVING EXISTS (..) of a sub-select correlated to the key, under a WHERE with NOT EXISTS ... OR key IN (..), failed with failed to postpone qual containing lateral reference (seed 277; the 4.2 branch only).

Transactions (test/fuzz/txn_fuzz.py)

The random queries run on tables a few statements old. A read that takes the log of ended rows for the whole truth (track_changes) or a write buffer for the rows of a statement can be wrong when the command id moves inside a statement or a transaction, which no single query shows: two wrong answers of 4.0 and 4.1 (the first UPDATE of a text key in a new backend, and an UPDATE that calls a volatile function that runs SQL) were of that class. test/fuzz/txn_fuzz.py runs scripts of two sessions that write to one table t(k, tenant, amount, pad) with a sift_col index (an int or text key; track_changes default, on a key, ctid or off; rows in the write buffer or not, promoted or not, vacuumed or not, a trigger or none) and compares, after a statement, at the end of a transaction and in a fresh backend, five reads (grouped count, sum, min and max; totals; point lookups; top-N; a range) of the plain heap plan (no scan, no index) with the scan forced, the default plan, an index scan and the scan with workers.

The statements of a script: INSERT, UPDATE of the same rows several times (of an indexed column, of the key, of a column the index does not hold), DELETE, INSERT .. ON CONFLICT, MERGE, COPY, data-modifying CTEs, a volatile function and a DO block that run SQL through SPI, a block with an exception handler, SAVEPOINT, ROLLBACK TO and RELEASE, a cursor with WHERE CURRENT OF, PREPARE TRANSACTION and its resolution, READ COMMITTED and REPEATABLE READ; a second session inserts, updates and deletes rows of its own and keeps a REPEATABLE READ snapshot open while the first writes; VACUUM, sift.col_promote, REINDEX, CLUSTER, VACUUM FULL, ALTER INDEX .. SET (track_changes = ..) and reconnects between them; and a transaction that takes a REPEATABLE READ or SERIALIZABLE snapshot, lets the second session commit, runs one of those commands (or sift.col_refresh, col_merge) on the index and reads in the same transaction (bug C below: seed 18 finds it on the 4.1.1 tree). A script is made by its seed: the same seed is the same script for one version of the generator (a new statement kind moves every seed: the findings are pinned by their regress tests, not by their seeds).

The first query whose rows differ (or that raises, or hangs, or crashes the server) ends the script; the script is cut down by delta debugging (actions, then rows, the trigger, the write buffer) and written as DIR/txn_fuzz_N.sql, which psql replays, and .json for --repro. --no-spi, --no-oc and --no-truncate leave out the statements of a finding that is open, so that a run goes on to the next one (none is open: the CI jobs difftest-txn run seeds 1-6 in the quick tier, 1-8 or 9-16 and, in the nightly run, 1-400 of the full tier, with every statement).

Findings, 2026-11-06 (all on v4.1.0, the 4.1.1 branch and the 4.2 branch, all fixed): the scan counted the rows of an aborted or still open INSERT .. ON CONFLICT once another statement committed rows after them (the first write buffer record without an inserter was taken for the one before it when the set was stamped, wbuf/batches.c; a wrong answer); an index scan of the sift_col index failed with could not read blocks N..N after an aborted UPDATE and the VACUUM that truncates the heap, in the backend that wrote (its cached lookup of the write buffer listed addresses past the end of the file, scan/point.c; an error). and a transaction that took its snapshot before it ran REINDEX, CLUSTER or ALTER INDEX ... SET (track_changes) on the index missed the rows other sessions ended in between (it took its own baseline of tombstones for deletes, col/track.c; a wrong answer, isolation/col_track_own_baseline). Seeds 2 and 15 (ON CONFLICT), 192 (truncation) and 971 (the transaction that ran the DDL) found them with the generator of that day.

test/fuzz/txn_fuzz.py --shapes adds the shapes that found bugs by hand and that no single query of the checks above runs (another set of scripts for a seed; the seeds without --shapes are unchanged). A check of kind cursor declares a SCROLL cursor (WITH HOLD outside a transaction, read after the COMMIT) over a grouped read, a top-N, a point list, a semi-join or a read that runs the scan again for each row of an outer query (a correlated sub-select, a LATERAL join), and reads it with FETCH, FETCH BACKWARD, MOVE and FETCH ABSOLUTE under each plan. A check of kind prep runs a prepared statement that lives as long as the session (the plan of the sixth execution may be the generic one; plan_cache_mode is drawn too) under each plan, the parallel one included (--variants workers compares only that plan). The kinds curdecl, curfetch and curclose keep one cursor per plan open while the other session commits and the index is changed (REINDEX, ALTER INDEX .. SET track_changes, sift.col_*), read later in the transaction or after it. A third of the seeds are a hash partitioned t with one index per partition (DETACH PARTITION and ATTACH PARTITION between statements; a key update moves a row to another partition; no track_changes on a key or ctid, which a partition refuses). The statements add INSERT .. ON CONFLICT DO UPDATE that calls a function that runs SQL, one with a WHERE, an ON CONFLICT DO NOTHING whose rows call a function that inserts, a DO block with both, a COPY whose last row exists (the rows before it are aborted together), and a trigger that reads t through SPI (before each inserted row, or after the statement over its transition table). --only cursor|prep|persist runs the checks of one shape in place of the plain ones, --parts 0|3 forces the layout. --concurrently (not in the CI seeds: it changes the scripts of a seed) adds REINDEX INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY of a second index between the statements, outside a transaction block, while the other session holds its snapshot or has committed; the 4.1.3 build (be9217512) is clean over seeds 1-100 of it. The deadlocks that fix removed need writers that run during the reindex, which two sessions that take turns do not make (test/t/col_reindex_concurrent.sh covers them), and DROP INDEX CONCURRENTLY of a tracked index fails by design, so the shape leaves it out.

Acceptance, 2026-11-06 (seeds 1-80 each, a tree that is known to be wrong): on v4.1.0 --only cursor fails at seed 8, --only prep at seed 2, --only persist at seed 9, --only prep --variants workers at seed 2, and the plain checks of the shapes (upserts, COPY and triggers with SPI) at seed 2. On the 4.1.1 branch --only prep fails at seed 34, which is the first wrong answer of the track_changes change in a REPEATABLE READ transaction (ALTER INDEX .. SET track_changes after the other session committed, fixed on 4.2; shrunk to 7 actions); the same seed with --variants workers fails too. The partitioned tables found nothing on v4.1.0, 4.1.1 or 4.2 (400 seeds of the plain checks, 200 of cursors and of prepared statements): no wrong answer of a partitioned table is known. A build that prunes a partition it must read (outside_window of src/agg/part/prune.c returns true for partition 1) fails at seed 3 of --parts 3 with the cursor, the prepared and the plain checks, and txn_fuzz.py without --shapes finds nothing in 40 seeds of it. The CI job difftest-txn-shapes runs seeds 1-6, 9, 10 and 34 in the quick tier, 1-8 or 9-16 in the full tier and 1-400 in the nightly run.

BM25 queries (test/fuzz/bm25_fuzz.py)

A table pd(id, grp, body) of random sentences over a small vocabulary (English or German, with stop words and words that share a stem) has a sift BM25 index built over part of the rows, the rest inserted after it in batches, some rows deleted, VACUUM and promotion for some seeds. A script is a few statements that run the same predicate (phrase, plain and boolean queries) in a correlated subquery, a LATERAL join, an EXISTS, ANY or IN subplan, or a cursor (SCROLL, FETCH BACKWARD, rescans with MOVE) and are run under the heap plan, the index plan and a parallel plan, each in a new psql process: the first backend of a session is where a context that an earlier query left behind (the stem memo of 775018b54) is missing. The rows of the plans must be equal; a plan that raises or crashes a backend alone ends the seed. The list of statements is cut down and written as DIR/bm25_fuzz_N.sql. On the 4.1.1 tree seed 9 crashes a backend (a LATERAL over unnest of queries, five statements after the cut); the 4.2 branch is clean over seeds 1-300 of 40 statements. The CI job difftest-bm25 runs seeds 1-12 in the quick and full tiers and 1-300 in the nightly run.

The comparison contract

Each result is materialized in a temporary table and compared by coldiff.compare (test/col/run.sql):

  • columns (names, types, typmods) and row count are equal; a query needs a total ORDER BY and rows are compared by position. An analytics file has no ORDER BY but a -- key: line: both results are sorted by those columns, then by every column as text;
  • every type but floats is equal as text: integers, dates, text, booleans, numerics (scale counts: 1.50 is not 1.5), jsonb;
  • float4/float8: |x - y| <= 1e-12 + 1e-9 * max(|x|, |y|) (--abs, --rel), because parallel workers and SIMD lanes add in another order. NaN equals NaN, an infinity only itself, -0 equals 0, NULL only NULL. This is no license for float4: sum(float4) adds in float4 in PostgreSQL, so a plan adding in double differs by about 1e-7 relative and fails (query t_float4_sum);
  • a query that raises (a cast, integer or float overflow) must raise the same SQLSTATE in every variant (the message may name another row’s value); such a file says -- error: yes, and a reference run that fails otherwise is a failure.

A per-order aggregate (avg(x), sum(x) of a float under an outer aggregate) adds one order’s elements from first to last in double and divides by their count: the value PostgreSQL gets when its plan feeds an order’s rows in array order (a hash aggregate over the unnest). PostgreSQL’s own plans disagree on last digits (a plan that sorts by order and key adds equal keys in its unstable sort order). A condition on the average (avg >= 9) that holds exactly for a decimal sum (8.7 + 9.0 + 9.3 over three) can then count a few more or fewer orders in one plan than another (three of 100 000 orders between the two arms of one query on the normal plan), whatever the scan does; the scan’s count is the first-to-last one. A threshold that must be reproducible therefore uses integer values (exact sums) or leaves a margin, never the last digit of a float sum.

A differing result always fails (exit 1) and prints the first differing rows of both; nothing is silenced, and a query without a result (an unexpected error) fails too.

The shape expectation

A file states the expected plan: -- expect: agg (the pushdown must happen once implemented), plain (it must not: volatile functions, unsupported or risky semantics) or any. Only on is checked for agg; every on* variant for plain. A mismatch is a NOTE in the coverage table’s last column and in the summary line; it fails only with --strict-shape. The coverage table has, per query and set: shape, expectation, equal or DIFF, whether on used SiftAgg, reference and on times in ms, and the speedup.

Corpus format

One query per file, as in bench/sql/analytics/queries: header lines, then the query. Header lines that are not a tag below are the title.

tag meaning
-- shape: metrics-summary the shape of the query (the name of an analytics file of the form metrics-summary or whole-top-category-kind is its shape)
`– expect: agg\ plain\ | any| the shape expectation (defaultany`)
-- sets: K10 K400 K4000 the query runs once per set of analytics_sets ({stores}, {sources}, {span(col)}, {range(col)} are expanded as bench/steps/analytics.py does); - or absent: once
-- key: 1 2 sort both results by these columns first; - compares by position (the query has a total ORDER BY); absent: sort by every column
-- scenario: yes part of the subset that the scenarios re-run
-- guc: name = value a setting for every run of the file (repeatable)
-- prepare: (int[], date) and -- args: ... the body is a prepared statement with $1…; the runs are EXECUTEs (with plan_cache_mode in a guc for a generic plan)
-- requires: SELECT ... the file runs only if the statement returns true (repeatable), e.g. a collation that exists
-- wrap: subquery materialize SELECT * FROM (query), for a statement that CREATE TABLE AS does not take (FOR UPDATE)
-- error: yes the query raises an error in the reference run
-- error: either the query raises an error in the reference run and in the scan; their SQLSTATE may differ (a value the index cannot store: 0A000 against the cast error 22P02)
-- isolation: serializable the file runs in a transaction of that isolation level

The sections --@ pg, --@ flat, --@ ch of an analytics file are cut: only pg runs. -- tol: is ignored (the contract above is the tolerance).

The fixture corpus (about 180 files) covers:

area fixtures and cases
lookup joins and arms j_* (semi, anti, key expressions, ON filters); per-order arms with sets, gates and general HAVING per_order_* / t_two_level_*; mapped aggregates t_mapped_agg_*
report shapes metrics-summary: summary of an object with dynamic keys by key, mapped key and period; item-summary-rollup: summary of an array of objects with per-order pre-aggregation, the three rollup levels as UNION ALL and as GROUPING SETS; time-series-granularities: time series at day, week, month and quarter with a date spine; store-top-categories: per-store rows and top-N; notes-path-exists: jsonb_path_exists on an array of objects; items-exists-same-element: same-element filter as EXISTS, @> and jsonb_path_exists
flat aggregates and quals IN, NOT IN, BETWEEN, LIKE, OR, NOT, CASE, IS [NOT] NULL, FILTER, HAVING, grouping by expressions; NULL-heavy documents, empty sets; BM25 match with an aggregate; a prepared statement with a generic plan; numeric(p, s) columns (predicates against constants of every precision, NaN and infinities, keys, expressions, distinct counts)
limits and their plain traps one past each group keys up to 8, rollups and cubes of up to 7 columns (k_keys_*, k_rollup_*, k_cube_*; traps: 9 keys, 9 joined tables, 17 combos); lookup joins of up to 8 tables and 16 combos (j_joins_*, j_combos_*)
generic aggregates g_*: variance family, DISTINCT over one or several arguments and with a spill, ORDER BY in the call, ordered and hypothetical sets, variadic and stable-fraction forms, array_agg/string_agg, expression arguments, joined columns, rollups, user-defined aggregates, parallel combine; x_bool_*: bool_and, bool_or, every merged from two workers; traps: a volatile transition function, a float read into text with -0
other traps numeric division, products past 38 digits, float8 casts, random() in the qual and aggregate, sum(int4)/sum(int8) past the type, integer/float/cast errors, NaN, infinities, -0, sum(float4), text min/max under a non-C collation, JSON values that are not numbers; DISTINCT ON (do_*: latest and earliest rows, mixed sort directions, expressions, joins, every column type, plain traps); outer joins, a set-returning function in the select list, window functions over the aggregate (w_*), TABLESAMPLE, FOR UPDATE, SERIALIZABLE
partitioned tables pa_*: pruning at every level, a dropped column before the index, default and unlogged partitions, ONLY, partitions without the index or with other options, inheritance, run-time pruning of parameters, stable functions and hash remainders; DML runs through the parent in the scenarios

Scenarios

After the fresh run, the fixture subset (-- scenario: yes) is compared again after DML that leaves the heap in every state the column index must answer for (--scenarios, default all). They are cumulative and only equality is checked:

scenario heap state
insert 20% more rows (also of t_jp and t_eo) without a VACUUM (pages not all-visible)
update an UPDATE of indexed columns (new tuple versions) and of the jsonb documents (also of t_jp and t_eo)
delete hard deletes (also of t_jp and t_eo) and soft deletes (deleted_at set)
abort a rolled-back INSERT, UPDATE and DELETE (dead tuples before any VACUUM)
vacuum VACUUM after all that
hot UPDATE of columns no index holds: HOT updates (the count is printed)
recycle DELETE, VACUUM (TRUNCATE off), then INSERT: new rows on the TIDs of deleted ones (the count is printed)
held a second session with an uncommitted INSERT, UPDATE and DELETE next to the corpus, at READ COMMITTED and inside a REPEATABLE READ transaction; then a REPEATABLE READ snapshot taken before a writer commits
maint REINDEX, then sift.col_promote and sift.col_merge when they exist

jsonpath truth tables

The fixtures jp_* read t_jp, whose members s (text), n (int4) and b (boolean) take every value of their leaves (1 and 1.0, the empty string, non-ASCII text, 2147483647), a JSON null and a missing key. The table compares an element e with a literal L of the member’s type; a scalar or null element and a missing key count as a missing member:

member of e @.k == L @.k != L !(@.k == L) !(@.k != L)
a value equal to L true false false true
another value of the type false true true false
JSON null false true true false
missing false false true true
a value of another JSON type unknown unknown unknown unknown

Against a null literal, == null is true for a JSON null only, != null for every present non-null member, and under !() the other rows. Each form pushed down equals the heap in every variant and scenario; a form that is not provable (the last row, a number against a float leaf, strict !(), jsonb_path_match) is a plain fixture. A member the leaf cannot hold (an array or object in a number leaf, a number out of range) keeps the normal plan, as do older units or write buffers with uncertain kinds for tests that need complete kinds (guaranteed by column minor 6; REINDEX or VACUUM FULL restores the guarantee).

An element against a column of its row

The fixtures eo_* read t_eo: members q (int4), q8 (int8), f (float8) of its array against columns r4, r8, rf of the same row, with NULL columns, a NaN and a -0, a JSON null and a missing member, empty arrays and a NULL document. Element j of row i holds the value r4 has in row i + j - 1, so every row has elements equal to its neighbours' columns, plus rows that must not match. An element is compared with its own row’s column (= <> < <= > >=, either side first), NULL on either side is unknown, and the element’s other tests (AND, OR, IS NULL, a literal) apply to that element. An expression of the column, a column of another type class (int4 member against a float8 column), a non-constant LIMIT, OFFSET and a second array keep the normal plan (eo_trap_*).

Bench mode

--bench [--runs N] runs only the first pass (no scenarios) with the median of N runs (default 5, after a warm run) of reference and on, and prints go/no-go lines: K4000 queries that must push down (expect: agg, and the analytics corpus’s S shapes) at least 20 times faster than the reference; K10 and K400 ones that push down not slower (0.95). A missed target is printed and fails the run only with --strict-shape. Times include the result’s temporary table.

Self-test

scripts/coldiff.sh --selftest runs a subset of the fixture corpus plain against plain (must pass), then with --mutate KIND, which perturbs every on* result as a pushdown bug would. These must fail: int (+1), float (relative 1e-6, beyond tolerance), text, null, rows (last row lost), dup (first row twice), order (rows 1 and 2 swapped, seen by a position-compared query), type (a wider column type), cols (an extra column). float-tiny (relative 1e-11, inside tolerance) must pass; a mutation applied to no result fails the self-test.

Plans that depend on what ran before

Only the choice between a shared CTE and its inline plan, and between the flattened form of a statement and the form as written, is kept between statements (verdict.c, USAGE.md, “The choice for a shared CTE is kept per backend”); every other choice of the planner is made from the statement, its statistics and the settings alone. The choice is kept under the statement without its constants, the size class of each table, the settings that move a plan and the files of the column indexes, so a statement of other constants meets the verdict of the one that made it. It is taken only if the plan it makes reads about the rows the verdict was made for (within 1.4 times, VERDICT_BAND), else the verdict kept for that size is used or the statement is planned in full. A plan can therefore differ by history only for a statement whose two variants cost about the same at the constants of the two statements, and then only until the next plan in full: an entry is read seven times, the eighth read plans in full and replaces it, and each plan in full that confirms it doubles the reads to the next (16, 32, 64). The same holds for a verdict another backend made (sift.verdict_cache_kb). The rows are the same whichever variant is planned. SET sift.agg_verdict_cache = off turns it off for the session: every statement is planned in full and nothing is kept, so its plan does not depend on what ran before (sift.verdict_cache_kb = 0 only stops the sharing between backends).

Known plan losses against a parallel normal plan

PostgreSQL prices a parallel plan of hash joins 1.2 to 1.5 times faster than its serial plan; it runs 2.0 to 3.2 times faster (2 to 4 workers launched). The scan’s own price for workers is within 10% of what it measures, so a scan priced just below a parallel normal plan can lose to it. Three shapes of the join matrix (1,000,000 rows, milliseconds serial / with workers) do on the current tree:

shape normal plan scan priced (normal / scan, with workers)
dashboard_distinct_filter 253 / 82-101 255 / 245 39,739 / 32,494
union_of_joins 352 / 176 276 / 246 38,207 / 31,998
set_anti_not_in_1000 142 / 45 156-258 / 76 17,823 / 13,529

Compensating in the scan’s price by a factor against a parallel normal plan fixes these and moves linksemi_all_parents (a scan win of 1.85 times, 205 ms against 379) to the normal plan, so the price of each scan shape is fitted first and the normal plan’s speed is left as PostgreSQL prices it. With max_parallel_workers_per_gather = 0 the three choose by the serial prices, which agree with the times.

The scans of the join shapes of the plan gate at 250,000 rows are not priced above the time they take loading their table, so there is no price to lower before a factor on the parallel normal plan (at 30,000 rows linkleft_all_parents is: 0.92 against a time ratio of 0.35, the heap-row term of the probe of a table that fits the cache; it stays a scan win). At 250,000 rows with two workers, a scan that loads its lookup table (the caches that keep the rows of a table for the next statement off, sift.agg_lookup_cache_kb and sift.agg_lookup_shared_kb 0; median of 5, milliseconds) against the normal plan, and the price of the scan over the plan’s:

shape normal plan scan loading its table scan with the rows kept price ratio
linksemi_all_parents 49.2 49.1 51.1 0.78
linksemi_2000_parents 50.7 23.5 22.4 0.11
linkjoin_all_parents 148 133 143 0.29
linkjoin_2000_parents 87.5 27.5 25.8 0.11
linkleft_all_parents 117 54.2 24.1 0.23
linkanti_all_parents 47.2 38.9 5.9 0.14

The plan gate times the scan with the rows kept (7.0 ms against 139 ms for linksemi_all_parents), which is why it reads a ratio of 0.05 where the price, which counts the load, reads 0.78: against a plan that loads the table too the scan ties, and the price is right for it. A factor of 1.7 on the parallel normal plan (the ratio of its real speed-up to its priced one) would take linksemi_all_parents to the normal plan, a tie loaded and 7 ms against 139 kept, and linkleft_all_parents at 30,000 rows (priced 0.92, 28.4 ms scan against 81.6 native with two workers, loading) to the normal plan as well: both are scans that win. The factor stays out until the price of a load counts the rows kept for the next statement, which a plan cannot know before it runs.

The price of the heap row of a probed entry (AGG_PROBE_VISIT, the page visit that an entry of a table not in the order of its key pays) was tried with the residency PostgreSQL gives a scan, the pages index_pages_fetched expects an index scan to fetch of the whole scan’s probes, in place of one visit for each entry. It brought linkleft_all_parents to 0.395 at 30,000 rows (stock lane; tuned 0.730 to 0.302) and left it at 0.228 at 250,000 (tuned 0.343 to 0.308), but it moved the shapes that were right: linkjoin_2000_parents and linkjoin_all_parents at 30,000 rows from 0.325 and 0.367 to 0.121, and linkjoin_all_parents at 250,000 rows from 0.288 to 0.076 (tuned 0.602 to 0.159) where the scan loading its table takes 133 ms against the parallel normal plan’s 148 (a ratio of 0.90): twelve times below its time, from three times. It is discarded. The pages index_pages_fetched counts are those of shared buffers and the cache of the operating system (effective_cache_size); the cost the term prices is a row of a table the processor’s cache does not hold (700 ns an entry in random order against 260 in the order of the key, at 1,000,000 entries), and the 64 MB table of the 250,000-row shape is all cached for PostgreSQL and is past the 32 MB of L3 of the measuring host. A residency that fits both needs the processor’s cache size, which PostgreSQL has no estimate of.

How the price of a hashed NOT IN was measured

A NOT IN of a list the planner hashes (AGG_REAL_SUBPLAN, AGG_WIDE_MISS_LAT, AGG_WIDE_MISS_HALF in src/agg/cost/model.h) is priced for a call and a probe of the hash for each distinct value, and a miss of the cache for each tuple of a value seen before once the values are many. They were measured on one x86-64 host (two groups of cores with 32 MB of L3 each) with hs_fact of agg_filter_hashed_subplan_price: 1,000,000 rows of a bigint column of 1,000, 100,000 and 632,000 values in random order, 16, 51 and 199 ms (the normal plan 125 to 176 ms); the call and probe is 720 to 800 instructions an entry (68 ns) from 1,000 to 1,000,000 distinct values. The miss is written as 6.6 times AGG_HASH_MISS_NS, the memory latency the planner already uses, so it follows that constant.

A standalone probe of a table of 64-byte entries whose next key depends on the last one (a latency, not a throughput) gives, in ns a probe at 1,000, 10,000, 100,000, 316,000, 632,000 and 1,000,000 distinct values: 9, 19, 131, 295, 368 and 404 on that host, and 4.0, 4.3, 7.7, 8.9, 18.5 and 20.1 on an Apple M4 (macOS, cc -O2) (the 69 MB table at 1,000,000 values included). The M4 pays a twentieth of the miss, and the price stays that of the x86 host: on a host where a miss costs less the scan of a column of many values is priced above its time and the normal plan stays, a missed win and no loss. The call and probe is bound by the CPU: PostgreSQL’s own test of a hashed NOT IN over 1,000,000 rows of 1,000 distinct values (count(*) with few NOT IN (SELECT k FROM hs_keys) against few IS NOT NULL, serial, 12 runs) costs 45 to 57 ns a row more on the x86 host (minimum to median, the host under other load) and 28.5 to 29.3 ns a row on the M4 (PostgreSQL 18.6), about 0.55 times. AGG_REAL_SUBPLAN stays that of the x86 host. On arm64 a scan with a hashed test is priced above its time (a call and probe a half dearer, a miss twenty times dearer): the planner takes the normal plan for some statements the scan would win (a missed win) and a scan that loses is not taken because of it, so the error is never a loss.

Plan-choice gate

scripts/plangate.sh guards the choice between the normal plan and Custom Scan (SiftAgg). It builds the tables of test/plan/gen.sql (events, dims, facts, a month spine, a partitioned copy; --rows, default 30000, and --seed; rows are hashes of their number and ANALYZE reads all of them, so statistics repeat) and runs each shape of test/plan/shapes.sql three ways: native (sift.enable_agg off), chosen (the planner’s own choice) and scan (table scans off, so the pushdown runs where the query allows). Results must agree by the differential suite’s contract (floats within 1e-12 + 1e-9 of the larger), the plan chosen must be the expected one, and the scan-over-native planner cost ratio must stay in the shape’s band, so drift shows before it flips a choice. For each shape the table prints plan chosen, expected plan, ratio and band, native and scan times (median of --runs, default 3) and a verdict: win/lose when the scan is chosen and faster/slower by more than 1 ms and 20%, miss when it is not chosen but would win by as much, else tie. A wrong result, unexpected plan or out-of-band ratio fails; so does a lose, unless --warn-time (the full tier runs at 50000 rows with it: hosted runners are noisy). The quick tier runs without timings (--runs 0). Expectations hold from 20000 to 50000 rows; a shape whose plan changes inside that range expects either.

--lane tuned runs the same shapes with a tuned planner profile (seq_page_cost 100, random_page_cost 4, max_parallel_workers_per_gather 8, hash_mem_multiplier 2, work_mem 4GB, jit off). Results must agree and a lose still fails; the plan and cost band are the lane’s own, in test/plan/bands.tsv (the tuned profile prices a sequential page at 100, which moves every ratio by a regime: the stock bands do not hold), so a shape taking another plan or a ratio leaving its band fails on PostgreSQL 18, as in the stock lane (a warning on another major; quick and full tiers run the lanes: plan-gate-tuned, plan-gate-table and plan-gate-table-tuned). A band any in a lane’s rows marks a scan path that exists at some sizes only (h02 in the tuned lane): nothing to hold, nothing to miss; --update rewrites it, so restore it by hand. seq_page_cost above random_page_cost is the setting that moves the picks: it prices a scan’s heap check at the sequential price, which can refuse the scan where the normal plan is many times slower.

--table runs the same shapes on the tables as sift_table (test/plan/table.sql drops the sift_col indexes, rewrites every table and partition into the table access method and declares the typed doc paths): the results must agree as ever, the plan and cost band are the lane’s own in test/plan/bands.tsv (table, and tabletuned with --lane tuned), so a plan or ratio that leaves them fails on PostgreSQL 18. The shapes that stay on the normal plan there are the partitioned ones (h01, h03, h04, h05: a partitioned sift_table has no pushdown yet) and the three month-spine lookups (f01, f03, f04: the table’s lookup join loads its rows slowly).

To add a shape, append three lines to test/plan/shapes.sql: -- name: x, -- expect: new and the query (a total ORDER BY with a LIMIT); run scripts/plangate.sh --update and read the diff: it writes the plan chosen and a band of a factor of 2 around the measured ratio (refreshing every shape’s bands; a plan stays unless new). In another lane the same flag rewrites that lane’s rows of test/plan/bands.tsv (the plan the lane chooses, either stays, and the band around its ratio): do it when a cost change moved the ratios on purpose, and say in the commit which change it was. A band is a factor of 2 around the ratio at the size it is recorded at, 30000 rows (the default). The ratio does not hold across sizes, and the estimate is right about it: the scan costs its start and its units, the normal plan its rows, so the scan-over-native cost ratio falls as the table grows. Over 101 shapes the median is 0.134, 0.082 and 0.067 at 30000, 100000 and 250000 rows (the ratio of the measured times, 0.109, 0.061 and 0.051; the estimate over the time stays between 1 and 1.8 at each size for the classes but the point lookups, records planner-pl20-units-stock*), and the drift is the same on the builds before and after the one-unit conversion of the cost models: at 50000 rows 30 of 106 shapes were outside the 30000 bands before it and 40 after, 70 at 100000. So the bands are the size’s own: another size takes the rows LANE@ROWS of test/plan/bands.tsv (scripts/plangate.sh --rows 250000 --update, once for each lane, records them; the four lanes at 250000 rows are there and the full CI tier runs them, half a minute each), and a size with none is checked for the results and the loses only. A shape whose ratio is wrong at a size, not only low, shows as a band that does not follow its time: the estimate over the time ratio of in_expr_key doubles from 30000 to 250000 rows while the time ratio stays, 0.217 to 0.429 against 0.131 to 0.124.

Plan-estimate calibration

scripts/plangate.sh --planning holds the planning cost: the instructions to plan each of the shapes with the scan on and off, under perf stat, against the ceilings of test/plan/planning.tsv. Skipped without perf, a warning on a major other than 18.

scripts/plangate.sh --calibrate checks that the planner’s estimates rank the competing plans of a shape the way they rank when run, in instructions and not in milliseconds or an absolute cost. The 30 shapes of test/plan/calibrate.tsv (point, range, order and limit, count, group, join, aggregate; at 30,000 rows, --lane stock or tuned, serial plans) are planned each way the planner can go with EXPLAIN only (Custom Scan (SiftAgg) with the other scans off, a sequential scan, a b-tree index scan, a bitmap scan, PostgreSQL without sift, the planner’s own choice), plans of the same shape are one, and each distinct plan runs under perf stat (user instructions of the backend, three runs, the instructions of planning taken off). It prints the table and fails when the plan the planner chooses takes more than 20% and 1,000,000 instructions more than the best plan. It also lists the pairs of plans the estimates rank the wrong way round by the same margin; those with a scan are the cost model’s (sift in the last column), the others are PostgreSQL’s own. It takes 30 seconds a lane, needs perf (without it the run is skipped) and runs in the full tier (plan-gate-calibrate, plan-gate-calibrate-tuned). Parallel plans are not measured: perf -p counts the leader only, and at 30,000 rows the planner chooses no plan with workers for any of the 30 shapes in either lane (18 of them get workers only with parallel_setup_cost = 0, a plan no estimate chooses); the workers decision is held by agg_workers_few_units and measured in the options row of sift.agg_par_min_rows. A regression test that asserts a plan the planner chose on its costs keeps the gap between the two prices beside the assertion; scripts/plan-margin.sh prints it for a statement or for the plan helpers of a test (--tests), and a gap under 25% is a tie that the statistics or the runner can turn.

Today (PostgreSQL 18, 30,000 rows): no chosen plan loses in either lane. Mis-ranked pairs:

  • no pair with a scan: the scan is never ranked the wrong way round by the margin. join_two_lookups is the closest: PostgreSQL’s own plan is priced 539 and the planner takes the scan at 583 (sift.explain_why shows it against the 934 it prices PostgreSQL’s plan at with the scan on the table), and the scan takes 17.4 million instructions against 55.9. The scan forced with the other scans off is priced 645, because those settings change what the scan costs; it is not the plan the planner chooses.
  • stock lane, PostgreSQL’s own estimates: count_all index-only scan 861 against sequential 1,739 and 18.5 million instructions against 10.3; distinct_one_parent bitmap 1,017 against index 1,699 and 4.8 against 2.2; deleted_rows sequential 1,668 against index 3,069 and bitmap 1,805, and 20.6 million against 4.9 and 5.8.
  • tuned lane: 16 pairs of PostgreSQL’s plans (index scan priced below sequential at seq_page_cost 100, the sequential scan takes 20 to 60% fewer instructions): the pages of the profile, which instructions do not count; none has a scan.

An aggregate over an expression of a column of the table and a column of a lookup (sum(f.amount * b.set_id) over facts_b joined to dim_b and dim_c, numeric amounts) is evaluated by PostgreSQL for each distinct pair of the two values by the scan and for each row by the normal plan, and is the one place where the estimates of the two plans are far from the instructions. dimexpr_dim_c, dimexpr_two, dimsum_two, dimrate_one and dimrate_two hold it at the gate’s sizes (an amount for about three rows stays on the normal plan, a rate of 40 values takes the scan). At 300,000 rows the instructions of the two plans and the planner’s prices, as the distinct amounts fall (one pair of values for each row at the top, a few hundred at the bottom):

distinct amounts normal plan, M instructions scan, M instructions scan priced / normal priced
100,000 1,157 1,434 79,452 / 13,227 (6.0)
30,000 1,156 1,230 75,136 / 15,727 (4.8)
10,000 1,156 909 78,633 / 15,727 (5.0)
3,000 1,157 501 26,329 / 13,227 (2.0)
100 1,154 192 the scan is chosen

The scan costs 190 million instructions for the 300,000 rows and 4,000 to 5,000 for each distinct pair (a hash insert, the lookup columns and two numeric calls); the normal plan 3,800 for a row, and its price is a fifth of that in the units the scan is priced in (PostgreSQL prices a numeric call as one operator, a hash join and a numeric hash aggregate at a fraction of what they run). The crossing is at about 15,000 distinct amounts (interpolated between the 10,000 and 30,000 rows of the table); the planner crosses between 100 and 1,000. Two changes to the price were measured on the lanes and not kept: pricing the numeric calls of the slots of a join’s combo as real_cost_walker does for an ON clause (the scan 6.0 times the normal plan at the top, as before), and a credit for the normal plan’s per-row evaluation of those expressions and of the numeric sum (plain_credit skips the combos of a join and the sums of a dictionary of values). Together they choose the scan from 3,000 distinct amounts down, as measured, in the stock, tuned and table lanes. In the table lane with the tuned profile at 250,000 rows (sift_table, seq_page_cost 100) the same shapes then take the scan and lose, 1,287 ms against 933 ms for dimexpr_two (the plan’s ratio 0.769; 1.298 without the change, where the scan takes 1,070 ms against 769 ms): the price of the scan in that lane does not follow its time either, and the change shows it. Capping the credit at half of a path’s price keeps that lane on the normal plan (ratio 1.004, no margin) and the stock lane at 3,000 distinct amounts on the normal plan too (22,168 against 14,844), so it moves the crossing from under 1,000 to between 1,000 and 3,000 distinct amounts, of the 15,000, and was dropped. The prices stay as they were until the scan’s price in the table lanes follows its time. In the lab data that raised the gap the pairs are 90,000 of 300,000 rows because the amount and the kind go together; the estimate of the entries (combo_entries) is the product of the two counts, 285,000, and the scan there takes 571 million instructions against 1,231 for the normal plan, priced 75,235 against 12,803: only the statistics of the pair, which the planner does not read, would show it.

RIGHT JOIN and FULL JOIN of the table to a lookup (dimright_dim_c, dimfull_dim_b, dimright_filtered; the lookup rows that nothing points to are groups too) cost the scan 600 instructions more for each row than the same inner join, and were priced above the normal plan with it. The cost was not the preserved side: an aggregate of a column of the scanned table, under a join that preserves rows for the other table, carries the join’s nulling mark, so it did not match the column and went the way of an expression (the mapped path: a combo slot, and an id from a hash table for each distinct value of the column, 200,000 for a unique count(f.id)). The unit the unmatched rows join to has only NULL leaves, which the kernels of a column count and add as they find them, so a column the join nulls is matched without the mark and runs on its own kernels. 200,000 rows and a lookup of 20,000, count(f.id) by a column of the lookup, user instructions of the backend and shared blocks, no worker:

join normal plan scan before scan after
RIGHT JOIN 358.0 M, 2,333 195.7 M, 289 61.7 M, 289 (chosen)
FULL JOIN 356.9 M, 2,333 195.6 M, 289 61.4 M, 289 (chosen)
the same with JOIN 357.9 M, 2,336 49.5 M, 226 49.5 M, 226

sum, min, max, avg and count of a column take the same path (sum(f.counter) 111.6 M before, 62.8 M after; a column with few values 75.8 M before, 60.9 M). Test agg_preserved_leaf_aggs. The shapes are in the gate as either, with the bands of the scan chosen.

A table of four times the rows of the fact table (links_b, 60 entries for each parent, a b-tree on (parent_id, status)) joined by the column the fact table is grouped by, to 200 parents, 2,000 parents and all of them, as a join that multiplies the rows, EXISTS, NOT EXISTS and LEFT JOIN (shapes linkjoin_*, linksemi_*, linkanti_*, linkleft_*; 250,000 rows, 1,000,000 entries, stock lane, instructions with the planning included):

shape normal plan priced scan priced normal, M instructions scan, M instructions chosen
join, 200 parents 24,893 2,143 757.0 48.7 scan
EXISTS, 200 parents 16,134 1,735 89.3 53.6 scan
NOT EXISTS, 200 parents 16,119 751 85.3 52.6 scan
LEFT JOIN, 200 parents 25,474 1,742 628.1 45.2 scan
join, 2,000 parents 35,057 14,625 733.3 325.8 scan (before: 44,997, normal plan)
EXISTS, 2,000 parents 25,578 10,361 797.2 193.7 scan
NOT EXISTS, 2,000 parents 27,360 3,642 739.0 190.2 scan
LEFT JOIN, 2,000 parents 29,548 10,950 407.9 203.4 scan
join, all parents 91,812 89,766 5,101.0 1,191.3 scan (before: 339,790, normal plan)
EXISTS, all parents 31,178 31,221 1,081.9 53.9 scan (a tie of the prices)
NOT EXISTS, all parents 43,667 26,767 1,230.5 48.1 scan
LEFT JOIN, all parents 38,811 59,796 2,196.1 217.2 scan (before: normal plan)

All twelve are chosen right now (plangate --calibrate: no known wrong in the family of 23 shapes of links and lookups). Two changes made it so, and both use what the planner knows:

  • The fan of a join that multiplies the rows is priced from the rows the join adds beyond the fact table (the estimate of the join’s rows against its larger input), the bound columns of the scan and the aggregate’s argument. A joined row takes about 55 instructions with a count(*) grouped by a column of ten values, 260 with sum of a column of the lookup, and 1,300 where a column of high cardinality gets a slot of the hash table for each value; the price was one constant (120 operators, and 360 for the rows of a slot) fitted to the last. It is now AGG_FAN_ROW for the row, AGG_FAN_KEY for each bound column (AGG_FAN_KEY_WIDE when the column has more than a quarter of the joined rows as distinct values, from its statistics), AGG_FAN_ARG for an argument of the lookup and AGG_FAN_FLAG for the flag of an EXISTS; a scan that carries an expression slot keeps the price fitted before, as the statements that fitted it are in the gate (dimexpr_*).
  • The normal plan of a join that multiplies the rows is priced for the rows it hashes and aggregates: its AggPath carries join_agg_extra (the joined rows beyond the larger input, at AGG_JOIN_NS nanoseconds of the model’s own unit), as hash_agg_extra does for a hash aggregate. PostgreSQL prices the rows of a hash join but the aggregate over them at the group count, and a LEFT JOIN over all parents is the shape where the scan’s whole-load price (59,796) alone is above the normal plan’s (38,811) while the normal plan takes ten times the instructions. The extra counts the rows that joins of tables add: the rows of a function (100 for each call of jsonb_each_text where a document has three keys), a sub-select or a CTE are PostgreSQL’s estimate and the scan does not charge them either (a CROSS JOIN LATERAL jsonb_each_text(doc) under a CTE that two scalar sub-selects read added 2.0 million phantom rows, 81,800 units, and turned the choice of the scans into the shared plan: test agg_cte_scalar_consumers).

The same cost on 16 shapes of tables of 1,000,000 rows with about 333 entries for each parent (the join, EXISTS, NOT EXISTS and LEFT JOIN of 20, 200, 3,000 and all parents; the large-table joins of the keyed-load cases): the join over all parents (scan 6,712 M instructions against 27,700 M for the normal plan, priced 70,240 against 385,241 before) and the EXISTS of 3,000 parents (449.5 against 866.9 M, priced 32,550 against 31,885 before) were chosen as the normal plan and are the scan now; 15 of the 16 choose the scan. The prices of the joins at 20 and 200 parents fell by the same step (a join of 200 parents 26,120 to 5,031, the plan taking 452 M against 2,022 M) and the choices there did not change. The one that keeps the normal plan is the EXISTS over all parents, a tie of the prices (the scan priced 32,475 against 31,713; it takes 1.8 times fewer instructions). The prices in the table above are the two sides of sift.explain_why; the normal plan’s join_agg_extra is added to the path itself, which is how the LEFT JOIN over all parents is chosen as the scan. No choice that was right in the eight lanes of the gate moved.

The price of a lookup table read whole from its heap (AGG_LOAD_KEY for a table kept as keys, AGG_LOAD_ROW for whole rows, the pages apart) was 10 and 20 operators for a row; the instructions of the load, counted with the lookup cache off and the planning taken off, are 393 a row for the keys of the EXISTS (120,000 rows read, 8,000 kept), 408 for the whole rows of the LEFT JOIN and 520 for the join (24,000 kept): a row costs the same whatever it keeps, and 16 to 24 instructions an operator of the scan’s own price. At 33,000 instructions for each priced unit of the plans they are compared with (the normal plan of the EXISTS over all parents at 30,000 rows: 3,742 units, 125 million), 120,000 rows of 10 operators are 3,000 units for 47 million instructions: the scan was priced as the plan that takes 3.3 times the time (15 ms against 4 ms forced, the lookup cache off). The constants are 7 and 9 now: a column index read whole from its leaves (AGG_LOAD_ROW_COLS, 8) stays dearer than the heap by the order of the lookups of agg_join_order, which want the table that is dear to load as the one the scan reads. 30,000 rows, stock lane, and the lane with seq_page_cost 100:

shape lane normal plan priced scan priced before scan priced after instructions, normal / scan
EXISTS, 2,000 parents stock 3,897 3,959 3,122 131.6 M / 3.6 M
EXISTS, all parents stock 3,742 3,948 3,111 125.6 M / 3.7 M
LEFT JOIN, 2,000 parents stock 4,544 7,104 4,035 260.7 M / 22.7 M
LEFT JOIN, all parents stock 4,389 7,100 4,031 254.3 M / 22.9 M
LEFT JOIN, 2,000 and all parents tuned 5,455 9,238 6,169 (chosen with the normal plan’s extra for the joined rows) 287.4 M / 22.8 M

These four choices were wrong by 12.6 to 36.9 times the instructions and are right now (--calibrate, four lanes: no link shape is wrong). What is left wrong in the table is PostgreSQL’s own plans, recorded as known with the reason in test/plan/calibrate.tsv: dimexpr_few_sets (stock, 30,000 rows: the hash join is priced 188 against 274 for the index plan, 1.9 million instructions apart) and dimexpr_dim_c (tuned, 250,000 rows: seq_page_cost 100 turns away from the sequential plan, 1.35 times the instructions).

The price of a lookup table read by the btree for the keys of an IN list (the keyed load, AGG_PROBE_KEY, AGG_PROBE_ROW, AGG_PROBE_IOS; sift_agg_keyed_ops) was 30 operators a key and 150 an entry, 75 less for an entry the covering btree gives on an all-visible page, against 9 for a row of the table read whole (AGG_LOAD_ROW): the load had been recounted to 9 and these had not, so the keyed read lost to the whole read at a tenth of the rows of the table where the two take the same at a half. Measured as the difference of the two reads of the same statement (the keys of a list are matched in the scan in both, 5,000 to 8,000 instructions a key, so the difference leaves them out), 48 pairs: tables of 50,000, 150,000 and 500,000 rows with a unique key and with 20 rows to a key (scattered over the heap), 1%, 10% and 50% of the keys, never analyzed, analyzed and vacuumed, lookup cache off, user instructions of one backend by perf stat and the median of three warm runs:

whole read, a row keyed read, an entry keyed read, a key covering btree, all-visible, an entry
instructions, never analyzed / analyzed / vacuumed 1,094 / 1,016 / 933 2,192 / 2,110 / 2,092 52 / 393 / 391 1,139 (vacuumed; 2,503 analyzed: the heap)
time 87 to 100 ns 151 to 211 ns not separable from the entries
operators (a model operator is 111 instructions or 10 ns) 9 (AGG_LOAD_ROW) 18 (AGG_PROBE_ROW) 4 (AGG_PROBE_KEY) 9 less than the heap entry (AGG_PROBE_IOS)

The keys of a list are in order: the descent from the root is shared by the keys that fall on a page, so a key costs 0.4 thousand instructions at most (it was priced at 5,000). The pages of the heap the entries visit stay at the price of heap_visit_ops. A table of another access method (sift_table) gives its row through the access method for each entry: 19,300 instructions and 1.6 us an entry fitted over 10 reads (150,000 rows; the table read whole 2,200 instructions and 227 ns a row), which is AGG_PROBE_ROW_AM (150, as it was) and no entry from the btree alone. The crossover surface (keyed less whole, mean of the three states; the planner’s choice in each state, K for the keyed read and W for the table read whole):

rows rows a key keys entries (share of rows) keyed less whole: M instructions ms chosen A0 A1 V
50,000 1 500 500 (1%) -55 -5.6 K K K
50,000 1 5,000 5,000 (10%) -40 -4.3 K K K
50,000 1 25,000 25,000 (50%) +7 -2.9 W W W
50,000 20 25 500 (1%) -58 -6.4 K K K
50,000 20 250 5,000 (10%) -48 -5.6 K K K
50,000 20 1,250 25,000 (50%) -3 +2.9 W K K
150,000 1 1,500 1,500 (1%) -149 -15.1 K K K
150,000 1 15,000 15,000 (10%) -105 -18.3 K K K
150,000 1 75,000 75,000 (50%) +28 -25.5 W W W
150,000 20 75 1,500 (1%) -149 -14.6 K K K
150,000 20 750 15,000 (10%) -118 -12.7 K K K
150,000 20 3,750 75,000 (50%) +8 +7.8 W K K
500,000 1 5,000 5,000 (1%) -503 -50.7 K K K
500,000 20 250 5,000 (1%) -496 -45.0 K K K
500,000 20 2,500 50,000 (10%) -392 -33.9 K K K
500,000 20 12,500 250,000 (50%) +29 -10.4 W K K

The two reads take the same instructions at 50% of the rows, so the planner takes the btree up to about half the table (the model’s own crossover is 55 to 65% with the pages of the heap added to the whole read); the choices wrong by the time or by more than 5% of the instructions were 21 and 16 of 48 before and are 8 and 0 after, all at half the rows, where the two take the same within 30 ms (total loss against the best read 2,702 M instructions and 357 ms before, 117 M and 118 ms after). A table never analyzed has no statistics for the rows a key has and its unique key is known: for a key that is not unique the entries of up to four keys of the list are counted in the btree (256 at most for each, the entries only, no heap row), where the keyed read was refused (the table was read whole then: 20 rows a key, 150,000 rows, 750 keys, 309 M instructions against 181 M by the btree). Test agg_lookup_keyed_crossover pins both sides of the crossover for a unique key and for 20 rows a key in the three states.

The price of a correlated LIMIT 1 child that the scan probes in its btree (AGG_PROBE_RUN, AGG_PROBE_COLL, AGG_PROBE_SCAN, AGG_PROBE_FETCH; child_probe_ops) was 800 operators for a run and 42 and 110 for an entry, in operators of 15 instructions, while the table it competes with is read whole at AGG_LOAD_ROW (9 operators of 111 instructions) a row. The load of the first-row table was priced by AGG_FIRST_ROW (0.1), in cost units for the choice and added to a cost counted in operators; it is AGG_LOAD_ROW now, in both. The probes lost the choice to the table read whole from about a twentieth of its rows as keys, where the two take the same instructions at a fifth. Measured on tables of 100,000 rows (30,000 and 300,000 give the same figures within 3%), the lookup cache off, user instructions and cycles of one backend by perf stat (median of three warm runs), as the slope over 1,250 to 20,000 keys of the statement with the child less the statement without it:

key probe: instructions probe: cycles table read whole, a row: instructions / cycles probes win up to (instructions)
integer, date 5,700 2,600 820 / 460 a 5.5th of the rows
text, C collation 6,900 4,400 980 / 520 a 5.5th
uuid 7,700 6,100 no table read whole
text, ICU collation 12,000 4,800 980 / 525 a 12th

The entries a probe examines apart from the first (10 entries a key against 1, 5,000 keys): 317 instructions and 130 cycles in an Index Only Scan on an all-visible page, 732 and 325 with the heap fetch of each. An operator being 111 instructions: AGG_PROBE_RUN 55 (the integer and the C text at 51 and 62, the uuid 69), AGG_PROBE_COLL 1.9 (a column of another collation than C, the one factor), AGG_PROBE_SCAN 3 (2.9) and AGG_PROBE_FETCH 7 (6.6). A table of another access method (sift_table) has no visibility map, so every entry of the probe, an Index Only Scan too, is a fetch of its own through the access method: AGG_PROBE_ENTRY_AM 285 (2.8 us and 14,000 instructions over a probe of a heap table, 50,000 probes of a 1,000,000-row table), and the normal plan, which fetches the same entries, wins unless its row count says otherwise (test agg_probe_table_am). The page search compares an integer, a date, a timestamp, a uuid and a text under the C collation inline, so no table of types follows; a uuid or a text takes more cycles than its instructions say (the keys of a list are not near each other in the btree), which the one price does not carry. The choice at 60 points (integer, date, text under C and under ICU; 30,000, 100,000 and 300,000 rows; a eightieth to a fifth of the rows as keys; the uuid has no table read whole to choose against):

wrong by instructions wrong by time instructions lost time lost
800 / 42 / 110 operators, AGG_FIRST_ROW 0.1 21 of 60 23 1,470 M 240 ms
55 / 42 x 1.9 / 3 / 7, AGG_LOAD_ROW 0 3 0 24 ms

The three left wrong by time are a fifth of a 300,000-row table (integer, date, text C): the table read whole takes 5 to 6% fewer instructions and 15 to 18% more time there (the load walks the heap in order, the probes keep their pages in the cache), a gap under the 25% of a tie. The pair of statements that pin both sides, and the number of keys between the integer’s crossover and the ICU text’s where they choose differently, are agg_probe_run_crossover.

What stays wrong or unmodelled:

  • The cache misses of the lookup into the entries (three for each fact row at 1,000,000 rows, 96 instructions for each joined row) are not in the price; the difference to the measured scan is under the margin of the choices above.
  • In the joins at 200 parents the best plan is the nested loop of index scans (61.6 million instructions, priced 30,402 against 24,862 for the hash join PostgreSQL chooses at 785): a wrong choice among PostgreSQL’s own plans that no price of the scan changes.
  • The warm planning of a NOT EXISTS that the scan keeps as the plain form costs what the native planner costs: PostgreSQL’s eqjoinsel compares the most common values of both join columns, so it grows with default_statistics_target (the plan gate analyzes at 1000: linkanti_all_parents plans in 31 million instructions, and in 1.75 million at the default 100 against 1.37 million for the probe form). The probe rewrite (a lateral LIMIT 1 child, sift_probe) is planned instead only where it is cheaper by price, and it has no such join to estimate. At 30,000 rows and 1,000 values, with the lookup cache off, one statement takes (planning + execution): probe form 0.5 + 6.9 ms, plain form 3.8 to 4.7 + 4.5 ms, native 4 + 10 to 14 ms; at the default target the plain form’s planning is 0.4 million instructions above the probe form’s and it wins by 2.4 ms of execution. The ceilings of linkanti_* in test/plan/planning.tsv are those of the plain form.
  • At 20 parents the two plans take the same instructions (20.0 to 38.8 million against 22.6 to 36.4) and the time is the planning of the scan, 1 to 2 ms, which the 20% and 1 ms limit of the gate reads as a loss on some runs: those shapes are measured by the benchmark cases of the keyed load, not held here. The thin shapes (linkanti_2000_parents and the other 11) take at least nine timed runs (-- runs: 9) and the median.