# Full constant array per child after partition pruning: where the cost goes

Thread #75, "Partition-aware simplification of constant IN lists after partition
pruning" (pgsql-hackers). It reconciles two measurements:

- (A) ours, 2026-10-09: with max_parallel_workers_per_gather=0 the full array
  inflates each child's estimate (706,992 vs 1,000) and the planner picks seq scans.
- (B) the follow-up from zthhh, 2026-10-13: the original IN query already runs as
  Parallel Index Only Scans, one row per partition, yet takes ~987 ms of execution
  against ~53 ms for a VALUES rewrite.

Nothing here is a patch. Every number is read from an EXPLAIN, a timing log or a
perf report in `logs/`. Figures that are computed rather than measured are labelled.
The in-place restriction is a **measurement-only prototype**
(`scripts/PROTOTYPE-restrict-saop.measurement-only.diff`).

## Setup

- Repro: the reporter's `partition_in_repro.sql` run unmodified (1024 HASH partitions,
  1M rows, VACUUM (ANALYZE)), and the follow-up's `rewrite.sql` used verbatim
  (`upstream/`). The VALUES list is checked to be exactly the case-B id list.
- Settings: shared_buffers=128MB, enable_partition_pruning=on, jit=off,
  autovacuum=off (static data). Parallelism is set per scenario.
- Builds, all `-O2` without asserts, on x86_64 (Zen 5):
  - r18 = REL_18_STABLE 30baaacdd1c (reports 18.6)
  - f2d7570 = f2d7570cdde (19devel, named in the thread)
  - master = bfa1a69edd7
  - proto = bfa1a69edd7 + the prototype, switched per session with
    `SET proto.restrict_saop = on`. With it off, it plans and times like master
    (`logs/structure_proto.jsonl`, `logs/summary.md`).
- Timing: carriles lane A, calibration sentinel per sample. 10 clean samples per
  variant, each one the median of 5 executions in a fresh session.
  - Client wall comes from psql `\timing`. Planning and execution come from
    EXPLAIN (ANALYZE, TIMING OFF) through a helper.
  - Reported as median [bootstrap 95% CI]. Comparisons use the same binary and run.
  - Two runs (master run1 gB, f2d7570 run1 gB) fell in a contended window (183 and
    211 samples rejected) and were repeated once the machine was quiet.
- Profiles: perf with dwarf call graphs, following the postmaster so the leader and
  every parallel worker are sampled. Percentages are inclusive, of all samples.

### Scenarios

| name | session settings | plan obtained (read from EXPLAIN) |
|---|---|---|
| ser | max_parallel_workers_per_gather=0 | 1000 x Seq Scan (the original report) |
| ser_idx | ser + enable_seqscan=off | 1000 x Index Only Scan, full array in each |
| par | max_parallel_workers_per_gather=2 | Gather, 1000 x Parallel Seq Scan |
| par_idx | par + enable_seqscan=off | **the follow-up's shape**: Finalize Aggregate / Gather (2 workers) / Partial Aggregate / Parallel Append / 1000 x Parallel Index Only Scan, full array, Index Searches: 1 each |
| nl | par + enable_seqscan/hashjoin/mergejoin=off | **the follow-up's VALUES shape**: Nested Loop / Values Scan / Append of IOS with run-time pruning, 1000 executed, 24 never executed |
| dpq | par_idx + parallel_setup_cost=1e9 + debug_parallel_query=on | the same serial plan under a single-copy Gather (1 worker): identical shape with and without the prototype |
| parF / parF_w0 | parallelism forced; w0 adds max_parallel_workers=0 | parF_w0 keeps the Gather but launches no worker |
| *_proto | + proto.restrict_saop=on | each child gets only its own constant |

The follow-up's attachment gives neither version nor settings. Its planner prices a
child IOS at 352 (ours: 25.9) and still picks it over seq scans. Its VALUES join
picks a Nested Loop costing 1,435,428 over a hash join. We did not try to infer its
cost settings. We reproduced its two **plan shapes** with the explicit knobs above
and say so.

## 1. Where the ~987 ms go: shipping the arrays to the workers, not index probes

In the follow-up's own plan, the Parallel Append finishes in 9.6 ms per process
(`actual time=0.026..9.616`, loops=3). But the Gather returns its first row only at
651.5 ms of 973.3 ms.

Same shape here (par_idx, master, TIMING ON, `logs/explain_master_IN_par_idx.txt`):
the Gather's first row arrives at 287.6 ms of 417.7 ms, while the 1000 child scans
together take 17.7 ms. That fraction (69 %) matches his (67 %).

Each child reports **Index Searches: 1**: one descent per child, not one per array
element. The "N probes per child" hypothesis is not what this plan does on 18+.

What the time is (perf, `logs/perf_master_IN_par_idx.{leader,workers}.txt`):

| process | inclusive % of all samples | function |
|---|---|---|
| leader | 41.6 | ExecInitParallelPlan |
| leader | 40.8 | nodeToString -> outDatum (each 1000-element bigint array printed byte by byte) |
| leader | 12.4 | scalararraysel (planning) |
| workers | 37.3 | stringToNode -> readDatum (parsing those arrays back) |
| workers | 3.2 | RelationBuildDesc (cold relcache, 1000 partitions) |
| both | ~2.6 | _bt_first (the index probes themselves) |

Measured split (proto build, run1 / run2, execution ms):

- Gather planned, **0 workers launched** (parF_w0): 289.9 / 280.3. The leader
  serializes the plan anyway and then runs it alone.
- Same plan **with workers** (parF): 424.6 / 409.5, i.e. +46.5 % [+44.6, +49.0].
  That extra is the workers deserializing the plan.
- Same serial plan, **no Gather** (ser_idx): 13.7.

So in that shape, about 276 ms is leader serialization and about 135 ms is worker
deserialization; the index scans themselves take about 14 ms. (Computed from the three
medians above.)

## 2. Reconciling the numbers: why forcing the index gave ~14 ms here and ~987 ms there

Same binary (master, run2), same full-array Index Only Scan plan, only parallelism
toggled:

| | planning | execution | client wall |
|---|---|---|---|
| ser_idx (no Gather) | 134.3 [131.3, 134.6] | **13.99 [13.90, 14.18]** | 163.1 |
| par_idx (follow-up's shape) | 85.3 [84.7, 86.3] | **419.1 [415.3, 422.6]** | 506.2 |

Execution +2894.8 % [+2850.0, +2928.2]; r18, f2d7570 and master agree (+2948 to +3101 %).

The two observations measure different things, and both stand:
- Ours (serial): the full array costs a bad estimate and, with it, a seq-scan plan.
- His (parallel): the full array is also copied into the plan that every worker
  receives, and that copy dominates execution even though the index is chosen.

Absolute numbers differ by machine and cost model:

| | planning | execution |
|---|---|---|
| his IN | 324.9 | 987.1 |
| ours, same shape | 85 | 419 |
| his VALUES (attached run) | 22.2 | 59.6 |
| his VALUES (quoted in the mail; not in the attachment) | 31.7 | 53.4 |
| ours VALUES | 10.3 | 13.3 |

What carries over is the shape: Gather-first-row fraction 67 % vs 69 %, Index
Searches 1 per child, 3000 shared hits in both.

Even serially, the full array still costs execution with the index (13.99 vs
5.86 ms restricted). Profile `logs/perf_master_IN_ser_idx.leader.txt`: per child,
`_bt_preprocess_keys` sorts and dedups the 1000 keys (`qsort_arg`,
`_bt_compare_array_elements`, `deconstruct_array`) before its single descent. That is
6.5 % of 10.1 % executor samples. It is per-child array preprocessing, not per-element
probing.

## 3. IN vs VALUES vs restricted array (planning + execution)

Same binary (proto) and run (run1Q), ms, median [95 % CI]:

| query | plan | planning | execution | client wall |
|---|---|---|---|---|
| IN, today (ser) | 1000 x Seq Scan, est 706,992 | 129.50 [128.14, 131.29] | 42.88 [42.51, 43.21] | 186.83 |
| VALUES join (nl, rows) | Nested Loop + run-time pruning | 10.59 [10.21, 10.85] | 13.47 [13.10, 13.80] | 25.44 |
| VALUES join (nl, count(*)) | same | 10.51 [10.21, 10.97] | 13.59 [13.40, 13.75] | 25.71 |
| UNION ALL split (earlier upper bound) | 1000 x IOS under Subquery Scans | 295.73 [294.43, 298.09] | 6.90 [6.60, 7.28] | 304.12 |
| **IN, restricted in place** (ser_proto) | 1000 x IOS, est 1,000 | 21.51 [21.23, 22.20] | **5.88 [5.80, 6.08]** | 28.95 |

Rows vs count(*) forms of the rewrite are within noise (+1.1 % [-3.0, +3.3] wall).

The ceiling of the in-place restriction, against today's IN, same binary:

| scenario | wall | planning | execution |
|---|---|---|---|
| serial, seq-scan case (ser -> ser_proto) | -84.4 % [-84.7, -84.0] | -83.5 % | -86.7 % [-87.1, -86.4] |
| serial, forced index (ser_idx -> ser_proto) | -81.6 % | -83.5 % | -57.2 % [-58.3, -56.1] |
| parallel allowed (par -> par_proto) | -91.7 % | -82.4 % | -97.3 % |
| **follow-up's shape (par_idx -> par_proto)** | **-94.0 % [-94.2, -93.8]** | -73.9 % | **-98.6 % [-98.6, -98.6]** |
| identical single-worker Gather (dpq -> dpq_proto) | -88.3 % | -77.7 % | -90.2 % [-90.4, -90.1] |

With the restriction, the planner no longer goes parallel at all: estimate 1,000,
cheap serial IOS. That is a large part of the parallel win (par_proto 30.6 ms wall
vs 510.1).

On a Gather of identical shape (dpq), the restriction still removes 90 % of
execution: 445.0 -> 43.5 ms. The Gather's first row moves from 413 ms to 35.5 ms
(`logs/structure_proto.jsonl`). What remains in the worker is opening 1000
partitions with a cold relcache.

- **In-place vs the UNION ALL bound:** execution is 14.8 % [-19.4, -10.7] lower in
  place (no Subquery Scan per child), so the earlier split was indeed an upper bound.
  Its planning on 19+/master is dominated by the unrelated `choose_plan_name`
  quadratic (separate thread).
- **In-place vs VALUES:** execution -56.7 % [-57.6, -55.0] (5.9 vs 13.6 ms: no
  run-time pruning step per outer row). Planning +104.7 % (21.5 vs 10.5 ms). Wall
  +12.6 % (29.0 vs 25.7 ms).

The prototype's 21.5 ms of planning is not what a real implementation would cost.
Profile `logs/perf_proto_IN_ser_proto.leader.txt`:
- The prototype function, inlined in `expand_partitioned_rtentry`, is 52.1 % of
  backend samples. `compute_partition_hash_value` alone is 19.2 %, because the
  prototype rehashes all 1000 constants for each of the 1000 children (10^6 hashes).
- Partition pruning, which already maps every constant to its partition once, is
  1.1 % (`prune_append_rel_partitions`).

**Computed, not measured:** reusing pruning's mapping instead of rehashing per child
removes most of that 52 %, which puts planning in the 7-10 ms range. That is the same
as the r18 UNION ALL split (10.76 ms) and the VALUES join (10.5 ms).

## 4. Planning: scalararraysel grows with children x constants (confirmed)

- Today's IN, serial: planning 129.5-133.3 ms on all builds.
- In-place restriction: 21.5 ms (-83.5 %).
- Profile of the follow-up's shape: scalararraysel is 12.4 % of all samples. Planning
  is a smaller share there only because execution became so large. In the serial
  profile of 2026-10-09 it was 92.6 % of planning.
- The children x constants growth (0.09-0.13 us per child-constant pair, flip to seq
  scan between 250 and 500 ids) was measured on 2026-10-09
  (our earlier message in this thread, 2026-10-09) and is not repeated here.

**Observed, not explained:** with enable_seqscan=off *and* parallelism allowed,
planning is ~35 % lower than serial with seqscan off (85 vs 134 ms, all builds,
narrow CIs). We did not investigate why; it does not change any comparison above,
which are all within one scenario.

## 5. Is pruning's mapping reusable for the hash case?

- **Measured:** pruning already computes, once, which partition every constant
  belongs to. `prune_append_rel_partitions` costs 1.1 % here, and restricting each
  live child to exactly those constants gives correct results: 1000 rows in every
  variant, checked per sample.
- **Measured:** the decision point exists. In `expand_partitioned_rtentry`, right
  after `relinfo->part_rels[i] = childrelinfo`, the child's partition index `i`, the
  parent's `boundinfo` and its translated `baserestrictinfo` are all at hand. That is
  where the prototype runs.
- **Not done and not claimed:** the prototype recomputes the hash per child instead
  of keeping pruning's per-constant result, and handles only a single-column hash key
  with a Const array. Range and list partitioning could instead go through
  partition-constraint refutation as in [2]. Hash cannot, since
  `satisfies_hash_partition()` is not refutable by predtest. For hash, reusing
  pruning's mapping is the available route.
- **Not done and not claimed:** whether the planner should shrink the array before
  `set_rel_size` (estimate), before `create_plan` (serialized plan size), or both.
  The measurements say both matter. The estimate decides seq vs index and parallel vs
  not. The array size decides how much each child preprocesses and how much the
  Gather ships.

## Files

- `scripts/`:
  - env.sh, setup_cluster.sh (ephemeral clusters; `--drop` removes them)
  - gen_queries.py, structure.py, show_structure.py, measure.py, run_all.sh,
    summarize.py, profile.sh
  - PROTOTYPE-restrict-saop.measurement-only.diff
- `upstream/`: both original attachments, the follow-up's rewrite.sql and result.txt.
- `logs/`:
  - summary.md: every median, CI and comparison
  - samples_*.csv: raw samples with sentinel status
  - explain_*: plans per build and variant
  - structure_*.jsonl
  - perf_*: leader and worker reports
  - original_script_*.out.gz