# Aggregate pushdown on column indexes (SiftAgg)

Plain SQL over a table with a `sift_col` index (or a `sift_table` table) runs on the column data when the planner proves the result equals the normal plan's; anything else keeps the normal plan. `sift.enable_agg` (default on) switches the matcher; `scripts/coldiff.sh` runs every query both ways and compares (docs/AGG.md).

Files:

| Path | Role |
|---|---|
| `src/agg/plan/`, `src/agg/plan_*.c` | shape matcher, cost |
| `src/agg/spec.[ch]` | what the planner proved, serialized into `custom_private` |
| `src/agg/exec/` and neighbours (`agg_int.h`, `unit.c`, `update/`, `init.c`, `par/`, ...) | executor, EXPLAIN |
| `src/agg/kern*.c` | vectorized operators |
| `src/agg/colsrc.h`, `src/agg/colsrc.c` | the seam to the column data and its provider registry |
| `src/agg/col.c` | provider over the `sift_col` index |
| `src/agg/pre.[ch]` | the pre-aggregates of a unit as the seam's records, shared by both providers |
| `src/table/src.c` | provider over a `sift_table` table (docs/COL-TAM.md section 6) |
| `src/agg/stub*.c` | test provider, the `sift_colstub` index AM (`SIFT_TESTING` builds) |

## What matches

Hook: `create_upper_paths_hook`, stages `UPPERREL_GROUP_AGG` and `UPPERREL_DISTINCT`. A `SELECT DISTINCT` of a plain scan is a group-by with no aggregate; `DISTINCT ON` is a group-by of first-row aggregates (docs/AGG.md).

Query shape: one relation (no inheritance, tablesample or row security) with an index of a provider; at most one lateral unnest of an indexed jsonb array or object (`jsonb_to_recordset`, `jsonb_array_elements`, `jsonb_array_elements_text` of an array of strings, `jsonb_each`, `jsonb_each_text`); inner joins in the FROM list flattened; no DISTINCT ON in the aggregating query. A window function of the same query level (`sum(sum(x)) OVER (ORDER BY month)`, `rank() OVER (ORDER BY count(*))`) is planned above the grouping stage and does not change it.

| Part | Supported |
|---|---|
| Columns | an indexed attribute or index expression (`equal()` match), typed jsonb paths (`->`, `->>`, `#>`, `#>>`, casts) as the index declares them, `jsonb_array_length` of an indexed array (a key, a filter or an argument), the columns of the unnest |
| Quals | `= <> < <= > >=` against constants/params, `IN`/`NOT IN`, `IS [NOT] NULL`, `AND`/`OR`; on `numeric` columns and numeric expressions the same operators, `BETWEEN` and `IN` lists of constants (see Numeric); any other boolean expression of ONE dictionary/integer column becomes a lookup table evaluated by PostgreSQL on each distinct value; an expression of up to eight leaves that has no kernel (division, `%`, functions, casts, `numeric` against float8) is evaluated by PostgreSQL per distinct tuple of values ("Expressions PostgreSQL evaluates") |
| Text search | `col @@> q` conjuncts of one BM25 index (`q` a constant, a parameter or a stable function of them): the index evaluates them to the match set, the scan keeps only those rows of its units and aggregates from columns; see `docs/COL-SEARCH.md` |
| Top-N | `WHERE ... [col @@> q] ORDER BY <index columns> LIMIT n` without groups: bounded heap over the rows that pass, zone-map skipping and early stop, heap read for the winners; `sift.enable_topn`, see `docs/COL-SEARCH.md` |
| Existential tests | one array of objects, same-element semantics, usable in `WHERE`, in `FILTER` and under `AND`/`OR`/`NOT`: `[NOT] EXISTS (SELECT 1 FROM jsonb_to_recordset / jsonb_array_elements / jsonb_each_text ... WHERE <element quals>)`; `jsonb_path_exists` / `@?` with a lax `$[*] ? (...)` of `@.key == < <= > >=` against a string (`==`), integer or boolean literal joined by `&&` `\ | \ | `; `col @> '[{"k": v, ...}, ...]'` and `'[...]' <@ col` with string, integer and boolean members; on an array of strings (`tags[] text`) `col ? 'k'`, `col ?\ | array[..]`, `col ?& array[..]` and `col @> '["a", "b"]'`. The element quals are the row quals above over the array's leaves; an element leaf compared with a stored column of the same row of the same type class (`WHERE i.qty = o.tenant_id`) is the element vector against the row's value broadcast. The test is true when some element passes |
| Group keys | up to 8 (7 with grouping sets, whose set number is the eighth key): a column, or any expression of one dictionary/integer column (`date_trunc`, `lower`, `CASE`, ...) evaluated per distinct value; a `numeric(p <= 18, s)` or `int8` column, or a numeric expression (`round(price, 1)`, `price * qty`, `floor(x)::int8`, and those PostgreSQL evaluates: `price % 7`, `round(price / 3, 2)`); a float4/float8 key is one group per float value as `float8_eq` has it (see Grouping sets and float keys) |
| Grouping sets | `GROUPING SETS`, `ROLLUP`, `CUBE` (any mix, `GROUP BY DISTINCT`) and `GROUPING()`: the sets are what PostgreSQL expanded, all aggregated in the one scan (a row is filtered, decoded and unnested once, then added to the groups of each set); the NULLs of the sets and the `GROUPING()` bits are the planner's own |
| Aggregates | `count(*)`, `count(x)`, `count(DISTINCT x)` (dictionary/integer columns), `count(DISTINCT pk)` at element level (distinct parent rows, proven by a unique NOT NULL column), `sum`/`avg` of int2/int4 and of float8 (also float4 for `avg`; `sum(float4)` is generic), `sum`/`avg` of float8 expressions built of `+ - *` over leaves (`sum(qty * (price - discount))`), `sum`/`avg`/`min`/`max`/`count`/`count(DISTINCT)` and `sum`/`avg(DISTINCT)` of a `numeric(p <= 18, s)` column or numeric expression (`sum(price * qty)`, exact, see Numeric), `sum`/`avg`/`percentile_cont` of a `numeric(p <= 15, s)` column cast to float8, `min`/`max` of numbers and dates, `sum`/`avg`/`min`/`max`/`count` of an expression PostgreSQL evaluates ("Expressions PostgreSQL evaluates"; the integer ones and the `DISTINCT` forms are the generic aggregates of PostgreSQL itself), `percentile_cont(c)` / `percentile_disc(c) WITHIN GROUP (ORDER BY x)` with a constant fraction in [0, 1] (x: a float8 expression as above; for `percentile_disc` also a float4, int2, int4 or date leaf), each with `FILTER`; every other aggregate with a non-volatile transition function, run by PostgreSQL's own functions (Generic aggregates); the same over the columns of a lookup join (below) |
| Above | `HAVING`, projection, ORDER BY, LIMIT, and window functions (of the same query level or an outer one) stay in the normal plan on top of the custom scan |
| Pre-aggregates | units whose rows are all visible are answered from what the index stored with them (`sift.agg_preagg`, `Units From Pre-aggregates`): the groups of one column, and at element level any subset of the dims of the unnested array or map (`GROUP BY e.kind, e.flag` with `count(*)`, `sum(e.n)`, `avg`, `min`, `max` of its numeric leaves; tests `=`, `<>`, `IN`, `IS NULL`, `LIKE`, `OR` on the dims; zone comparisons on the rows; `jsonb_each` keys), from the element rollups of column format minor 4, refreshed on demand (`docs/COL-PREAGG.md` 2, 6 and 7). A test on a leaf that is not a dim, `count(DISTINCT)` and high-cardinality keys scan the units |

Unproven means no path; the query keeps its plan. Not matched: volatile expressions and transition functions, an expression of more than eight leaves, columns outside the index, joins that are not lookups of the kind below, a `UNION` of aggregates in one pass (see Shared CTE), nested aggregation of the unnested rows, and an `@@>` that is not a top-level `AND` conjunct (`docs/COL-SEARCH.md`, Limits). Refusals are listed once per family: "What keeps the normal plan" (expressions), "Generic aggregates", "Stays on the normal plan" (joins, per-order form).

### Lookup joins, several leaves, per-order aggregates

- **Lookup join**: `[LEFT] JOIN small ON small.k = <leaf>` (one to three equalities of dictionary/integer/date leaves and table columns, up to 32 tables and 128 combos); `EXISTS` / `IN (SELECT ..)` (semi join: the row stays once however many rows match); `NOT EXISTS` (anti join); a key that is an expression of leaves (`small.k = lower(o.source)`); the rest of an `ON` that is no key (`ON small.k = o.x AND small.flag`, `AND o.y > small.min`) is a filter of the combo (it decides the match of a `LEFT` join, acts as a `WHERE` for an inner join); a `WHERE` on the joined columns of an outer join (evaluated after the join, so an unmatched row meets NULLs). At least one equality is the key; a join with none stays. The table is read at executor start with the query's snapshot, matched per distinct tuple of leaf values and cached across units.
  - Exact: a matching row replicates the input row (a non-unique table needs no proof), a NULL key matches nothing, text compares as `=` (deterministic collations), unmatched rows are one row with NULLs (`LEFT`) or gone (`INNER`). Mapped columns are keys, filters and `CASE`/`coalesce` operands like leaves.
  - Aggregates of joined columns: `count`, `count(DISTINCT)` (hashable types), `min`, `max` (btree-ordered types), `sum`, `avg` (`numeric`, `float8`) of an expression of them are taken per matching row: `NULL` for an unmatched `LEFT` row (an `INNER` join drops it). Distinct values are kept once and compared (the type's comparison function) or added (`numeric` through its own functions, so the result scale is the normal plan's) when the scan ends.
  - `sum` and `avg` of `int2`/`int4`/`int8`, `interval` and `avg(float4)` run PostgreSQL's own `pg_aggregate` transition and final functions on the distinct values and their counts (`SAA_M_PG`: the state is copied and added `count` times as `nodeAgg` would, strict inputs skipped), so type and scale are the normal plan's.
- **Several leaves**: a key or filter over up to eight leaves (of the order and its items: `CASE WHEN o.lang = 'en' THEN i.category ELSE ... END`) is evaluated by PostgreSQL per distinct tuple, cached across units. `x->>'k' IS [NOT] NULL` on a leaf of another type is the null test of that leaf.
- **Arrays of strings and lengths (3.1)**: `tags[] text` has one unnamed child, the element. `jsonb_array_elements_text(col->'tags') t` groups, counts and filters on `t` like a field of an array of objects; `col->'tags' ? 'a'`, `?|`, `?&` and `@>` test the elements. `jsonb_array_length(col->'tags')` is an int4 leaf from the array's element counts: NULL where the array is absent; a JSON null counts as absent where the normal plan raises; a scalar or an object raises the overflow error, as does a number or boolean among the strings (the hint turns the pushdown off). Test `agg_jsonb_array`.
- **The text of any scalar (3.1)**: a path declared `astext` (`scores.* astext`, `items[]{score astext}`) holds what `->>` gives for a string, a number (as `numeric_out` prints it) or a boolean. `col->>'k'`, the `value` of `jsonb_each_text`, `jsonb_array_elements(...)->>'k'` and the `text` columns of `jsonb_to_recordset` read it, so text expressions run once per distinct text, as on a `text` leaf. The flag is in the index definition (`SIFT_COLD_ASTEXT`). It is never used for `@>`, `?`, jsonpath or `jsonb_each`, where `"12"` and `12` differ. An object or array at the path raises the overflow error. Test `agg_jsonb_astext`.
- **Key of a free key object**: `(o.metrics->>'m_total')::float8` next to the unnest, with `metrics.* float8` in the index, is a row-level leaf built per unit from the elements whose key is `m_total` (NULL for rows without it).
- **Per-order aggregates**: `SELECT .. avg(x) .. FROM (SELECT o.id, keys, agg(..) AS x .. GROUP BY o.id, keys) GROUP BY keys`, also over a `UNION ALL` of up to four such arms (rollup levels over a shared CTE), each arm grouping by the unique id and by outer keys (or a NULL constant). The scan keeps the current row's inner aggregates (`count`, `sum`, `avg`, with `FILTER`) per outer group and, when the row is done, adds them to the outer `count(*)`, `count(DISTINCT id)`, `avg`, `sum`.
  - **WHERE and HAVING**: an outer WHERE is taken when PostgreSQL pushed all of it into the arms. Quals on the keys filter an arm's rows; quals on inner aggregates are the arm's HAVING (a cell that fails it adds nothing to the outer aggregates). A conjunct that is not `inner aggregate <op> constant` (`sum(x) > 2 * count(y)`, an `OR` of aggregates, a function of several) is a boolean expression PostgreSQL's evaluator runs per cell. A `pseudoconstant` qual left above the arms (`WHERE (SELECT ...) > 3`, `now() > ..`, a stable function of constants) is a gate evaluated at scan start that turns the arm off or on.
  - **Grouping sets** (`ROLLUP`, `CUBE`, `GROUPING SETS`, `GROUPING()`) in the aggregating query run in the same scan: a cell is added to the group of each set, keyed by the set number as for flat sets.
  - **Cell keys**: an arm may group by more than its id and the outer keys (`GROUP BY o.id, category, kind` under `GROUP BY category`, or under no `GROUP BY`: `SELECT count(DISTINCT id), sum(n), count(*) FROM (..)`). A row then has several inner groups (cells) in one outer group; the arm's other group expressions are the cells' keys, shared by arms with an equal expression, after the outer keys and before the set number (`Cell Keys` in EXPLAIN). A group never has them (the scan emits NULL). Outer aggregates take every cell (`count(*)` counts cells, `count(DISTINCT id)` the rows, `sum` and `avg` of an inner aggregate its cells' values) and an arm's HAVING decides each cell. A global `count(DISTINCT id)` over the arms, or an outer key set smaller than the arms' group expressions, needs them: an outer group cannot index two inner groups of one row.

## Ordered-set aggregates

`percentile_cont` and `percentile_disc` keep each group's values exactly and pick ranks with the final functions' arithmetic: `ceil(c * n)` for `disc`; `floor` and `ceil` of `c * (n - 1)` and `lo + frac * (hi - lo)` in double for `cont`; `DESC` counting from the end, NULL inputs left out, NaN last, `-0` equal to `0`. Aggregates on the same argument share one set of values, an exact histogram of the distinct (group, value) pairs while it fits `work_mem` (at most 4 slots of 16 bytes per pair). When four in five of the first 8,192 pairs are new (prices, times) the pairs are appended to an array instead of a hash, 16 bytes each (EXPLAIN ANALYZE: `Ordered Sets Flat`), which holds `work_mem / 16` pairs, equal values side by side; a `mode()` keeps the hash. Beyond that the pairs are appended to a buffer of `work_mem` that is sorted and written as a run of (group, value, count) to a temporary file whenever it is full, and the runs are merged, 16 at a time, when the scan has ended (EXPLAIN ANALYZE: `Ordered Sets Spilled`): memory is bounded and nothing fails or falls back.

A constant fraction outside [0, 1] or a fraction that is a column of the group keeps the normal plan (which raises its own error; a `Param` or stable expression is evaluated once). The array forms and other argument types are generic aggregates. Overflow errors inside a float8 expression argument are not reproduced (as for `sum`).

## Generic aggregates

An aggregate without a native kernel runs on PostgreSQL's own functions (`SAA_GEN`). The scan decodes the argument leaves of the rows that pass the filters, builds the argument Datums (an expression of up to 4 leaves goes through `ExecEvalExpr` per row, or per element below an unnest) and calls the transition function per row and the final function per group as `ExecAgg` does: initial value, strict transition functions with a first-value copy, expanded states, `FILTER`, extra arguments and NULL direct arguments of the final function, the function's `fn_expr` (so `AggCheckCallContext`/`AggGetAggref` work), and privileges (`EXECUTE` of the aggregate for the user, of the component functions for the owner). The result is the normal plan's row for row, errors included. A group's rows arrive in scan order, the heap order of the normal plan's sequential scan, so order-sensitive aggregates (`array_agg`, `string_agg`, `jsonb_agg` without `ORDER BY`) agree while the index is in heap order.

| Family | Examples | How |
|---|---|---|
| Statistics | `stddev*`, `var*`, `corr`, `covar_*`, `regr_*`, over numeric, float8, int | `float8_accum`/`numeric_accum`/`int8_accum` and their finals; parallel through their combine functions |
| Booleans, bits | `bool_and`, `bool_or`, `every`, `bit_and`, `bit_or`, `bit_xor` | the transition functions |
| Sums without a kernel | `sum(float4)` | `float4pl` in heap order (the kernel adds in float8) |
| Min and max of text | `min(text)`, `max(varchar)`, any collation | `text_smaller`/`text_larger` over the decoded text |
| `DISTINCT` | `min`/`max`/`sum`/`avg`/`count(DISTINCT)` of other types, `string_agg(DISTINCT t, ',')`, `array_agg(DISTINCT x)`, `jsonb_agg(DISTINCT x)` | one varying argument without `ORDER BY`: the distinct values are kept per group in a hash set and fed in the type's btree order (NULL last, the first of equal values kept); when the sets pass `work_mem` the values move to the `tuplesort` above (`DEBUG1`: `DISTINCT of ... moved to a tuplesort`) |
| Collecting | `string_agg`, `array_agg`, `json_agg`, `jsonb_agg`, `json_object_agg` without `ORDER BY` | the transition function in scan order |
| `ORDER BY` in the call | `string_agg(x, ',' ORDER BY y DESC NULLS LAST)`, `array_agg(x ORDER BY y, z)`, `jsonb_agg(... ORDER BY ...)`, also with `DISTINCT` | one `tuplesort` of `(group, arguments)` in `work_mem` (spills to disk), then the transition function over each group's sorted rows; the sort operators, collations and NULL placement are the call's |
| Several varying `DISTINCT` arguments | `regr_count(DISTINCT y, x)`, `array_agg(DISTINCT x ORDER BY x DESC)` | the same sort, with the rows equal on the `DISTINCT` keys (NULLs equal) dropped as they are read back |
| Hypothetical sets | `rank(c) WITHIN GROUP (ORDER BY x)`, `dense_rank`, `percent_rank`, `cume_dist` | the group's sort, then the aggregate's own transition function (it adds the hypothetical row) |
| Variadic, many arguments | `VARIADIC` aggregates, aggregates of more than 4 arguments | as many argument slots as the call has |
| Joined and mapped columns | `string_agg(c.derived_category ...)`, `max(upper(c.name))` over a lookup join | the value of the lookup table's row for the row, one joined argument per aggregate |
| Ordered sets | `percentile_cont`/`percentile_disc` with an array of fractions, over an interval, a date, text or any other type with a btree order, `mode()`, `DESC`, with `FILTER` | the values of a group go through one `tuplesort` of `(group, value)` (bounded by `work_mem`, spills like the normal plan's sort), then the group's transition function over the sorted values and the final function with the direct arguments |
| User aggregates | any `CREATE AGGREGATE` whose transition and final functions are not volatile | as above |

Stays on the normal plan, all checked at plan time:

- A volatile transition, final or combine function, or a volatile argument expression.
- A column that is not a scalar leaf of the index (`jsonb` members of a type the index does not hold, arrays, `bytea`, `char(n)`, types not stored yet), an argument expression of more than 4 leaves, more than one joined column in an aggregate, `DISTINCT` of a joined column over a hash set (taken through the sort), a lookup, any sub-query or parameter that is not constant or stable.
- `DISTINCT` or `ORDER BY` over a type without a btree ordering.
- A fraction or hypothetical value that is a column of the group (the normal plan evaluates it per group and raises its own error outside [0, 1]); a constant, `Param` or stable-function fraction is taken.
- No workers (the leader scans alone: the serial scan, not the normal plan) when the aggregate is ordered (`ORDER BY` in the call, ordered sets, hypothetical sets), `DISTINCT`, over a joined column, lacks a combine function (a user aggregate declared without `combinefunc`), has an internal state without serial and deserial functions, uses a function that is not `PARALLEL SAFE`, or when a native float `sum`/`avg` is in the same query (its lanes fix the additions' order; generic states merge by worker).
- Hypothetical-set aggregates (`rank(c) WITHIN GROUP`) are priced by `AGG_GEN_HYP` so that the default plan stays the normal one: the scan sorts the group's rows and the transition function sorts them again (0.9 times the normal plan measured). The fixtures force the scan with page costs.

**Parallel scan.** States are merged by key value with the aggregate's combine function (an `internal` state through its serial and deserial functions, any other as a serialized datum; see Parallel scan). Collecting aggregates (`array_agg`, `jsonb_agg`) concatenate the workers' arrays in worker order, which the normal plan does not promise either. Float accumulation differs in the last digits (Exactness).

**Cost.** Per row: `AGG_GEN_ROW` (12 operator costs: the `fmgr` call and the leaf's Datum) plus the transition function's `procost` (1 for built-ins, 100 for a SQL function), the argument expression's cost and 2 per leaf; plus `AGG_GEN_DISTINCT` (10: hash probe of the pair), `AGG_GEN_ORDERED` (24: tuplesort put of an ordered set) or `AGG_GEN_SORTED` (30: put and read back of `ORDER BY` or a multi-argument `DISTINCT`). Per group: `AGG_GEN_GROUP` (150: state, final function, sort of an ordered group). Measured: a boolean or bit aggregate about 40 ns a row, a numeric accumulation 100 ns, a SQL function 1 us. Calls count the elements the data has, outside the factor the normal plan's unnest puts on the rest of the scan (see Cost): the whole table takes the scan, a selective store set keeps the btree plan (fixture `g_cost_selective`).

Measured on 300000 orders (one core, no workers): 3 to 23 times faster than the plain plan for statistics, boolean, bit, text `min`/`max`, `DISTINCT` and date percentile kinds, 1.3 to 4.6 for `ORDER BY`, multi-argument `DISTINCT`, joined-column and ordered-set kinds, 1.6 for a SQL-function user aggregate; hypothetical sets 0.9 (the normal plan). The aggregate's own work (numeric accumulation, a SQL function, a tuplesort) is the same in both plans, so those gain least. The ordered and `DISTINCT` kinds are serial; the combine-function kinds reach 2.5 to 4.5 times the W=0 scan with 4 workers.

## Shared CTE

PostgreSQL computes a CTE named more than once a single time, so aggregates over it (time-series-granularities: a time series at four granularities, each arm a `GROUP BY` of the same CTE) never reach the index. For a non-recursive CTE over a table with a column index, read twice or more, `agg/cte.c` (a `planner_hook`) plans the query again with the CTE `NOT MATERIALIZED` (PostgreSQL refuses that itself for a volatile function or a data-modifying statement) and keeps that plan only if it has a `Custom Scan (SiftAgg)` and costs 2 times less than the materialized one, on top of the factor of about 6 `agg_cost` charges every scan of an unnest (the planner assumes 100 rows per `jsonb_to_recordset` call; see Cost). time-series-granularities keeps the materialized plan for the 400 store set and takes the inline one for the 4000 store set, 5 times cheaper. The arms are planned apart (own PlannerInfo); the executor fuses those that qualify into one pass (One pass for the arms, `fuse_register`), the others are each a pass (four for time-series-granularities).

The inline copy repeats the CTE body in every arm, so a computing sub-select of that body (an `IN (SELECT .. GROUP BY ..)` ranking that keeps a few keys) would run once per arm. In the inline plan it is a `MATERIALIZED` CTE of the same `WITH` that the arms read (`hoist_sublinks`; the CTEs it reads stay those of the `WITH`; a correlated sub-select, `NOT IN` and a plain table lookup stay in place). On a 3-million-row table with a ranking sub-select that keeps few keys, the hoisted plan takes 132 ms where the inline plan with copies takes 237 ms (shared plan 114 ms) and plans 30 to 75 ms faster in a fresh backend; larger requests run in the same time; replays of 156 and 237 statements plan identically.

No setting: `sift.enable_agg = off` plans none of it.

## Numeric

A `numeric(p, s)` column with `p <= 18` is an INT leaf holding the number times 10^s, planned as an int64 leaf with a scale: integer kernels, group ids and accumulators, and PostgreSQL's own numeric functions for anything that is not integer arithmetic. An `int8` column is the same leaf with scale 0 (so also a group key). Through 3.1 an unconstrained, wider or negative-scale numeric was refused at `CREATE INDEX`; since 3.2 those are wide leaves.

**Predicates** (`numeric` column against a constant or parameter): `= <> < <= > >=`, `BETWEEN`, `IS [NOT] NULL`, `IN`/`NOT IN` of constants. Up to 64 constants are an OR of equalities (an AND of inequalities for `NOT IN`) as PostgreSQL evaluates them; more are one test of a hash set of the scaled integers (NaN equal to NaN only). `NOT IN` with a NULL member is never true. The operand is evaluated at scan start and scaled with `numeric_mul`, `numeric_floor` and `numeric_ceil`; the operator is rewritten for the stored integers:

| Predicate | On the stored integers |
|---|---|
| `x = c` | holds for `c * 10^s` when it is an integer, for no value otherwise |
| `x <> c` | every value when `c * 10^s` is not an integer |
| `x < c` | `x < ceil(c * 10^s)` |
| `x <= c` | `x <= floor(c * 10^s)` |
| `x > c` | `x > floor(c * 10^s)` |
| `x >= c` | `x >= ceil(c * 10^s)` |

A constant beyond the integer range, NaN (greater than every number in PostgreSQL's numeric order) and the infinities give the same answer for every value of the column and never reach the kernels.

**Expressions** (aggregate arguments, group keys, predicates, `count(DISTINCT)`): `+ - *`, unary minus, `abs`, `round`/`trunc` with a constant number of digits (also negative), `floor`, `ceil`, numeric constants, `int2`/`int4`/`int8` columns cast to numeric, and numeric cast to `int2`/`int4`/`int8`. They run as a program of int128 values scaled by the result's `dscale`, as PostgreSQL gives it: the larger operand scale for `+` and `-`, the sum of scales for `*`, the digits asked for by `round` and `trunc` (0 for a negative count), 0 for `floor` and `ceil`. Two expressions in a comparison are compared as their difference against 0.

The planner tracks each step's possible digits from the columns' declared precision and refuses the expression when a step could need more than 38 (int128), a sum or average when its argument could need more than 26 (room for 10^12 rows), and a cast to an integer when the kept digits could overflow it (PostgreSQL raises then). Equality is by value (`1.0` and `1.00` are one number for a distinct count, key or `min`). Results carry the scale above (trailing zeros kept as PostgreSQL does).

**Aggregates**: `sum`/`avg`/`min`/`max`/`count`/`count(DISTINCT)` of a column or an expression (`sum(DISTINCT)` and `avg(DISTINCT)` too). `sum` adds in 128 bits, so it is exact however many values there are, with the argument's scale (the `dscale` PostgreSQL gives the sum); `avg` is `numeric_div` of that sum and the count (as `numeric_avg`); `min`/`max` are the value; a group without values is NULL. `sum`, `avg` and `percentile_cont` of `numeric(p <= 15, s)` cast to float8 divide the integer by 10^s in double: below 2^53 the correctly rounded value `numeric_float8` computes.

**Evaluated by PostgreSQL** (no kernel, since the result scale or error is PostgreSQL's to decide): division, `%`, `sqrt`, `power`, the other functions, casts, NaN and infinite constants inside arithmetic (a NaN in the column is not one of these: the programs handle it) and `IN` with a parameter array or more than 64 constants; see "Expressions PostgreSQL evaluates". `min`/`max(DISTINCT)` and `percentile_*` over numerics are generic aggregates (the scan keeps float8 percentiles only).

Speed on 300000 rows (one core): a range or `IN` predicate with `count`/`sum` 19 to 28 ms plain against 0.6 to 1.0 ms; `sum(price * qty)` 51 against 3.1; `GROUP BY round(price, -1)` 39 against 3.5; `count(DISTINCT price)` per store 130 against 15; 100000 groups 41 against 17 and 300000 groups 91 against 56.

**Existential tests** keep the normal plan when equality with the plain plan is not provable from the index:

- jsonpath `!=` and `!()` (a JSON null member is "not equal" to a literal, an absent key is not; the index holds a NULL for both);
- jsonpath numbers against a float leaf (jsonb numbers compare exactly, the leaf holds the rounded double);
- strict mode and variables; a literal of another type than the leaf (unknown in jsonpath); `@>` with a JSON null, a float or an empty object;
- an element compared with an expression of the outer row or with a column of another type class; `LIMIT`/`OFFSET`; a second array in the sub-query.

`NOT` of `jsonb_path_exists` or `@>` is NULL for a NULL document, so it is taken only for a column with a NOT NULL constraint (`NOT EXISTS` is never NULL). A document the index cannot hold (an object or a string where the array is, a member of another type than declared) raises `0A000` as for any pushed query, where the plain jsonpath and `@>` return a result; a JSON null where `jsonb_to_recordset` raises in the plain plan has no elements here. The element predicate runs on every element of the unit's rows before the other quals.

## Expressions PostgreSQL evaluates

An expression of up to eight scalar leaves (`int`, `date`, `timestamp`, `float` and `numeric(p <= 18, s)` columns, dictionary columns) with no kernel is a combo: `ExecEvalExpr` runs the planner's own expression tree over the decoded values once per distinct tuple of leaf values, and rows look the result up by the tuple's id. `numeric` leaves are rebuilt as the heap's numeric (scale, NaN included), so rounding, scales, operator semantics and errors are PostgreSQL's.

A combo serves as an aggregate argument (`sum`/`avg`/`min`/`max`/`count` of numeric and float8 expressions, `min`/`max`/`count` of the others; a numeric `sum`/`avg` adds with `numeric_add`, so no digit limit), group key and filter, next to the kernels in one scan. It covers numeric `/`, `%`, `sqrt`, `power` and every volatile-free function, the `numeric(p, s)` cast, `numeric` against float8, NaN and infinite constants inside arithmetic, `IN` with a parameter array or more than 64 constants, expressions over 38 digits and `sum`/`avg` arguments over 26 digits, `CASE` and `COALESCE`, and the same for `int`, `float` and `timestamp` leaves (`i4 % 7` as a key, `date_trunc('month', ts)`, `sqrt(abs(f8))`).

- **Cost**: each distinct tuple of leaf values pays `AGG_EVAL_SLOT` (60 operators) per expression; each row pays 3 per 64-bit leaf (the id probe), and 2 or 1 for a numeric `sum`/`avg` or an extreme. The tuple count is `estimate_num_groups` of the leaves' index expressions over the rows reaching the expression, so an expression over a many-valued column pays for every row. PostgreSQL prices the normal plan's evaluation at `procost` per function (1 operator) and a numeric transition at one, though a numeric function costs 40 operators and more and an interpreter call about 5; the scan is credited half of that difference per row that reaches an expression, a numeric transition or a numeric kernel operation (`plain_credit`, floor 10 percent of the scan's price). So the shapes in Speed pick the scan at default costs, while `sum(rate / 3)` over 300000 distinct values (the scan evaluates every row as the normal plan does, plus its machinery) and selective queries (the credit is per passing row, the scan reads every unit) keep the normal plan. A scan with workers pays `AGG_SETUP_FACTOR` times `parallel_setup_cost` once (see Cost).
- **Calendar keys (3.1)**: a `timestamp` group key built of `date_trunc` (second to millennium), `::date` and `extract` (minute to millennium, `dow`, `doy`, `week`, `isoyear`; not `second`, `epoch`, `julian`) changes value only on a second, minute, hour or day boundary of the data, so it is not a combo. The scan numbers the rows' steps (`floor(ts / step)`, an unsigned multiply per row), covers the unit's numbers in a range table as an integer key does, and evaluates the expression once per step (about 1,100 days for three years of `date_trunc('month', ts)::date`). EXPLAIN shows `(per day)`, `(per hour)`, `(per minute)` or `(per second)` after the key. An infinity or a range over 2^22 steps falls back to a hash table of step numbers with the same ids. The group estimate comes from the column statistics (36 months), so month-over-month questions on a `sift_table` can take this path. A `timestamptz` key (`date_trunc`, `extract`, `::date` of the leaf) reads the clock of the session's time zone and is cut the same way on that clock: the executor takes the zone when it starts (so a cached plan and `SET TIME ZONE` give the rows of the zone in force), lists the offsets the zone has over the span of the data (`pg_next_dst_boundary`, a few pieces for a daylight saving time) and numbers each row's step `floor((t + offset) / step)` on the local clock, one multiply and an add for a span of one offset, a lookup in the pieces otherwise; the expression is evaluated by PostgreSQL once per step, at an instant of it. `date_trunc` to a second, minute or hour keeps the offset of the instant it cuts (the repeated hour of a daylight saving change gives two values), so those keys are grouped by the instant they cut to. A zone named in the call, `epoch`, `second` with fractions, `julian`, the time zone fields and an expression that reads the leaf in another way keep the normal evaluation. Wherever the zone is not a pure offset (daylight saving time, 30 and 45-minute offsets, local mean time before 1900) the rows are those of PostgreSQL's plan (`test/sql/agg_trunc_zone.sql`, 14 zones; fuzz family `zonekey`).
- **Cache**: tuples are kept up to `sift.agg_combo_cache` entries (default 1048576) and dropped past that (bounded memory; a returning value is evaluated again). A combo with a lookup join or a dictionary leaf is never dropped (its ids live outside the cache).
- **Errors**: an expression that can raise (a divisor that is not a nonzero constant, `sqrt`, `power`, `ln`, a cast of text, any function that is not leakproof) is a late conjunct of a `WHERE` or `FILTER`, evaluated only for rows that passed the other conjuncts, and as a key or argument only for aggregated rows. So `WHERE d <> 0 AND x / d > 1`, `FILTER (WHERE d <> 0)` and `CASE WHEN d = 0 THEN 0 ELSE x / d END` raise nothing, as in the normal plan; a row on which the normal plan fails fails here with the same error. One reordering: PostgreSQL orders a `WHERE`'s conjuncts by cost, so a zero divisor in a row that only a costly conjunct excludes can fail one plan and not the other. A lookup-table predicate or key (evaluated per value of the dictionary or the unit's range, also for values no selected row has) never takes an expression that can raise: it goes to a combo.
- **NaN stored in a numeric column**: the index keeps NULL and the overflow flag; the source presents the row as `PG_INT64_MAX` (not NULL) with its bit in `SiftSrcVec.ovf`. Numeric orders NaN above every number and equal to itself, which is what that integer is to the kernels, so `min`/`max`, `count`, `count(DISTINCT)`, group keys, `ORDER BY ... LIMIT` and `IS [NOT] NULL` need nothing more; code that adds or compares reads the bit.
  - The programs (`+ - * abs round trunc floor ceil`, the aggregates and keys over them) carry a NaN flag per value, set by any operand's NaN, and give `SIFT_NX_NAN` (above every program value, equal to itself) for a flagged one. `sum` and `avg` keep a flag per group and give NaN.
  - `SNX_CMP3`, the comparison of two expressions (`n > m`; not their difference, which cannot tell which side is the NaN), gives -1, 0 or 1 with NaN equal to NaN and greater than the rest. A comparison with a constant takes the NaN row's answer from the operator and the constant (`NaN op c`: `<>`, `>` and `>=` for a number; `=`, `<=` and `>=` for NaN; `NaN < c` never), also for a NaN or infinite constant. A constant comparison that holds for every number is a source filter only if it holds for NaN too.
  - The cast to an integer raises `cannot convert NaN to integer` (`SNX_INTCAST`) for a NaN of a row the aggregate takes or the conjunct reaches (a late conjunct). `n::float8` is NaN; a generic aggregate gets the NaN datum; combos read the flag and rebuild the NaN (`sum(n / 3)`, `n % 3` as a key, `n > 1.5::float8`). `sift_table` tables still raise `0A000` for it (the provider does not pass the flag).

**Speed** (300000 rows, default costs, 300000 rows `numeric`/float8 columns): `sum(price / 3)` with `avg(weight / 7)`, a key `weight % 7` with `max(sqrt(abs(price)))`, a float8 key `f::int % 5` with `sum(sqrt(f))` run 3 to 9 times faster than the normal plan at W=0, 2 and 4 (the scan kinds are serial; the normal plan parallelizes); no pick is slower than the serial normal plan. The high-cardinality shape `sum(rate / 3)` (300000 distinct values) is the worst case of any hash aggregate (one evaluation per row, as the normal plan, plus a table of ids and a cache entry per row) and keeps the normal plan. A combo of one 64-bit leaf has no entry hash (the entry is the value's id), probes ids one prefetched cache line each, and adds values as integers (a 128-bit sum at the largest scale seen, `numeric_add` only past 38 digits, NaN or infinity); what is left is the evaluation itself (`numeric_div` is 670 instructions a row).

**Tests**: the `n_numeric_pg_*` and `e_*` fixtures (`test/col/fixtures`, `-- expect: agg`, compared with `sift.enable_agg` off) and the regress cases `agg_numeric` (numeric, sums of many scales, guarded divisions, a cache of 64 entries), `agg_numeric_nan` (NaN against the normal plan for every shape that reads the column: in segments, the write buffer, after a merge, with deleted rows) and `aggstub_expr` (the same on the test provider; `sift_colstub` has no `numeric`, so numeric fixtures run on `sift_col` only). The runner raises the page costs of an `expect: agg` fixture on the 4000-order table; `coldiff.sh --default-costs --fixture-rows 300000` runs the fixtures at server costs on `t_int`, `t_float`, `t_num` and `t_nan` tables that grow with the rows, where the numeric and `e_*` fixtures pick the scan. The expectations failing at that size are dictionary, jsonb and tiny-table shapes: `f_*`, `q_null_tests`, `n_empty_*`, `n_numeric_nan_cast*`, `s1`, `s2`, `s3`, `s4`, `s7` at K10 to K4000.

### What keeps the normal plan

- An expression of more than eight leaves (the expression cache holds at most eight inputs), or reading a column outside the index.
- Volatile functions, sub-plans, aggregates and window functions inside the expression (not constant per tuple of leaf values).
- A `numeric` column the index cannot hold (through 3.1: precision over 18, unconstrained, negative scale) is not read from the index; the digit limits above do not apply to it.
- A filter on an expression (`lower(col) IN (...)`) is pushed, but PostgreSQL estimates 1% of rows for an expression, so the normal plan can look cheaper: `CREATE STATISTICS s (mcv) ON lower(col) FROM t; ANALYZE t` gives the planner the real share.

## Executor

One unit at a time (the rows of a segment inside one superblock), through the seam of `src/agg/colsrc.h`:

1. Row predicates to a bitmap with the kernel tables (integer comparisons are `range` and dictionary lookups `lut` of the column table, `cmp_f64` / `lut_i32` of the aggregation table; bitmap operations are the column table's `bits_*`). Dictionary predicates are evaluated once per distinct value of the unit's dictionary.
2. With an unnest, element predicates in vectors of 4096 elements, the row bitmap lifted to the elements through their parent ordinals.
3. `sift_colsrc_live` and `sift_colsrc_vet`: dead rows out, then, after the value filters, the rows that the snapshot does not see (all-visible pages pass, the others are read in the heap; a `sift_table` table has nothing to check).
4. Group ids: dictionary codes and integer values map to dense key ids through tables built once per dictionary / value range; ids combine into a direct array (up to 2^20 groups) or a hash table beyond.
5. Aggregates accumulate by group in tuple order.

The groups leave as the rows of `custom_scan_tlist` (keys, then aggregates); the plan above projects and filters. EXPLAIN ANALYZE shows `Rows Vetted By Heap`, `Heap Pages`, `Units`, `Units Pruned`, `Rows Scanned`, `Tuples Aggregated`, `Dense Chunks` and `Groups`; plain EXPLAIN shows filters, group keys and pushed aggregates.

**Dense chunks.** A chunk of up to 4096 rows or array elements whose aggregates are `count`, `sum` and `avg` of one integer or float leaf (or `count(*)`), with or without `FILTER`, grouped by dictionary (text) leaves or not at all, is aggregated from the bitmap of its rows, with no tuple list and no gather by position. An aggregate takes the bits that pass its `FILTER` and have a value; a count is a popcount (per group with keys); a sum runs over the bits in row order, so a float sum has the tuple path's bits. Anything else in the query (a join, an expression key, `min`, `max`, `count(DISTINCT)`, a late `FILTER`) and a unit that reads few rows (`SPARSE_RATIO`) run the tuple path. `sift.agg_dense = off` turns the dense path off.

### One pass for the arms

The inlined arms of time-series-granularities scan the same rows for the same aggregates and differ in their keys. Nodes of one executor state register when they begin (`fuse_register`); the first to run takes every registered node whose spec equals its own once keys are left out, uniting the leaves, joined-table columns and combo slots only some read (`agg/spec_fuse/`, `sift_aggspec_fusable`). It scans once with the arms' distinct keys and the arm as the set key (the grouping-set machinery, `sift_aggspec_fuse`); each node returns the groups of its set. `sift.agg_fuse` turns the pass off. Each set's tuples are those of the node alone, in the same order, so rows and float sums equal separate passes. `EXPLAIN ANALYZE` shows `Arms In One Pass` and the shared scan's counters on each node.

Fusable arms:

- No grouping sets or two-level arms; the arms' distinct keys fit the 32 of a grouping set.
- The same aggregates; when the arms join a lookup, the union of theirs, each set updating its own (`SiftAggSpec.setaggs`; not with a percentile or `GROUPING`, which are numbered by the arm's list).
- The `WHERE` conjuncts every arm has are the pass's; an arm's extra ones are tested on its own set (`SiftAggSpec.setpred`). The pass keeps the rows of any arm (an `OR` of them, which also prunes units).
- Lookup joins of tables or correlated sub-selects, which repeat or drop every set's tuples: an inner and a left join of the same table are one left join, and the inner-join arm drops tuples without a row (and those failing the rest of its `ON`) in conjuncts over its own set's joined rows (`SiftAggSpec.postsets`).
- A CTE or VALUES list joined as a plan is the same when it is the same CTE (its plan in the statement) or the same list with the same conditions (`SiftAggJoin.sig`). Its plan outputs all its columns, so the arms number them alike, and the pass runs the plan of the node that starts it.
- A correlated sub-select is the same in two arms when its query as written, conditions and scan-call expressions are (`SiftAggJoin.sig`). PostgreSQL plans only the columns an arm reads, so the pass runs the sub-select of an arm that reads all the pass's columns.

Staying apart: arms the pre-aggregates answer one by one, and arms that join no lookup (a per-set chunk filter costs more than the shared decode saves).

### Groups in the order the parent sorts by

A grouped scan returns its groups in the order the table met them and PostgreSQL puts a Sort above it for an `ORDER BY`, a window (`PARTITION BY .. ORDER BY ..`) or a merge that wants them sorted. Besides its path the planner (`agg/plan_ord.c`) offers a copy that promises pathkeys: the longest prefix of the pathkeys of the query's `ORDER BY` (`sort_pathkeys`), of its window (`window_pathkeys`) and, for a sub-select or an inlined CTE, of the parent query's (`query_pathkeys` of `parent_root`) whose columns are keys of the group. A column the sub-select gives as a constant is in any order, and `COALESCE(key, constant)` sorts as a key whose NULL is the constant; the scan of the sub-select is offered with the parent's pathkeys, which include those columns, so the parent's Sort goes. The parent uses the path when it is cheaper than the Sort it would add (`ORDER BY`, window or Incremental Sort on a shorter prefix). EXPLAIN shows `Group Order Keys` in the node.

The executor (`agg/gorder.c`) sorts the final groups, after the lanes and the workers' tables are merged, so it is the leader's and costs by the groups, not the rows: the distinct values of each key (far fewer than the groups) are sorted once with the comparator of the pathkey (its sort operator, collation and nulls order, so the order is the Sort's), each id gets a rank (equal values the same rank), and the groups are sorted by the tuple of ranks as integers. An ordered scan keeps its groups in memory unless the first key of the order is an integer, a date or a timestamp: past hash memory it spills in ranges of that key (`agg/spill.c`: when the key is the value of a leaf or a step of it, the tables go at once to equal widths of the range the zones of the leaf give, else to one stream that a sample of 8,192 values of the key cuts into ranges of as many groups; a range that does not fit is cut again by the sample, and the ranges are merged in the order of the key, each sorted by `agg/gorder.c`), so a table of 1,026,312 groups at `work_mem` 4MB is 557 ms where the scan and a Sort took 850 (520 with the table in memory), and 345,272 groups 212 ms where they took 300 (257); a range of one value that does not fit is merged as it is. It is not fused with other scans. Not offered for grouping sets, `DISTINCT ON` or a scan that keeps only some of its groups (`ORDER BY` an aggregate `LIMIT n`). The rows and their order are the normal plan's (tests `agg_group_order`, `agg_group_spill_order`).

### count(DISTINCT) of a 32 bit value

A dictionary code, a dense id of a numeric expression or an integer column up to 32 bits is counted per group in one of three exact forms (`struct Dist`); NULLs never enter.

- **Bitmaps**: dense values (the range seen is covered by few enough 4096 bit chunks). Each group has a directory over the shared range, chunks are allocated when a value falls in one, and the count is a popcount. A set that fits the cache sets bits one by one; a bigger one (2 MB and more) finds and prefetches the words of 32 values before setting them, since each is a miss of its own.
- **Bounded set**: sparse values go to an open addressing set of (group, value) pairs, inserted in batches with the slots prefetched.
- **Sort**: past `get_hash_memory_limit()` (`work_mem` times `hash_mem_multiplier`, per aggregate) the pairs move to a tuplesort that spills to temp files, and a group's count is read from the sorted runs. A bitmap or set outgrowing the budget converts, so memory never grows with the distinct values. EXPLAIN ANALYZE shows `Distinct Sets Spilled`.

**Workers.** A set still a bitmap of raw 32 bit values goes as its chunks of 32 bits or more (group, chunk number, 512 bytes) plus the bits of sparser chunks as pairs; the leader ORs a chunk into its own and counts new bits by popcount (`Distinct Chunks Merged` in EXPLAIN ANALYZE). A worker's blob is at most its bitmap's memory, which `hash_mem` bounds. A narrow set (`dist_narrow_words`) goes as the words of its window, ORed and popcounted by the leader. Dictionary and numeric-expression values are ids local to a worker, so they go as pairs by value (the numbers themselves for a numeric expression, to which the leader gives its own ids). `sum(DISTINCT)` and `avg(DISTINCT)` of numerics are the same pairs from the set that adds each number once per group; the leader adds the numbers it has not added yet (`agg_distinct_parallel`). `EXPLAIN (VERBOSE)` says `Serial Because` for a scan that cannot have workers whatever the cost (a state the leader cannot merge).

Memory and time (customers-per-month, `count(DISTINCT customer_id)` per month, 10M orders, 2M customers, one backend without workers): 398 MB peak with a hash set, 13.5 to 13.8 MB with the forms above at `work_mem` 4MB and 64MB (same rows); with 3 workers 51 MB and 40 MB at the leader (each worker has a budget). At 10M rows the forms take 76 to 87 ms against 704 ms for the hash set. Fixtures `d_*` and the `aggstub_agg` and `aggstub_sets` regress cases cover the three forms, the chunks and pairs from workers, the spill (`work_mem` 64kB), NULLs and the global case.

### EXPLAIN in text and in the other formats

`FORMAT TEXT` prints the counters of a custom scan as a few lines in the manner of `Buffers: shared hit=.. read=..`: `Label: 384 read, 64773 pruned (2 by set summary)`; a part of the previous number is in parentheses. `FORMAT JSON`, `YAML` and `XML` keep one typed field per counter, named as in the tables of this document, so a tool reads those and never the text. In text a counter that is zero is left out, and a line left empty is not printed, unless `EXPLAIN (VERBOSE)` (every counter is printed then). Never left out at zero: the units read, the rows scanned, the tuples aggregated, the groups, the units answered from the pre-aggregates, the rows and time of the lookup tables load, the heap fetches and superblocks skipped of a top-N, the counters of `SiftScan` and `SiftAggRows`, and the members of a group that is printed only when it applies (units net of exceptions or dead rows, the point directory, the rows re-read from the heap, the first row lookups, the semi tables, the search matches): the zero is the finding there. The counters that tell why a plan is slow when they are not zero (rows vetted by the heap, heap pages, units pruned, dense chunks, spills, key filters) are printed whenever they are not zero. The plan lines (`Index`, `Partitions`, `Filters`, `Search`, `Top-N`, `Group Keys`, `Cell Keys`, `Pushed Aggregates`, `Unnests`, `Lookup Joins`, `Workers Planned`, `Workers Launched`, `SIMD`, `Arms In One Pass`, the `GPU` lines and, under `VERBOSE`, `Serial Because` and `Pre-aggregates`) are one line each in every format.

| Text line | Node | Fields of the other formats, and the words of the text |
|---|---|---|
| `Units: 384 read, 64773 pruned (2 by set summary), 10 from pre-aggregates` | `SiftAgg` | `Units` read, `Partitions Removed` partitions removed, `Units Pruned` pruned with `Units Pruned By Set Summary` in parentheses, `Units From Point Directory` from point directory with `Rows From Point Directory` as rows, `Units Filtered By Sorted Run`, `Units Read Again From The Heap` read again from the heap, `Units Without The Sketch`, `Units From Pre-aggregates`, `Units Net Of Exceptions` and `Units With Exceptions Scanned`, `Units Net Of Dead Rows` and `Units With Dead Rows Scanned`, `Superunits`, `Units Without The Rollup`, `Units Scanned By Cost`, `Rollup Refresh Queued` (refreshes queued) |
| `Rows: 27819 scanned (2 filters in source), 183 aggregated, 17 groups` | `SiftAgg` | `Rows Scanned` with `Filters In Source`, `Type Filters In Source` and `Wide Filters In Source` in parentheses, `Tuples Aggregated`, `Dense Chunks`, `Combo Caches Restarted`, `Rows Vetted By Heap`, `Heap Pages`, `Rows Read Again From The Heap` (re-read from the heap), `Search Matches`, `Groups` with `Group Order Keys` (order keys) in parentheses, `Groups Dropped By Top-N`, `Ordered Sets Spilled`, `Ordered Sets Flat`, `Group Table Spills`, `Distinct Sets Spilled`, `Distinct Chunks Merged`, `Distinct Stream Superblocks` |
| `Lookup: 4751 rows read, 166 rows loaded, 1 index scans, 4.045 ms to load, 2 key filters, 3 semi tables (6 kB)` | `SiftAgg` | `Lookup Rows Read`, `Lookup Rows Loaded`, `Lookup Rows Shared`, `Lookup Index Scans`, `Lookup Heap Fetches`, `Lookup Column Loads`, `Lookup Rows Classed`, `Lookup Class Workers`, `Dimension Maps Shared`, `Dimension Maps Built`, `Lookup Load Time` (`TIMING`), `Child Direct Probes` with `Child Probe Leaves` (the leaf pages the probes moved to), `Child Probe Upper Pages Pinned`, `Child Probe Heap Fetches` and `Child Probe Index Only Entries` (answered by the visibility map), `Join Key Filters` (key filters), `Join Key Passes`, `Shared Lookup Tables`, `Cached Lookup Tables`, `Child Blocks Prefetched`, `First Row Lookup Tables`, `First Row Lookups`, `First Row Keys Differ`, `Semi Lookup Tables` with `Semi Lookup Table Size` in kB, `Lookup Pages All Visible` (the share of the lookup tables' pages the visibility map holds all-visible; a separate `Lookup Hint` line says to VACUUM them when it is under half for 64 pages or more) |
| `Top-N Run: 12 superblocks skipped, 0 heap fetches` | `SiftAgg` (top-N) | `Superblocks Skipped By Top-N`, `Top-N Candidates`, `Top-N Restarts`, `Units Read By Sorted Run`, `Units Read Without Filters`, `Units Answered By Run Head`, `Heap Fetches`, `Top-N Sorted Rows` |
| `Pre-aggregate Rollup: rows; dims: key; measures: value` | `SiftAgg` | `Pre-aggregate Rollup` (`rows`), `Pre-aggregate Dims` and `Pre-aggregate Measures` as three fields |
| `Scan: 1 index searches, 40 superblocks, 12 blocks scored (3 skipped), 80 candidates` | `SiftScan` | `Index Searches`, `Key Passes`, `Superblocks`, `Blocks Scored` with `Blocks Skipped` in parentheses, `Candidates`, `Heap Checks`, `Heap Pages` |
| `Filter Mask: 3 superblocks, 10 exact pages, 2 lossy pages` | `SiftScan` | `Filter Superblocks`, `Filter Pages Exact`, `Filter Pages Lossy` |
| `Rows: 100 read, 7 groups, 3 distinct pairs` | `SiftAggRows` | `Rows Read`, `Groups`, `Distinct Pairs`, `Distinct Sets Spilled` |

The JSON and the text carry the same figures: a counter the structured formats leave out when it does not apply (`Lookup Rows Shared` at zero) is left out of both.

### EXPLAIN labels

Every label that `src/` raises is named in a document. The ones not described where their feature is (`EXPLAIN ANALYZE` unless said otherwise):

| Label | Node | What it counts or says |
|---|---|---|
| `Sets Summed From One` | `SiftAgg` | grouping sets whose groups were added from the finest set's at the end of the scan (the sets all lie inside one of them and every aggregate adds exactly: counts, integer and numeric sums and averages, min, max); absent when each set had its own pass |
| `Sort Method`, `Distinct Values`, `Sort Memory` | `SiftSort` (`ANALYZE`) | how the sort ran: `rank` (the keys replaced by the ranks of their distinct values: the number of distinct values of each key, and the kB the ranks held) or `tuplesort (reason)` when the ranks did not pay |
| `Shared Dictionaries` | `SiftAgg` (`ANALYZE`) | the dictionaries of text columns this scan took from the shared memory of the server (`hits`) and the ones it had to parse itself (`misses`); absent when it used none |
| `Rows Decided By The Log` | `SiftAgg` (`ANALYZE`) | of the rows vetted by the heap, those the tombstone log decided without a heap page |
| `Group Pass Restarts` | `SiftAgg` (`ANALYZE`) | times a group pass of a spilled scan was started again with a smaller share of the groups because its table outgrew the memory it was given |
| `Units Read From Their Ends` | `SiftTopN` (`ANALYZE`) | units of a top-N whose rows are in the order of the key, read from the end the order asks for |
| `Ranker Searches` | `SiftScan` (ranked) | searches of the index the ranker made when it made more than one |
| `Blocks Scored`, `Blocks Skipped` | `SiftScan` (ranked) | posting blocks the ranked evaluation scored, and those it skipped without scoring |
| `Limit Hint` | `SiftScan` (ranked) | the `LIMIT k` the planner gave the scan |
| `Include Tests` | `SiftScan` (ranked) | tests of INCLUDE columns the ranker applies before it scores (a comparison, `IN`, `IS [NOT] NULL`, a boolean, and one for each small joined table whose keys it was given) |
| `Filter Mask Up To Matches` | `SiftScan` (`VERBOSE`) | with `Filter Mask From Matches`: the bitmap filter is built when the query matches between the two numbers of documents |
| `Filter Pages Exact`, `Filter Pages Lossy` | `SiftScan` | pages of the bitmap filter that keep the offsets of their tuples, and pages kept whole (lossy) |
| `Group Columns`, `Distinct Counts` | `SiftAggRows` | the group-by columns of the node, and its `count(DISTINCT)` aggregates |
| `Distinct Pairs` | `SiftAggRows` | the (group, value) pairs the distinct counts kept |
| `Combo Caches Restarted` | `SiftAgg` | times a combo cache reached its entry limit and was started again |
| `Group Table Spills` | `SiftAgg` | times the group table spilled to partitions |
| `Lookup Rows Shared` | `SiftAgg` | rows of the lookup tables the leader put in the shared memory of its workers |
| `Lookup Column Loads` | `SiftAgg` | loads of the columns of a lookup table from the leaves of an index of that table |
| `Lookup Rows Classed` | `SiftAgg` | rows of a lookup table given the class of their key (the table is kept as one row for each class of values: `agg_lookup_dense`) |
| `Lookup Class Workers` | `SiftAgg` | workers that made the classes of the lookup table's keys together with the leader (`agg_lookup_gang`) |
| `Pre-aggregate Measures` | `SiftAgg` | the measures the node reads from the pre-aggregates (`Pre-aggregate Dims` are the dimensions) |
| `Top-N Units By Order Code` | `SiftAgg` | units a top-N compared with its bar by an exact order code, by a prefix of it, or as bytes (text under `C`, `uuid`, `bytea` keys) |
| `Rows Split By` | `SiftAgg` | `the key of a lookup join` when the workers split the rows of the scan by that key (each takes the rows whose key is in its share, so a key is resolved by one participant), instead of taking the rows of a unit |
| `Units Net Of Dead Rows`, `Units With Dead Rows Scanned` | `SiftAgg` | units answered from their pre-aggregates net of their dead rows (`sift.agg_dead_delta`), and units with dead rows that were scanned instead |
| `Unnests` | `SiftAgg` | `yes` when the node unnests a list of the row |
| `Workers Priced For` | `SiftAgg` | `the rows the scan reads`: the workers are priced for the rows the scan reads, not for the tuples PostgreSQL expects of the joins |

## Parallel scan

The planner offers a second path with the workers PostgreSQL would give a parallel sequential scan (`compute_parallel_worker`: table size, the `parallel_workers` reloption, `max_parallel_workers_per_gather`), only when the whole query is parallel safe (`glob->parallelModeOK`, no unsafe function in the spec) and the spec is one of the parallel kinds below; the cheaper path wins. The cost divides the scan by PostgreSQL's divisor (workers plus the leader's share when it scans too, `parallel_leader_participation`), adds `parallel_setup_cost` and charges every partial table for its groups (merge cost per group). `sift.enable_agg = off` removes both paths.

The node is not under a Gather: its workers return tables, not tuples, so it keeps a parallel context of its own (`sift_agg_worker_main`, one `shm_mq` per worker). The leader opens the source, creates the context, fills the shared state, launches, and waits with the source still open; sources keep their page pinned, never locked, between calls, so no interrupt is held off (which would make `InitializeParallelDSM` plan no workers). A worker opens the relations and the source with the leader's snapshot and parameters and registers the same predicates and keys from the spec. Work is a shared-memory counter that leader and workers take items from:

- **Exact aggregates** (counts, integer sums, min/max, count(DISTINCT)): an item is 2 superblocks. A participant aggregates its items into one table and sends it when the counter runs out; the leader merges tables by key value (dictionary codes and key ids are per process), unions count(DISTINCT) sets and adds the rest. Integer arithmetic is exact, so order does not matter.
- **Float sums** (`sum`, `avg` of float8): additions are not associative, so the scan has 16 lanes (`SIFT_AGG_LANES`): lane l is superblocks l, l + 16, l + 32, ..., summed in superblock order into its own table, and the leader adds the lane tables in lane order as soon as earlier lanes arrived. Whoever scans a lane, the additions are the same, so the result is bit-identical for 0, 1, 2, ... workers and every SIMD level. The scans that gained workers later (a spine or a correlated child plan) add in tuple order when their plan has no workers, as they did, and in lane order when it has (launched or not). `avg` counts and the "no float seen" flag merge with it; the `avg(float8)` overflow guard takes the largest input of all lanes.
- **Without workers** the leader runs the same items (a serial run is the same computation), also when workers cannot be launched (none free, nested in a worker, no DSM). A scan with float sums and no workers launched adds the lanes in turn into one table (`lane_fold`: a lane's float sums are collected in the accumulator and added to the earlier lanes' when the lane is done), with the bits of a scan with workers and no table dumped per lane.

All memory that grows with the groups (key ids and values, the group arrays, count(DISTINCT) sets) is in one table context reset between lanes; a unit's buffers and the registered expressions live as long as the scan.

Which specs run in parallel is `sift_aggspec_parallel_ok`, shared by the planner and the executor:

|  | Parallel (a table is dumped, merged by value, reset) | Serial only (the leader scans alone: no workers planned, no lanes) |
|---|---|---|
| Keys | a column (dictionary, integer, date, bool), an expression of one column (`SAK_LEAF`, `SAK_LUT`), float keys (bits), numeric keys (`SAK_NUM`: the value, 128 bits), keys of a combo (`SAK_COMBO`), the grouping set number (`SAK_SET`) |  |
| Aggregates | `count(*)`, `count(x)`, `count(DISTINCT row id)` of an unnest, the outer aggregates of two-level arms (`SAA_O_*`: counts, `sum` / `avg` of the cells, `count(DISTINCT)` of a key the arms group by), `sum` / `avg` of integers, float8 and numeric (leaf or expression), `min` / `max` of a leaf or a numeric expression, `count(DISTINCT)` of a dictionary or integer leaf, ordered-set aggregates (`percentile_*`: the histogram of values with their counts, or the sorted pairs of a spilled one), generic aggregates with a combine function (`stddev`, `var`, `corr`, `bool_*`, `bit_*`, `array_agg`, `string_agg`, user aggregates with `combinefunc`; not with a native float `sum`/`avg` in the query), any `FILTER` | `count(DISTINCT)` and `sum` / `avg` with `DISTINCT` of a numeric expression (a set of 128 bit values per group), mapped aggregates (`SAA_M_*`: values of a lookup table), generic aggregates that are ordered, `DISTINCT`, over a joined column, hypothetical or whose functions are not parallel safe or have no combine function |
| Shape | row filters of every kind, element filters, unnest, `sum(x * y)` programs, lookup joins and combos, grouping sets, virtual leaves (`jsonb` member by key), two-level arms (per-order aggregates) | a child plan run per combination of leaves (a correlated sub-select the scan cannot read as the table of a unique key), a hashed sub-plan in a lookup expression (`NOT IN (SELECT ..)`) |

A worker loads the joins' lookup tables itself with the leader's snapshot (a few rows each) and evaluates its combos' slots; a combo's output rows hold ids of the key tables, so a lane forgets them with the table. A numeric sum's partial table carries the 128 bit state; grouping sets are groups of the set number (an integer key); an ordered set sends its histogram as (group, value, count) runs, the leader adds the counts into one histogram (or a tuple sort once it does not fit `work_mem`) and `percentile_*` walks the sorted values once as in a serial scan. Generic aggregates (`SAA_GEN`) with a combine function are dumped as partial states and merged by key value with it, morsel based like the exact kinds; the others run on the leader alone, and a query with any generic aggregate is never fused with other arms.

The serial kinds keep state in structures that are not dumped and have no merge defined; the scan is one item over all the superblocks into one table, with float sums in tuple order.

- A mapped aggregate keeps a lookup table's values that its sets refer to by id, per process, and adds float8 and numeric sums in its hash table's order, which two workers cannot reproduce (nor a numeric's scale when equal values differ in it).
- A hashed sub-plan (`x NOT IN (SELECT ..)`) and a child plan run per combination of leaves live in the leader's executor: a worker has no plan state to start them in.
- A lateral `LIMIT 1` child of a unique key is read as a table (`First Row Lookup Tables`) and shared like one. A child with no `ORDER BY` whose key has no unique index, or that tests the joined row alone (`AND f.flag`), keeps the child for a key whose rows differ in the columns it returns: the table is read once the child has run for an eighth of the rows of the table (or at once when a cache holds it), a key whose rows agree is answered from it and the others run the child (`First Row Lookups`, `First Row Keys Differ`). PostgreSQL marks a lateral sub-select at the aggregate's own query level parallel restricted, so workers appear only inside arms (a query level of their own). A correlated child that runs for each distinct input is a probe at a random place of its index: when its runs have read a sixteenth of a relation it scans (an index or a heap, from the OS, at least 1,024 blocks in all) and take 25 microseconds or more each, the rest of that relation is asked of the OS in block order (`Child Blocks Prefetched`, `PrefetchBuffer`; no setting, and a relation of more than 1,048,576 blocks is left alone), so the probes find their pages in its cache: 300,000 entries, 25,000 distinct inputs probing an index of 14,600 pages of a 2,400,000-row mapping table, cold on an NVMe device, 0.95 to 2.7 s down to 0.55 to 0.6 s, the I/O wait 0.72 to 2.3 s down to 0.32 s (an interleaved run on the same data); warm and tmpfs runs never reach the trigger.

The per-order arms run in workers. They hold a cell per (arm, group) while a row is read and flush it into the outer accumulators at the row's end; a row is in one item, so an item flushes its last row's cells before its table is dumped (`arms_flush`). Outer counts and sums add; the `count(DISTINCT)` of a key the arms group by travels as (group, key value) pairs to which the leader gives its own key ids; cells' float sums use the lanes; an inner `avg(float8)` guard travels with the tables (largest input per arm). Test `aggstub_scan` checks that every parallel kind gets workers and gives the one-process scan's and the normal plan's rows.

EXPLAIN shows `Workers Planned` and `Workers Launched`; `Units`, `Rows Scanned`, `Tuples Aggregated`, `Units Pruned` and the heap counters add the workers'.

## Filters in the source

The row conjuncts that compare one scalar leaf of the rows with a constant are offered to the source before the scan (`filter_init`, `sift_colsrc_set_filters`, `SiftSrcFilter`), and the `sift_col` provider applies the ones it takes on the stored chunks. It tests the encoded values (`sift_col_filter`): bit-packed and FOR chunks are compared as packed integers by the range kernels, dictionary chunks through a bitmap of the matching codes, and nothing is decoded for the predicate. The forms:

- `=`, `<>`, `<`, `<=`, `>`, `>=` of an `int2`, `int4`, `int8`, `date`, `timestamp`, `timestamptz` or `bool` leaf with a constant or parameter, and of a `numeric(p <= 18, s)` leaf as its scaled integer (`amount > 100` is `value > 10000` at scale 2; a constant between two values of the scale rounds the comparison the way `numeric_operand` does; one that every or no value satisfies becomes "not null" or no row);
- `IN` of up to 8 integers (a longer list stays a table lookup in the executor: the engine would decode it).

Text leaves take their own path (`prune_str`, `filter_str`, "Text of any length"). A filter is exact: a NULL never passes and the executor does not retest the rows. A unit whose leaf has overflowed values is filtered on the NULL the index keeps for them, after the check above found no row of the snapshot holding one (a numeric leaf's one overflow value, NaN, is stored as NULL with the flag and passes `>`, `>=`, `<>` and not-null only, as in the executor). Float leaves, `OR`, `NOT IN`, element conjuncts, late (per-order) conjuncts and expressions stay in the executor. Every accepted filter also goes to `sift_col_begin`, so units whose min/max zone refuses one are skipped unread.

A `sift_table` offers the same filters to every segment layout (`tbl_set_filters`) and applies them with the same code (`agg/chunkfilter.c`); it takes a filter only when every layout holds the column as an integer leaf, so a column added after the first groups were written stays with the executor.

Per unit `col_filter` orders the filters by the share of rows each keeps (estimated from the leaf's zone: values spread evenly between min and max, a text leaf by its codes), most selective first, and stops at the first that leaves no row: no other leaf is decoded, the heap is not vetted. A chunk pass costs the same whatever is left in the selection, so the order matters only for that early stop.

Survivors are read for their rows only: when fewer than a thirty-second of the unit's rows still count, the executor reads a scalar leaf with `sift_colsrc_read_sel` (`sift_col_read` with a selection: packed values gathered at those positions, the chunk not decoded) into the unit's zeroed vector, which aggregate inputs, keys and the conjuncts left in the executor use as the whole leaf. `EXPLAIN ANALYZE` shows `Filters In Source` (conjuncts the source took). The test provider takes none (`set_filters` is empty); `sift_table` takes the integer filters and the text tests of its layouts (a column a group lacks stays with the executor). What the source does not take keeps the executor's `cmp_int` kernels (the `range` kernels at int32 and int64 width; scalar, AVX2 or AVX-512 chosen at run time).

### Filtered counts of the store benchmark (average-shipped-order, big-recent-orders)

average-shipped-order is `avg(amount)` where `status = 'shipped' AND country = 'DE' AND created_at` in one year; big-recent-orders is `count(*)` where `amount > 100 AND created_at >= '2025-12-02'`. The store generator hashes every predicate column, so no column follows the heap order and the zone maps prune nothing (`Units Pruned 0`); on a heap clustered by `created_at` (or `(country, created_at)`) the timestamp bound and the year range prune to the window's units, `amount`, `status` and `country` alone never do. What remains is decoding and testing rows, which the source filters cut to 14.3 instructions per row for average-shipped-order and 9.6 for big-recent-orders; the rest is unit page vetting and the engine's chunk reads (Requests, Source filters).

## Containment of an object document (`@>`)

`doc @> '{"b": "x", "n": 3, "o": {"p": true}}'` on an object document is a test of the leaves of the paths below `doc` (`build_object`): a string equals its leaf, an integer an integer leaf, a boolean a `bool` leaf, an object is the same of its members, an array is contained by the list leaf of its path. A member the leaf cannot compare alone is answered through the kinds the chunks keep, as a top-level condition of the WHERE (`contains_conjuncts`): `"k": true` is `doc ->> 'k' = 'true' AND jsonb_typeof(doc -> 'k') = 'boolean'` on an `astext` leaf (a string `"true"` is not a boolean), `"k": "true"` or `"k": "12"` the same text with the type `string`, `"k": null` is `jsonb_typeof(doc -> 'k') = 'null'`; the other members stay a containment of what is left of the pattern. Under an `OR` or a `NOT` the pattern is not split (a type test is a conjunct only) and keeps the normal plan, as does a number on an `astext` or `float8` leaf (the text of `1.50` is not that of `1.5`, and a double does not tell two numerics apart). Test `agg_jsonb_contain_object`, bench case `agg_jsonb_contain_object`.

A containment or a key test (`?`, `?|`, `?&`) that names a path no leaf of the index holds is refused as `jsonb_path_not_declared`, and the notice names the path (`doc.o.zz`): declare it in `paths` (`sift.suggest_paths` writes the spec) to give the scan a leaf to test. `x ? 'k'`, `x ?| array['a', 'b']` and `x ?& array['a', 'b']` (an OR or an AND of `?`) of the document column itself, every key a leaf of the index (not the member of a path) is the type test below on the leaf of `x.k` that declares the path (any JSON type but none, a JSON null too), with the documents that are no object read again from the heap: an array holds the key when it has the string, a string when it equals it (`Rows read again`), and an `astext` or typed leaf reads as missing for them exactly as for an object without the key. See "Key exists" below.

## Key exists (`doc ? 'k'`)

`doc ? 'k'` (and `?|`, `?&`, which are an OR and an AND of `?`), with `doc` a `jsonb` column and `k` a path of the index (`paths = 'doc.k int4'`, any leaf type but a wide one), is pushed as the type test `jsonb_typeof(doc->'k') IS NOT NULL` of the leaf plus the documents that are
no object: an array has the key when it holds the string, a string document when it is the string, which the leaf does not store (the shredder keeps an array as the overflow of every leaf below it, and, from 4.2, a string document too, flagged by
`SIFT_SEG_ROOTS` of the segment; `docs/FORMATS.md`). Those rows leave the scan and are read again from the heap with the key tested as the document says (`Rows ... re-read from the heap` in `EXPLAIN`), so the answer is `?`'s for every kind of document:
object, array of strings, mixed array, string equal to the key or not, number, boolean, JSON null, empty object or array, SQL NULL. The native evaluation of `?` decodes every document (about 700 cycles per row on 600,000 rows of 2 KB), the pushed test
reads one chunk of the leaf. The query may read the value of the key beside the test (`sum((doc ->> 'k')::float8) FILTER (WHERE doc ? 'k')`, a comparison of it, a group key, `CASE WHEN doc ? 'k' THEN (doc ->> 'k')::float8 END`) when the leaf's read can be done again from the heap (the scalar reads of `agg/from/recheck.c`: `(doc ->> 'k')::T` with T the leaf's type, `doc ->> 'k'` of a text or astext leaf, the cast of `doc -> 'k'` to float8): a document that is no object reads NULL, as the leaf says, and the rows that hold a value the leaf cannot store are read again for the read, whose error is raised where the row reaches the expression. Both are read again in one pass (`SRK_HASKEY` beside the read mode, `SRK_WITH_HASKEY` in the spec). The document itself (`doc`, `doc::text`) or a path below the key keeps the normal plan.

The test is answered wherever it stands, not only as a conjunct of the WHERE: a branch of an OR or an AND, the FILTER of an aggregate, a CASE (`sum(CASE WHEN doc ? 'k' THEN x ELSE 0 END)` is a FILTER and its `IS NOT TRUE`, `agg/expr/casefilter.c`),
`NOT` and `IS NOT TRUE`. Outside the conjuncts the source gives the rows that pass as a bitmap of the unit (the way the tests of ranges and wide values are given, `SAP_WIDE`), and the rows of the other types are the complement of the types:
a missing key and a NULL document are both the type none, so `(doc ? 'k') IS NOT TRUE` and the ELSE of a CASE are exact for a NULL document too, while `NOT (doc ? 'k')` (NULL for it) is answered only when the column is NOT NULL.
Tests `agg_jsonb_key_forms` (38 forms on a nullable column, a NOT NULL one, old segments, writes, promote, VACUUM and two workers: the rows equal the scan off) and `agg_jsonb_key_read` (the reads beside the test, on documents of every kind and a value the leaf cannot store of five kinds, old segments, writes, VACUUM). Not answered, and so on the normal plan: a read of the document or of a path below the key beside the test (the document is no leaf, and a path below the key cannot be declared together with the key), a `jsonb_typeof` test of the same path beside a read, a key that is no leaf and the empty key (which no path declares). `jsonb_typeof(doc->'k')` in the same places was already answered (a virtual leaf). A comparison of the read inside an `AND` of the same FILTER (`sum(x) FILTER (WHERE doc ? 'k' AND (doc->>'k')::float8 > 3)`) is answered: the key test is dropped from the reads of the leaf as a conjunct, and the rows the leaf cannot read pass the comparison and raise where they reach the aggregate. A `jsonb_typeof` guard
around a read of the same path (`CASE WHEN jsonb_typeof(doc->'k') = 'number' THEN (doc->>'k')::numeric END`) stays normal: the leaf cannot hold the values the guard skips, and a value read again from the heap (a string `"3"` in a float8 leaf) is stored as a number, so the guard sees the type of the leaf, not of the document; a chunk keeps the JSON type only for an overflow slot, so keeping it takes a format change.

- **Old segments.** A segment written before 4.2 has no `SIFT_SEG_ROOTS`: it stores a string document as a missing key, so all its NULL slots of the leaf (every row without the key) are read again from the heap, and the plan is priced with them
  (`rootless` of the leaf statistics, the share of the rows in such segments, at `AGG_RECHECK_ROW` each): a table that is old throughout and has few documents with the key stays on the normal plan, a mixed one pays for its old share only.
  VACUUM flags the old segments (one pass over the heap with every version of every row, which VACUUM reads anyway, writing only the directory; autovacuum's insert threshold reaches a loaded table that is never updated) unless the heap has a document that is a string, in which case they stay as they are and the
  priced read of the heap answers exactly. Test `agg_root_key_exists` (every kind of root, writes, an old segment next to a flagged one, the VACUUM).
- **Documents that are a string.** Since 4.2 a table with a document that is a JSON string at the root rereads those rows when a recheck-capable read touches the leaves below it, as it always did for arrays (`reread` of `agg_scalar_dirty` rose by the string roots).

## The JSON type of a path (`jsonb_typeof`)

`jsonb_typeof(x->'k') = 'number'` (also `'string'`, `'boolean'`, `'object'`, `'array'`, `'null'`), `jsonb_typeof(..) IN ('string', 'number')`, their complements `<>` and `NOT IN`, and `jsonb_typeof(..) IS [NOT] NULL` are conjuncts the source answers (`SAP_TYPE`, `typeof_test`, `compile_typeof`, `sift_colsrc_filter_types`; tests `agg_jsonb_typeof`, `agg_jsonb_typeof_parallel`, the `k_typeof_*` fixtures). The argument is a path of the jsonb value (`->`, `#>`, nested), not its text, naming a scalar leaf of the index (`bool`, `int2`, `int4`, `int8`, `float4`, `float8`, `numeric`, `text`, `astext`; not lossy, not a key of a map or a list element).

- **An `astext` leaf** holds the text of a string, number or boolean, so stored values have no type of their own: the chunk keeps the kind of each number and boolean, a string is the leaf's own, an object or array is an overflow value with its tag, a JSON null a NULL slot of its own kind; a `'12'` that is a string stays a string. A minor 3 segment has no kinds, so a test telling an astext leaf's types apart raises there with the `REINDEX` hint, as a NULL slot does for `'null'`.
- **What it keeps.** Rows whose path type is in the set of seven: `string`, `number`, `boolean`, `object`, `array`, `null` (a JSON null) and none (a missing key, a NULL document, a path leading nowhere: `jsonb_typeof` is NULL). A stored value has its leaf's type (a `float8` leaf holds numbers), a value the leaf could not hold the type its chunk kept, a NULL slot the chunk's kind (`null` or none). `= 'a'` and `IN` are the listed types; `<>` and `NOT IN` the others **but none** (NULL, which no comparison keeps); `IS NULL` is none, `IS NOT NULL` every type but none. A name that is no JSON type is no type (`= 'bogus'` is not pushed, `<> 'bogus'` is every type but none).
- **Errors.** Rows of any other type are dropped unread: a string among a `float8` leaf's numbers does not raise the overflow error other reads of that leaf raise (`tolerate_ovf`). When the query also reads the leaf's values (an aggregate, a key, another conjunct: `SIFT_SRC_JT_READ`), a kept row whose value the leaf cannot hold (a number out of the float range, a non-integer in an `int4` leaf, any string, boolean, object or array on a leaf of another type) raises the pushdown's error, unless no snapshot sees the row (the unit's page is not all-visible: `sift_col_vet` runs on those rows only). When it does not (`count(*) WHERE jsonb_typeof(m) <> 'number'`), rows are counted by type and nothing raises. A row whose type the chunk does not know (an overflow value without tag, a NULL slot or astext value of a unit without kinds, from minor 3) raises with the `REINDEX` hint whatever the query reads. The filter runs first, on the unit's live rows, before the other integer filters: an error is possible for a row another conjunct would drop, never the contrary. A sum or other read of the same leaf with no type filter guarding it keeps raising for the strings.
- **As an expression.** Elsewhere `jsonb_typeof(x->'k')` is a text leaf after the source's (`SiftAggVLeaf.types`, `types_leaf`, the leaf `#typeof`, `read_types_leaf`): per row the type's name (a dictionary of six, NULL where the path has no value) read from the chunk (`SiftSrcProvider.read_types`, `sift_col_row_types`), no leaf value read. So it is a group key (at most seven groups), a branch of `OR`/`NOT`, an aggregate `FILTER`, a `CASE`, or the argument of a function (`upper`, `coalesce`, `LIKE`, a comparison), through the text leaves' lookup tables. Semantics are PostgreSQL's: no value at the path is NULL, a JSON null is `'null'`. These shapes decode the type vector and evaluate per distinct name.
- **Not taken.** A name no row has as a conjunct (`= 'bogus'` goes through the expression above). A type test in an expression (`FILTER`, `CASE`, `OR`, a key) **that also reads the path's values** (`sum(x) FILTER (WHERE jsonb_typeof(x) = 'number')`): only a conjunct guards the read and the leaf cannot give the strings among its numbers, so the plan is the normal one (`k_typeof_not_pushed`). A path of an element of a list or map. A `sift_table`, whose jsonb column is one opaque leaf with no leaf of the path (it needs declared paths, `docs/COL-TAM.md` section 11; the provider only forwards `filter_types`, `read_types` and `tolerate_ovf`). The stub source.
- **Where it runs.** `process_unit` calls the source before the integer filters (`type_filters`); remaining rows are read as for those filters. Workers do the same on their units, and a BM25 match set is expanded to stored rows like other filters. A unit's pre-aggregates do not answer a query with it. `EXPLAIN` shows the conjunct in `Filters` and, with `ANALYZE`, `Type Filters In Source`.

## Zone pruning

The row conjuncts comparing an integer leaf of the rows (`int2`, `int4`, `int8`, `date`, `timestamp`, `timestamptz`, `bool`, and a `numeric(p <= 18, s)` leaf as its scaled integer; not a jsonb element, not a lossy leaf) with a constant or parameter by `=`, `<`, `<=`, `>`, `>=`, or `IN` of a list that fits a table, become per leaf an interval `[lo, hi]` (`sift_aggspec_zone_cmps`, `prune_init`: operand evaluated once per scan, comparisons of one leaf intersected; `<>`, `NOT IN`, element conjuncts and float leaves unused; an `OR` is tested disjunct by disjunct, below). A conjunct taken as a source filter (above) also goes whole to `sift_col_begin`: an integer `IN` is tested against its members, so a unit lacking the value is skipped.

The executor passes the intervals to `sift_colsrc_begin` as `SiftSrcPrune`. The `sift_col` provider turns each into two comparisons (`>= lo`, `<= hi`) for `sift_col_begin`, which skips units whose min/max zone map excludes either before any leaf is decoded, and counts them (`Units Pruned`). The executor still applies every conjunct to the rows of units it reads, so pruning only removes work, for every spec, parallel or not.

An `OR` of such tests (two ranges, a list of equalities, `BETWEEN`s, an AND of ORs, an OR of ANDs of several leaves) is a `SiftSrcPrune` holding its disjuncts: the provider (`sift_col_begin_zones`) prunes a copy of the kept units with each disjunct and keeps the union, so a unit is read when any disjunct may match. A disjunct no zone can refuse (a text test, an expression of the leaf, `<>`) leaves the `OR` untested. `agg_cost` prices an `OR`'s units as the sum of its disjuncts' shares (at most 1). The rollup path does not use the `OR`: it answers a unit whole only from plain comparisons (`SiftSrcPrune` entries without kids).

**Pricing.** The share of units kept for one comparison is `f + (1 - f) * (1 - corr^2)`: f is PostgreSQL's selectivity estimate (`clause_selectivity`: the column's MCVs and histogram, taken when the conjunct is compiled), corr the heap column's correlation with heap order from `pg_statistic` (0 when unknown, and for jsonb paths and index expressions). The most selective comparison counts, and at least one unit is read. A column that grows with the heap (ids, insertion time) has |corr| near 1 and a range query is priced by its range; an unclustered column by the whole table. Leaf read, predicate and unit terms scale with the share; tuples and aggregate updates already follow the selectivity. Numeric comparisons and `IN` lists count like integer ones.

For comparisons and short `IN` lists with a constant at plan time the share is counted on a sample of the index's real zones (at most 2048 units, the same units in every integer leaf, `SiftColZones`): a unit stays when every conjunct's zone meets its values, so two windows no unit meets leave about none. A conjunct with no constant (a parameter) or a source without zones keeps the estimate above. A unit keeping one row in eight or fewer is read at those rows only (docs/COL-SEARCH.md), so leaves no filter tests are priced for the share of passing rows (`AGG_SPARSE_LEAF`).

### Statistics request

`SiftSrcStats` of the `sift_col` provider carries the row and unit counts, the visibility share, `ndistinct` (a text leaf with no inline unit: its largest segment dictionary, a lower bound over several segments; an integer leaf: bounded by its range), `min` and `max` of integer leaves, `elems_per_row` of group leaves and `zone_span[leaf]` (requested as `zone_width[leaf]`), the mean over units of `(zone.max - zone.min) / (leaf max - leaf min)` (0 for a perfectly clustered column, 1 for a random one). With it the share of units a window of fraction f of the range overlaps is about `min(1, f + zone_width)`, against `f + (1 - f) * (1 - corr^2)` from correlation alone. Two more are requested for each integer leaf, computed at build or ANALYZE from the zone maps the index keeps (nothing read per query):

- `zone_hist[leaf]`: 32 equal-width buckets of the range, each with the number of units whose zone overlaps it, to price skewed ranges and `=` on a clustered column exactly.
- `inline_frac[leaf]`: the share of a text leaf's rows whose unit stores the strings inline (the writer knows it per unit; `sift.col_leaves` counts `inline_chunks`), which `inline_text_cost` would use instead of the heap column's average width.

## Text of any length

A unit stores a text leaf's strings inline when one is over 255 bytes; the scan hands it to the executor as a dictionary of its own with a fresh id, so group keys, `count(DISTINCT)`, `min`/`max` and lookup-table filters work on text of any length (fixtures `t_text_long*`, `t_text_inline_*` with strings of 250 to 600 bytes: filters, groups, `min`/`max` in the C collation, `count(DISTINCT)`); there is no error path for long strings. Caches keyed by dictionary id (`DictSlot`: lookup tables, key maps) are a ring of `NDICT_SLOTS` entries freed on reuse, so a new id per unit costs a rebuild per unit and no growth.

A text test on a plain `text`/`varchar` column goes to the provider (`prune_str`, `filter_str` of `agg/colsrc.h`) instead of the unit's dictionary: `col = const`, `col <> const`, `col = ANY (consts)`, `col <> ALL (consts)` for every deterministic collation, `col < <= > >= const` in the C collation, and `col LIKE 'prefix%'` (no other wildcard). `sift_aggspec_str_test` recognizes the shape in the lookup-table predicate, `str_init` hands the tests to `prune_str` once per source (each worker), and `process_unit` narrows the unit's rows with `filter_str` before anything is read; when the provider answers true the leaf is not read for that conjunct. `sift_col` runs it as `sift_col_filter` on the encoded chunk: an inline unit's zone prunes by the prefix keys and a selective equality decodes the prefix blob and lengths only. A `NOT IN` list with a NULL, a Param list and any other expression keep the lookup-table form. `sift_table` has no callbacks yet (`prune_str`/`filter_str` NULL: the executor tests the values itself, correct but slower for inline units).

Reading a leaf of long strings builds the unit's dictionary (about 0.5 us a row), which the heap scan beats when few rows pass or the aggregate is cheap. So `agg_cost` charges a leaf of a text column of average width 200 bytes or more (`pg_statistic`; jsonb paths and expressions are not priced) 56 units a row when anything but a pushed text test reads it, 3 units when only a pushed test does (`inline_text_cost`). The planner does not know a column's mix of dictionary and inline units (see Statistics request). Measured: the pushed equality is 4.7 times faster than the plain plan and the group-by-text 2.3 times, while the two shapes above that keep the normal plan are 13 and 1.3 times slower on the scan.

## Exactness

Integer, date, timestamp, dictionary, boolean and numeric results are exact. `sum(int)` is int8; `avg(int)` is the `numeric` division of the exact sum and count.

**Float `sum` and `avg`** add in lane order (Parallel scan) per group, with or without workers; a spine or a correlated child plan adds in tuple order when its plan has no workers and in lane order when it has. The result does not depend on the SIMD level or the worker count (those two scans apart, between a plan without workers and one with). It can differ from the normal plan (heap order) by the rounding of reordered float addition (comparator tolerance: relative 1e-9, absolute 1e-12), and, for those two scans, a plan with workers can differ from one without in the last digits. Bound: any order of n float additions is off the exact sum by at most (n - 1) 2^-53 of the sum of the absolute values, so two orders differ by at most 2 (n - 1) 2^-53 of it; relative to the result that is large only when the terms cancel (on the replay statements at 1,000,000 rows the difference was at most 4e-13 of the result). `agg_par_lanes` checks the bound on terms of 1e16 that cancel. PostgreSQL's parallel float sums differ the same way, and between runs; every other aggregate (integers, numeric, counts, min, max, count(DISTINCT)) is exact in any plan. `max_parallel_workers_per_gather = 0` or `parallel_workers = 0` on the table keeps the earlier sums of those two scans.

**Generic aggregates** are exact where the function is (numeric, integers, text, dates, booleans, bits, `array_agg` of a set with a following sort); float accumulation (`stddev`, `corr`, `regr_*`, `sum(float4)`) differs in the last digits from the normal plan (comparator tolerance, relative 1e-9), because additions are reordered or, in parallel, regrouped by worker. Errors are the function's, raised at the same value.

**Group order** is not stable between a serial and a parallel run (workers merge in launch order, a hash table lists groups by slot): a query that needs an order has an `ORDER BY`, as with the normal plan.

**Float8 errors.** PostgreSQL's float8 aggregates raise errors a plain double sum hides:

- `sum(float8)` raises the overflow error when a group's sum is infinite and none of its inputs was.
- `avg(float8)` keeps a sum of squares (`float8_accum`) that overflows first. The scan does not keep it and raises the same error when inputs are large enough to have overflowed it (`8 * n * max(x)^2 >= DBL_MAX`). The one input where the normal plan does not fail although the bound is met (all values equal and above 1e150) fails here too; set `sift.enable_agg` off for it.
- Overflow errors inside a float8 expression argument are not reproduced (the result is infinite).

**Values the index cannot hold** (a jsonb member that does not fit the declared type, a JSON string in a number path, text over its declared `text(N)`: the shredder's overflow) are not read from the leaf. Where rows are not read again from the heap (below), the scan raises `0A000` with a hint, instead of the cast error (`22P02`) the normal plan raises on an invalid value.

- **Planning.** The planner refuses leaves the engine marks lossy and leaves that a unit flags with an overflow value (`SiftColLeafStats.has_ovf`, from the zones read for statistics, memoized per backend) or a write buffer holds one of. A NaN of a numeric leaf is not one: PostgreSQL evaluates it. A query reading a flagged leaf keeps the normal plan, which reads the value from the heap and gives its rows or its error (`col_agg_overflow`; a number out of its `int2`, `int4`, `int8`, `float4` or `float8` path's range is such a value: `col_agg_overflow_range`).
- **Rows read again.** Only for the columns of a `jsonb_to_recordset` the scan reads those rows again from the heap: the value a row has depends on the reading expression (a record column or `->>` cast reads the string `"2"` as 2; `@>` and jsonpath compare it as a string), which the leaf does not record, so every other read of a flagged leaf keeps the normal plan. Flagged rows leave the unit's live mask and return after it as an in-memory unit made from the heap tuple (the snapshot's version, a HOT chain followed, the index expression evaluated again), shredded with the function's SQL. The error the function raises (a string that is no number, a list that is no array, an element that is no object) is kept and raised when the row reaches the expression, after its own conjuncts, so rows that do not reach it raise nothing.
- **Cost.** The planner charges `AGG_RECHECK_FRAC` of the rows at `AGG_RECHECK_ROW` operator costs when the leaf is flagged; `EXPLAIN ANALYZE` shows `Rows Read Again From The Heap`.
- **Clearing the flag.** The flag stays with the unit, dead rows included, until the segment is rewritten. `VACUUM` rewrites a lone segment that flags a leaf's overflow only in dead rows, whatever its dead share (`sift_cseg_stale_ovf`), restoring the pushdown without `REINDEX`. When `VACUUM` finds dead rows on under 2% of the heap pages it skips vacuuming the indexes, so `amvacuumcleanup` looks up the heap row of every row holding an overflow value (dead to every snapshot, following a HOT chain: `sift_cseg_reap_overflow`) and clears the gone ones' liveness before the merge picks (`col_agg_overflow_vacuum`).
- **Plans older than the value.** A plan made before such a value was written (a prepared statement's generic plan) is invalidated by the writer: the first row of a write buffer that overflows a leaf sets the leaf in the buffer's directory entry and invalidates the table's relcache entry in every session at once, committed or not (`col_agg_overflow_buffer`).
- **Scans already running** when another session writes such a value return the normal plan's rows: before a unit is filtered, rows holding such a value in a leaf the scan reads are checked against the snapshot (`check_overflow`: live ones, then the `sift_colsrc_vet` heap check for those rows only); a writer in flight, rolled back or committed after the snapshot leaves none to see (`agg_overflow`, an isolation spec). A row of the snapshot holding one still raises the error.

## SIMD

Kernels are chosen at run time through the `sift_cpu` rebind registry and `sift.simd`. The scalar table is the specification; the AVX2 and AVX-512 tables are bit-identical to it, so every level gives the same result. `sift_aggkern_for(level)` returns a level's table for the differential test; `scripts/coldiff.sh` runs the corpus at `sift.simd = scalar` and `auto`.

### Grouping sets and float keys

- **Sets**: one group table per set over the same key ids; the set is a key of the groups, so the empty set's group is one group and a data `NULL` is not a rolled-up key's `NULL`. `GROUPING(a, b)` is the bit mask of the sets (first argument highest bit); the empty set returns its one row over no input too. N sets are priced as N group updates per tuple; filters, leaf reads and unnest are paid once.
- **Float keys**: a float4/float8 column or expression is a key through its bits, with `-0` as `0` and every NaN as one NaN (`float8_eq` and `hashfloat8`). The index stores `-0` with its sign (the scan treats it as `0`), a value it cannot hold raises `0A000` as above, and the group is emitted as the type's value (`float4` stays `float4`).

## Cost

`agg_cost` prices the scan from the source's statistics: a leaf read, a predicate, a tuple passing the filters, a key id and an aggregate update, each in `cpu_operator_cost` (about 3 ns; constants fitted on whole table runs of one to three keys and aggregates, 17 ns per unnested element all in). The scan's share of the plan's price (everything above the native plan's own child and sub-plans) is multiplied by `SIFT_COST_AGG_SHARE` (0.93, `col/pg.h`): a planner unit is 1.75 µs in a hash join, 3.2 in a native aggregate and about 2.3 in a SiftAgg scan on a sift_col index, but 2.5 on a sift_table (scan over native: median ratio 0.96 over 106 shapes at 250,000 rows, records `planner-pl16b-units-table250k-20261103.tsv`; 0.66 to 0.72 on sift_col, `planner-pl16-units-stock250k-20261102.tsv`). 0.93 is the fit on a sift_table and a floor on a sift_col, not a fit there: lowering it to the sift_col fit changes no pick that is slower or faster by 20% and 1 ms in either lane (3 missed faster scans on sift_col, 4 on sift_table at every factor from 0.6 to 1.1), and below 0.8 sift_table picks the SiftAgg of `spine_range_join` (305 ms against 132 native at 250,000 rows; 30.6 against 22.6 ms at 30,000), below 0.9 that of `top_rows_per_kind_unindexed_column` (27.0 against 22.5 ms) and below 0.7 that of `spine_expression_key` (107 against 17 ms), which run at 4.2, 12.9 and 13.9 µs a unit against the median 2.5; pricing those three constructs up is a change of each, not of this share. BM25 keeps PostgreSQL's own prices (factor 1). This section holds the cost model once; other sections cite it.

- **Scan speed.** The operators the scan runs itself (reads, filters, tuples, keys, aggregate updates, worker merge) are charged `AGG_SCAN_SPEED` (0.2) of their count: shapes it runs alone take 0.4 to 0.9 ns for an operator counted at 3 ns. Lookup-table reads, children's plans and expressions PostgreSQL evaluates keep their price. `UNION ALL` arms run as one pass (`agg/fuse.c`) are priced as one under `SiftAggRows`; the others pay `AGG_FUSED_ARM` (0.1) of their price.
- **Units read.** The scan reads every unit the zone tests leave (Zone pruning; none on a column the heap order does not follow), so a selective query is its table scan against the btree plan's selected rows: the normal plan is kept for the 10 and 400 store sets, the scan taken for the 4000 store set and the whole table. Grouping sets, mapped aggregates and the outer WHERE add only their sets' and accumulators' update cost.
- **Heap vetting** of rows on pages that are not all-visible is added per page, over the pages of the units the zone tests leave (a table just rewritten by `CLUSTER`, `VACUUM FULL` or `pg_repack` has an empty visibility map).
- Elements per row of an array default to 3 when the source has no statistic.
- **Bytes read** (`scan_bytes`): the index's compressed bytes shared among its leaves by value width (4 or 8 bytes), 0.1 operator per byte (about 3 GB/s), times the share of units the zone tests leave; a source that does not know its bytes (a table) is taken to compress by half. A scan of wide columns of a big table is priced by what it reads; the heap plan keeps PostgreSQL's page and tuple prices.

Two terms keep selective queries on the normal plan (the scan reads every unit, the btree plan the selected rows):

- **Start-up.** 210000 operator costs (about 0.6 ms: opening the source, lookup tables, first units; 1 to 1.5 ms measured on a scan pruned to a few units), which a plan on a few dozen rows does not pay.
- **Unnest excess.** The scan of an unnest is charged the square root of the excess of the unnested rows the normal plan expects (100 per call of `jsonb_to_recordset`, `jsonb_array_elements`, `jsonb_each`, less what its filters cut) over the elements the selected orders hold (about 6 for an unfiltered unnest of 3 elements per row, 1 when an element filter cuts the expectation: whole-selective-item-stats); the normal plan's sort, join and aggregate costs grow with that excess though the data has a handful per row.
- A free key object's metric looked up by key (`metrics->>'m_total'`) is charged 9 operator costs per element of the object and key (`AGG_VLEAF_ELEM`). metrics-items-blend at K400 and the summed-`numeric` shape without a path flag at K400 keep the btree plan; metrics-summary and item-summary-rollup at K400 and every shape at K4000 keep the scan. Fixtures `p_cost_selective_unnest`, `p_cost_selective_numeric` and `p_cost_selective_metric_key` assert it (`expect: plain`).

**Rows of a key on a cut index.** The lookup of a key that repeats (`few_keys_lookup`: one parent, 167 rows of 300,000) reads every unit of the scan, because a key-cut index keeps its heap addresses only for the rows of a segment and the units of the other keys are not skipped. A directory of heap addresses per key would let the scan read the key's rows only (about 190 blocks, the btree plan's 187). It is not built: at 300,000 rows the scan reads 603 blocks in 1.7 to 2.3 ms against 187 blocks in 0.75 to 0.86 ms for the btree probe, so the directory would end in a tie with a plan the planner already takes, and the other shapes it could serve (a count and sum over one repeating key, a grouped count over three keys) already run on the btree plan in 0.5 and 0.25 ms. It would pay where a key's rows spread over most units of a table of several million rows, the case the scan wins by reading fewer bytes. The writer would need addresses on a cut index, a reader path through the bitmap, a chooser for repeating keys and a relaxed `point_lookup_only` gate; the plan-flip risk is the cost of that last change. (Counts and times from one local run, not from bench records.)

**Workers.** A path with workers pays `AGG_SETUP_FACTOR` (2) times `parallel_setup_cost`, the least a scan is charged for workers: starting four workers and opening the source in each takes 8 to 40 ms measured, so a scan the estimate puts under about 4,000 units stays in one process (a cost unit is worth about 0.5 us here, so the default `parallel_setup_cost` of 1000 alone would charge 0.5 ms). Ordered-set aggregates add the merge of their histograms, 1.5 operators per value (`AGG_ORDERED_MERGE`).

**Returned rows.** The node's returned rows are bounded by the groups its tuples can make (`rows_out` in `agg_cost`: the sets times the unnested rows it expects to aggregate), so the plan above is not priced for PostgreSQL's group estimate of an unnest (200 distinct values times 100 rows per call).

**JIT.** A plan with a SiftAgg node is not JIT compiled (`sift_planner`, `src/agg/cte/hook.c`): its cost, standing for 15 to 40 ms of scanning, is above `jit_above_cost`, and compiling the projections of nodes above (a window, a union) takes 3.5 to 5 ms (13 to 17 percent of the queries measured).

## Stays on the normal plan

What the planner refuses among the joins, the per-order form and a few jsonb tests, each once with the reason. The traps with a fixture (`t_not_in_subquery_correlated`, `t_key_expr_volatile`, `t_two_level_where_*`) must keep the plan and equal `sift.enable_agg = off`; the others are listed from the planner's refusals.

Every refusal has a slug, `sift.explain_refusals` and `sift.explain_why` name it, and `sift.stats()` counts it (`refused.<slug>`, and `limit.<id>` for a refusal by a fixed limit of [LIMITS.md](LIMITS.md)). The table is generated from `src/agg/refuse.h`; a `limit` is a query with more of something than the scan holds, `semantic` a shape the scan does not prove.

<!-- refusals:start -->
| slug | class | by design | the query kept the normal plan because | what to do |
|---|---|---|---|---|
| `other` | semantic |  | the shape is not one the aggregate pushdown proves, and no more specific reason is recorded | Try a simpler form of the statement (one table, a plain GROUP BY) to find the part that is refused, and report the statement if it should be accepted. |
| `disabled` | semantic | yes | sift.enable_agg is off | SET sift.enable_agg = on (it is off in this session, role or database). |
| `query_kind` | semantic | yes | the query has a set-returning function in the select list, row locks or a recursive CTE | None needed, the normal plan is the answer; take the set-returning function, FOR UPDATE or the recursive CTE out of the statement to get the scan. |
| `from_shape` | semantic |  | the FROM list is not one table with the inner, left, semi or anti joins and lateral functions the scan reads | Write the FROM list as one table with inner or left joins and lateral functions, or move the other sources into a sub-select the scan can read. |
| `table_kind` | semantic | yes | the table is inherited, sampled, row-secured or not a plain, materialized or partitioned table | None needed, the normal plan is the answer; the scan reads plain, materialized and partitioned tables that are not inherited, sampled or row-secured. |
| `no_source` | semantic | yes | no sift_col index or sift_table covers the table | CREATE INDEX .. USING sift_col on the table; sift.index_choices shows what it would hold. |
| `partition_source` | semantic |  | a partition has no column index the plan can use, or the partitions do not share a layout | Create the same sift_col index (same columns and options) on every partition, as CREATE INDEX on the parent does. |
| `partition_count` | limit |  | the table has more partitions left by PostgreSQL's pruning than a scan reads | Filter on the partition key so that fewer partitions are left, or query the partitions that matter. |
| `join_child` | semantic |  | a joined sub-select, VALUES list, CTE or function is not one the scan can read or run as a child | Join a table the scan reads, or write the sub-select, VALUES list or CTE as a single-table SELECT with a WHERE; the detail names the child. |
| `outer_join` | semantic |  | an outer join of the scanned table keeps rows the scan cannot reproduce | Use an inner join if the unmatched rows are not needed, or aggregate the joined table in a sub-select first. |
| `unnest_leaf` | semantic |  | the array or object a lateral function unnests is not a leaf of the column index | Add the array or object path to the paths option of the sift_col index (WITH (paths = '..')) and REINDEX. |
| `where` | semantic |  | a condition of the WHERE or ON clause is not one the scan can test | Test a column of the index with a comparison, IN, BETWEEN, LIKE or IS NULL, or add the column or expression the condition reads to the sift_col index. |
| `jsonb_path_not_declared` | semantic |  | a containment (@>) or key test (?, ?|, ?&) on a jsonb column names a path the index does not declare in paths, so no leaf holds its values | Declare the path in the paths of the index (sift.suggest_paths writes the spec), or test a path it declares; the detail names the path. |
| `join_condition` | semantic |  | a join condition is not an equality or filter the scan can look up | Join on an equality of columns of the scanned table and the other side, or an IN list, and filter the rest in the WHERE. |
| `group_key` | semantic |  | a group key is not a leaf, or an expression of one, that the scan reads | Add the column the key reads to the sift_col index, or group by the column itself. |
| `too_many_keys` | limit |  | the query groups by more columns than the scan holds | Group by fewer columns; the detail names the limit. |
| `error_parity` | semantic |  | the scan could raise, or not raise, an error for a value where the normal plan does the other (a cast of a JSON null before PostgreSQL 18, a guard of jsonb_typeof around a read) | None needed, the normal plan keeps SQL's errors; guard the cast so that it returns NULL, or use PostgreSQL 18, to get the scan. |
| `output_expr` | semantic |  | the select list or HAVING reads something besides group keys and the aggregates the scan computes | Select the group keys and the aggregates only, and compute the expression over them in an outer query. |
| `aggregate` | semantic |  | an aggregate, or its FILTER, DISTINCT or ORDER BY, is not one the scan computes | Use aggregates the scan computes (the list in USAGE) without the clause that is refused, or compute the rest in an outer query. |
| `distinct_on` | semantic | yes | DISTINCT ON keeps a row the scan cannot pick: more than four sort columns after the ON columns, a sort or select-list column of a joined table, or a volatile select list | None needed, the normal plan is the answer; keep the sort columns after the ON columns to four and the select list on the scanned table to get the scan. |
| `grouping_sets` | semantic |  | the arm does not group by grouping sets | Use a plain GROUP BY for this arm; the single-scan arm answers ROLLUP, CUBE and GROUPING SETS. |
| `dirty_units` | semantic |  | some unit holds values a leaf the query reads cannot store, and the scan could not read those rows again as SQL does | Find the leaf with values it cannot store in sift.col_leaves, fix the data or the type of its path, and REINDEX. |
| `dirty_order` | semantic | yes | a condition casts a path whose value some unit cannot store, and PostgreSQL may test it before a condition that would drop the row whose value raises, or after one that raises too | None needed, PostgreSQL's order of the conditions decides; put the guard in a CASE or a sub-select so that it runs first. |
| `volatile` | semantic | yes | the query calls a volatile function, which PostgreSQL evaluates for every row and the scan cannot reproduce | None needed, the normal plan is the answer; compute the volatile function outside the statement or in an outer query to get the scan. |
| `first_memory` | limit |  | the first rows of the groups would not fit in the memory of a hash aggregate | Raise work_mem or group by fewer values; the detail names the limit. |
| `group_memory` | limit | yes | the groups would not fit in the memory of a hash aggregate, and the scan cannot spill them, or the normal plan, which spills them, is estimated cheaper than the scan with its spill | Raise work_mem so that the groups fit a hash aggregate, or leave the normal plan, which spills them. |
| `lateral_limit` | semantic |  | a LATERAL sub-select with LIMIT 1 stays a child that runs for each key: no unique index holds its key, or its ORDER BY leaves ties | Add a unique index on the key of the LATERAL sub-select, or an ORDER BY that makes its LIMIT 1 unique. |
| `distinct_set` | semantic |  | count(DISTINCT x) keeps the distinct values of each group: x is not a NOT NULL column of the scanned table under a unique single-column index, so its rows cannot be counted in its place | Count DISTINCT over a NOT NULL column under a unique single-column index, or leave the normal plan. |
| `two_arms` | semantic |  | the arms of the two level aggregate are not UNION ALL branches of aggregates over one scan, each grouped by a unique row id | Write the arms as UNION ALL branches of aggregates over one scan, each grouped by a unique row id. |
| `two_keys` | semantic |  | a key of the two level aggregate is not one every arm groups by | Group every arm by the keys the outer aggregate groups by. |
| `two_aggs` | semantic |  | an aggregate over the arms is not one the two level scan computes | Use sum, count, min or max over the arms, or compute the other aggregate in an outer query. |
| `spine_shape` | semantic |  | the join of the arms to a spine is not an equality of their keys, or the WHERE reads the arms | Join the arms to the spine on an equality of their keys, and keep the WHERE off the arms. |
| `shape_width` | limit |  | the query has more joined tables, value combinations, leaves, arms or columns than the scan holds: the detail names the limit and its value | Reduce the joined tables, value combinations, leaves, arms or columns below the limit the detail names. |
| `spine_rows` | limit |  | the rows of the spine are estimated above sift.agg_lookup_max_kb | Raise sift.agg_lookup_max_kb, or filter the spine to fewer rows. |
| `rows_shape` | semantic |  | the grouping of the rows of a SiftAgg plan has no count(DISTINCT) of integers, or a key or aggregate it cannot group | Group by integer keys with aggregates the scan computes (count(DISTINCT) of integers), or leave the normal plan. |
| `topn_shape` | semantic |  | the ORDER BY .. LIMIT is not over the scanned table's columns with a condition the scan tests | ORDER BY columns of the scanned table, with a WHERE the scan tests, then LIMIT. |
| `window_first` | semantic |  | the first rows of each group, asked with a window function (rn = 1, rn <= N), are not a lone row_number(), rank() or dense_rank() with PARTITION BY and ORDER BY in a sub-select that does nothing else, over a table with a column index for N > 1 (rank and dense_rank: up to 1,000) | Write the first rows of each group as a lone row_number(), rank() or dense_rank() OVER (PARTITION BY .. ORDER BY ..) in a sub-select that does nothing else, kept with rn = 1 or rn <= N. |
| `cost` | semantic | yes | the normal plan is estimated cheaper than the scan | None needed, the normal plan is estimated cheaper; ANALYZE the table if the estimates look wrong, and compare with sift.agg_force = on (the row shows both costs). |
| `cte_cost` | semantic | yes | the shared CTE plan is estimated cheaper than the plan with the CTE inlined | None needed, the cheaper form was chosen; MATERIALIZED or NOT MATERIALIZED on the CTE picks the other. |
<!-- refusals:end -->

The shapes with a fixture:

| Shape | Reason |
|---|---|
| `FULL JOIN` to a table; `RIGHT JOIN` (PostgreSQL makes it a `LEFT` join of the other side) | the unmatched rows of the table would be output rows with no source row; the scan emits only tuples of source rows |
| a correlated or row-valued `NOT IN (SELECT ..)` | the hashed `SubPlan` reads the row, or tests two columns at once: no lookup table over one leaf. `NOT EXISTS` is a join (pushed) |
| a correlated `LEFT JOIN LATERAL (SELECT .. LIMIT 1)` lookup | the row PostgreSQL takes among duplicate keys depends on its plan, so the scan answers from a table only the keys whose rows agree on the columns read (and a unique key): write a join on the key |
| join to a sub-select with `GROUP BY`/`LIMIT`/`DISTINCT`, a CTE read more than once, `VALUES`, a function in `FROM` | PostgreSQL does not pull them up to a base table, so the right side is a plan, not a table readable at start; a child plan under the node is not run |
| the scanned table on the nullable side of an outer join (`spine s LEFT JOIN items t ON t.d <@ s.range`) | the scan would have to return a group for every row of the other table: aggregate first and join the spine above it, which is pushed |
| a lookup keyed from a column of another joined lookup | a second join level (one combo holds one table); a literal, a `CASE`, or a join of the lookup to the scan's own column is pushed |
| join with no equality between leaves and columns of the table (a range or inequality join, `ON true`) | nothing to hash the table by; each distinct tuple would scan the table |
| more than 3 keys in one join, more than 32 joined tables, more than 128 combos | the spec's limits (`SIFT_AGG_MAX_JOINS`, `SIFT_AGG_MAX_COMBOS`) |
| key or filter that is volatile (`random()`, `clock_timestamp()`) | PostgreSQL evaluates it per row; the combo cache once per distinct tuple |
| the same leaf with two types in one combo (`varchar` leaf compared with a `text` column and `IS NULL` of the leaf as `varchar`) | the combo types each leaf once; the relabelled use is another type |
| join key of text with a nondeterministic collation | the collation's equality is not byte equality of the dictionary entries |
| `sum`/`avg`/`min`/`max` of a joined `money` column | no hash opclass for the distinct-values table of mapped aggregates |
| `sum(float4)` of a joined column | float4 addition is order dependent: the distinct-values form adds `value * count` |
| `sum`/`avg` of other types of a joined column (`numeric` and `float8` are matched; `int2`/`int4`/`int8`, `interval` and `avg(float4)` through the PostgreSQL functions) | the aggregate is not in the allowlist of `mapped_pg_agg`; a function whose transition state is not a copyable datum cannot run per distinct value |
| volatile outer qual above the arms (`WHERE random() < 0.5`) | PostgreSQL evaluates it once per inner group; the scan would evaluate it per cell, in another order; fixture `t_two_level_where_volatile` |
| outer qual above the arms that mixes a group column of an arm with an inner aggregate (`avg_price > 100 OR kind = ..`) | the column belongs to the cell's outer group, known only when the cell is attributed; fixture `t_two_level_where_mixed` (the inner aggregate may still run in the scan) |
| outer qual that reads the columns of several arms of the `UNION ALL` | a cell belongs to one arm; the qual would need the other arms' cells of the same row |
| a non-null constant as the key of an arm (`SELECT id, 'z', avg(x) .. GROUP BY id`; `NULL` is pushed) | the arms are the cells of one outer key, and no arm's group expression holds the constant |
| `max`/`min` as the inner aggregate of an arm, or `FILTER` or `CASE` over a text leaf in one | the inner aggregates are `count`, `sum` and `avg`; the arms are pushed one by one and the outer aggregate runs in PostgreSQL |
| arms with more than `SIFT_AGG_MAX_KEYS` (32) keys in all: the outer keys, the cell keys and the number of the set | the grouping and arm masks have one bit per key |
| `x ? 'k'` of a path below another path, a comparison of jsonb values (`x['k'] = '"v"'`), `x['k'] IS NOT NULL` | a leaf stores NULL for a missing key and for a JSON null alike, and a jsonb comparison tells a string from a number: they are no function of the stored values. `x ->> 'k'` and the cast to the leaf's own type are pushed |