Hi zthhh,
> In my test, the original IN query already uses Parallel Index Only Scans.
> Each of the 1000 scanned partitions returns one row, with zero heap
> fetches, and all buffer accesses are hits. Nevertheless, execution takes
> about 987 ms.
You're right that the cost isn't just the seq-scan choice, and it isn't the
index work either. I reproduced your plan shape on 18.6, on 19devel
(f2d7570) and on master; all three agree.
Each child reports "Index Searches: 1" -- one descent per child, not one per
array element. The time is in shipping the plan to the parallel workers.
Under perf, the leader spends ~41% in nodeToString -> outDatum, printing each
child's 1000-element bigint array byte by byte, and the workers spend ~37% in
stringToNode -> readDatum parsing them back; the index probes are ~2.6%. In
your plan the Gather returns its first row at 651 of 973 ms; in the same shape
here, 287 of 418 ms, while the 1000 child scans together take 17.7 ms.
Toggling only parallelism on the same full-array index plan, execution goes
from 14.0 ms to 419 ms. The same plan with the Gather but 0 workers launched
is still 290 ms: the leader serializes the plan anyway, and the 1000-element
array is in every child.
Even serially, the full array still costs at execution with the index: 14.0 ms
vs 5.9 ms when each child carries only its own constant, because
_bt_preprocess_keys sorts and de-dups the 1000 keys per child before its
single descent.
So the restriction helps both cases, for different reasons: it takes the
estimate to 1 (which is what picked the seq scan in the original report) and
it shrinks what each child preprocesses and what the Gather ships. Measured
against today's IN, same binary: the serial seq-scan case is 186.8 ms wall
(planning 129.5, execution 42.9); your VALUES rewrite is 25.4 ms (planning
10.5, execution 13.5); and restricting each child's array in place is 29.0 ms
(planning 21.5, execution 5.9). That is an 84% wall drop in the serial case
and 94% in your parallel shape; with the estimate corrected the planner no
longer parallelizes at all. The VALUES rewrite is a good workaround that
needs no planner change.
For where a fix would live: pruning already maps each constant to its
partition once (prune_append_rel_partitions is ~1% here), and that mapping is
in hand in expand_partitioned_rtentry, where restricting each live child to
its own constants gives the right 1000 rows. Range and list partitioning
could instead refute the irrelevant constants through the partition
constraint, but hash cannot, since satisfies_hash_partition() isn't something
predtest can refute -- reusing pruning's mapping is the route that also covers
hash.
I couldn't match your exact cost settings (your child index-only scan is
priced at 352 vs 25.9 here), so I reproduced both of your plan shapes with
explicit knobs rather than inferring them.
Attached are the measurements (scenarios, timings with 95% CIs, and the perf
breakdown), the raw median/CI summary, and the throwaway prototype I used to
bound the benefit -- measurement-only, single-column hash, not a proposal.
The full logs and repro harness are available if useful.
Regards,
Manu
# 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
# Timing summary: median [95% CI of the median], ms
- dirty samples discarded: f2d7570/run1: 3
- dirty samples discarded: master/run1: 49
- dirty samples discarded: master/run2: 53
- dirty samples discarded: proto/run1: 77
- dirty samples discarded: proto/run2: 7
- dirty samples discarded: r18/run1: 3
| build | run | variant | n | client wall | planning | execution |
|---|---|---|---|---|---|---|
| f2d7570 | run1 | IN@dpq | 10 | 604.03 [601.41, 605.00] | 132.19 [129.51,
133.64] | 459.53 [458.66, 460.70] |
| f2d7570 | run1 | IN@par | 10 | 370.42 [369.20, 372.00] | 126.11 [124.62,
128.49] | 231.23 [230.13, 231.57] |
| f2d7570 | run1 | IN@par_idx | 10 | 523.55 [520.64, 525.83] | 84.77 [83.95,
85.37] | 438.95 [437.52, 440.92] |
| f2d7570 | run1 | IN@ser | 10 | 187.69 [185.21, 190.15] | 129.46 [128.72,
130.31] | 43.85 [43.45, 44.20] |
| f2d7570 | run1 | IN@ser_idx | 10 | 158.17 [155.09, 160.70] | 130.86 [129.38,
131.73] | 13.78 [13.65, 13.94] |
| f2d7570 | run1 | SPLIT@par | 10 | 361.96 [356.68, 365.27] | 319.03 [315.74,
322.00] | 36.85 [36.52, 37.15] |
| f2d7570 | run1 | SPLIT@ser | 10 | 335.49 [334.53, 339.94] | 324.00 [320.25,
326.00] | 6.97 [6.60, 7.19] |
| f2d7570 | run1 | VALUESc@nl | 10 | 26.92 [26.25, 27.50] | 10.95 [10.18,
11.42] | 13.85 [13.71, 14.26] |
| f2d7570 | run1 | VALUESc@par | 10 | 41.36 [40.40, 42.62] | 10.79 [10.43,
11.12] | 34.60 [34.20, 35.54] |
| f2d7570 | run1 | VALUESc@ser | 10 | 47.80 [47.25, 48.36] | 10.33 [10.03,
10.51] | 48.78 [48.47, 49.58] |
| f2d7570 | run1 | VALUESr@nl | 10 | 26.28 [25.38, 27.64] | 10.70 [10.56,
11.40] | 13.62 [13.43, 13.97] |
| f2d7570 | run1 | VALUESr@par | 10 | 41.80 [40.51, 42.70] | 9.84 [9.65, 10.10]
| 35.56 [35.12, 35.92] |
| f2d7570 | run1 | VALUESr@ser | 10 | 47.81 [47.18, 50.09] | 10.27 [9.65,
10.62] | 48.63 [48.09, 49.19] |
| master | run1 | IN@dpq | 10 | 594.79 [588.34, 601.23] | 86.11 [85.26, 87.12]
| 441.76 [440.28, 444.17] |
| master | run1 | IN@par | 10 | 367.57 [364.03, 373.94] | 129.74 [127.82,
131.36] | 224.37 [222.37, 230.93] |
| master | run1 | IN@par_idx | 9 | 510.27 [505.70, 513.59] | 86.69 [86.14,
87.84] | 424.06 [421.21, 427.58] |
| master | run1 | IN@ser | 10 | 193.63 [189.66, 195.30] | 133.22 [131.39,
134.51] | 44.38 [43.67, 44.97] |
| master | run1 | IN@ser_idx | 10 | 163.62 [162.06, 165.43] | 134.53 [133.20,
135.91] | 13.91 [13.76, 14.04] |
| master | run1 | SPLIT@par | 10 | 330.06 [329.06, 330.98] | 291.66 [289.93,
292.94] | 36.80 [36.32, 37.19] |
| master | run1 | SPLIT@ser | 10 | 304.55 [304.00, 307.03] | 297.76 [295.82,
298.90] | 6.91 [6.62, 7.23] |
| master | run1 | VALUESc@nl | 10 | 26.31 [25.73, 26.94] | 10.83 [10.68, 11.16]
| 13.56 [13.47, 13.70] |
| master | run1 | VALUESc@par | 10 | 40.80 [40.32, 41.94] | 10.63 [10.48,
10.80] | 34.87 [34.32, 35.48] |
| master | run1 | VALUESc@ser | 10 | 47.21 [46.78, 48.25] | 10.07 [9.70, 10.67]
| 46.62 [46.43, 46.87] |
| master | run1 | VALUESr@nl | 10 | 25.47 [24.95, 25.95] | 10.27 [10.04, 10.79]
| 13.32 [13.08, 13.56] |
| master | run1 | VALUESr@par | 10 | 41.55 [40.62, 42.31] | 10.53 [10.35,
10.75] | 34.70 [33.94, 34.96] |
| master | run1 | VALUESr@ser | 10 | 47.25 [46.94, 48.21] | 9.82 [9.55, 10.12]
| 46.75 [46.13, 47.27] |
| master | run2 | IN@dpq | 10 | 593.96 [580.26, 596.58] | 84.81 [83.95, 85.73]
| 440.99 [429.92, 445.33] |
| master | run2 | IN@par | 10 | 363.48 [358.27, 368.01] | 128.68 [124.45,
130.65] | 222.08 [218.94, 224.70] |
| master | run2 | IN@par_idx | 10 | 506.18 [501.80, 509.82] | 85.33 [84.74,
86.26] | 419.10 [415.29, 422.60] |
| master | run2 | IN@ser | 10 | 191.52 [188.19, 194.61] | 133.33 [128.18,
135.81] | 44.55 [43.58, 45.14] |
| master | run2 | IN@ser_idx | 10 | 163.12 [162.01, 164.10] | 134.26 [131.27,
134.64] | 13.99 [13.90, 14.18] |
| proto | run1 | IN@dpq | 10 | 597.99 [596.53, 602.60] | 86.97 [85.67, 87.55] |
444.98 [442.38, 447.88] |
| proto | run1 | IN@dpq_proto | 10 | 69.77 [68.21, 71.01] | 19.41 [19.02,
19.81] | 43.49 [42.70, 44.04] |
| proto | run1 | IN@par | 10 | 368.99 [366.56, 372.90] | 129.41 [128.24,
130.27] | 224.44 [221.88, 232.89] |
| proto | run1 | IN@parF | 10 | 507.62 [501.42, 510.35] | 86.44 [85.50, 88.52]
| 424.60 [420.20, 427.98] |
| proto | run1 | IN@parF_w0 | 10 | 376.30 [371.25, 379.68] | 85.48 [84.53,
86.02] | 289.91 [285.57, 292.47] |
| proto | run1 | IN@par_idx | 10 | 510.12 [505.31, 514.32] | 87.39 [86.65,
88.40] | 423.80 [421.72, 425.64] |
| proto | run1 | IN@par_proto | 10 | 30.55 [29.96, 31.60] | 22.80 [22.13,
23.20] | 5.97 [5.73, 6.10] |
| proto | run1 | IN@ser | 10 | 190.78 [189.01, 192.56] | 133.34 [129.76,
134.07] | 44.19 [43.45, 44.47] |
| proto | run1 | IN@ser_idx | 10 | 161.90 [158.01, 163.97] | 133.17 [130.43,
133.94] | 13.69 [13.58, 13.78] |
| proto | run1 | IN@ser_proto | 10 | 29.81 [29.30, 30.61] | 22.02 [21.67,
22.85] | 5.86 [5.71, 5.98] |
| proto | run1Q | IN@ser | 10 | 186.83 [184.30, 189.50] | 129.50 [128.14,
131.29] | 42.88 [42.51, 43.21] |
| proto | run1Q | IN@ser_proto | 10 | 28.95 [28.16, 29.92] | 21.51 [21.23,
22.20] | 5.88 [5.80, 6.08] |
| proto | run1Q | SPLIT@ser | 10 | 304.12 [302.36, 305.73] | 295.73 [294.43,
298.09] | 6.90 [6.60, 7.28] |
| proto | run1Q | VALUESc@nl | 10 | 25.71 [25.18, 26.31] | 10.51 [10.21, 10.97]
| 13.59 [13.40, 13.75] |
| proto | run1Q | VALUESr@nl | 10 | 25.44 [25.14, 26.34] | 10.59 [10.21, 10.85]
| 13.47 [13.10, 13.80] |
| proto | run2 | IN@dpq_proto | 10 | 64.98 [64.17, 66.95] | 18.44 [18.13,
18.65] | 41.20 [40.62, 42.53] |
| proto | run2 | IN@parF | 10 | 492.21 [490.53, 494.30] | 83.56 [82.62, 84.22]
| 409.47 [406.06, 412.81] |
| proto | run2 | IN@parF_w0 | 10 | 362.28 [359.36, 364.13] | 83.24 [81.77,
85.08] | 280.32 [278.97, 281.84] |
| proto | run2 | IN@par_proto | 10 | 28.63 [27.86, 30.02] | 22.01 [21.55,
22.71] | 5.78 [5.70, 6.00] |
| proto | run2 | IN@ser_proto | 10 | 28.44 [27.84, 29.65] | 21.28 [21.15,
22.13] | 5.65 [5.58, 5.84] |
| r18 | run1 | IN@dpq | 10 | 598.76 [595.57, 599.94] | 82.19 [81.07, 83.73] |
455.13 [452.82, 456.76] |
| r18 | run1 | IN@par | 10 | 366.48 [365.63, 367.49] | 123.45 [122.33, 125.33]
| 228.53 [227.48, 231.00] |
| r18 | run1 | IN@par_idx | 10 | 517.55 [516.56, 518.91] | 82.26 [81.92, 83.05]
| 436.26 [434.98, 437.12] |
| r18 | run1 | IN@ser | 10 | 183.84 [181.53, 186.52] | 126.53 [124.31, 128.00]
| 43.57 [43.30, 43.96] |
| r18 | run1 | IN@ser_idx | 10 | 152.52 [151.61, 156.54] | 126.56 [125.11,
128.16] | 13.63 [13.54, 13.81] |
| r18 | run1 | SPLIT@par | 10 | 52.81 [51.98, 53.81] | 11.08 [11.00, 11.64] |
37.82 [37.34, 38.81] |
| r18 | run1 | SPLIT@ser | 10 | 24.37 [23.79, 25.15] | 10.76 [10.53, 11.21] |
6.87 [6.69, 7.26] |
| r18 | run1 | VALUESc@nl | 10 | 27.18 [26.70, 27.70] | 10.73 [10.51, 11.77] |
14.31 [14.14, 15.29] |
| r18 | run1 | VALUESc@par | 10 | 42.49 [41.48, 43.68] | 10.82 [10.75, 11.23] |
36.62 [35.76, 37.21] |
| r18 | run1 | VALUESc@ser | 10 | 47.57 [47.02, 49.23] | 10.18 [10.10, 10.48] |
49.34 [48.94, 49.96] |
| r18 | run1 | VALUESr@nl | 10 | 26.74 [26.45, 27.06] | 10.61 [10.38, 11.01] |
13.89 [13.75, 14.27] |
| r18 | run1 | VALUESr@par | 10 | 41.79 [41.04, 43.21] | 9.67 [9.60, 9.97] |
36.56 [35.67, 37.75] |
| r18 | run1 | VALUESr@ser | 10 | 48.28 [47.59, 50.09] | 10.21 [9.65, 11.03] |
48.97 [48.77, 50.95] |
# Comparisons: (to - from) / from, median [95% CI], same binary and run
| build | run | from -> to | what | wall % | planning % | execution % |
|---|---|---|---|---|---|---|
| f2d7570 | run1 | IN@ser_idx -> IN@par_idx | parallel penalty, same full-array
index plan | +231.0 [+226.3, +237.6] | -35.2 [-36.1, -34.5] | +3086.1 [+3043.6,
+3117.9] |
| f2d7570 | run1 | IN@par_idx -> VALUESc@nl | IN (follow-up shape) vs VALUES
nested loop | -94.9 [-95.0, -94.7] | -87.1 [-88.0, -86.5] | -96.8 [-96.9,
-96.7] |
| f2d7570 | run1 | VALUESr@nl -> VALUESc@nl | rows vs count(*) form of the
rewrite | +2.4 [-3.0, +6.4] | +2.4 [-6.6, +6.8] | +1.7 [-0.7, +5.0] |
| master | run1 | IN@ser_idx -> IN@par_idx | parallel penalty, same full-array
index plan | +211.9 [+208.0, +215.3] | -35.6 [-36.4, -34.4] | +2947.8 [+2917.9,
+2992.2] |
| master | run1 | IN@par_idx -> VALUESc@nl | IN (follow-up shape) vs VALUES
nested loop | -94.8 [-95.0, -94.7] | -87.5 [-87.7, -87.1] | -96.8 [-96.8,
-96.8] |
| master | run1 | VALUESr@nl -> VALUESc@nl | rows vs count(*) form of the
rewrite | +3.3 [+0.2, +7.4] | +5.5 [+0.1, +9.2] | +1.9 [-0.1, +3.6] |
| master | run2 | IN@ser_idx -> IN@par_idx | parallel penalty, same full-array
index plan | +210.3 [+207.0, +213.8] | -36.4 [-36.9, -34.9] | +2894.8 [+2850.0,
+2928.2] |
| proto | run1 | IN@ser_idx -> IN@par_idx | parallel penalty, same full-array
index plan | +215.1 [+209.9, +221.8] | -34.4 [-35.0, -32.9] | +2994.9 [+2971.7,
+3024.6] |
| proto | run1 | IN@ser -> IN@ser_proto | restriction, serial (seq-scan case) |
-84.4 [-84.7, -84.0] | -83.5 [-83.8, -82.7] | -86.7 [-87.1, -86.4] |
| proto | run1 | IN@ser_idx -> IN@ser_proto | restriction vs forced index,
serial | -81.6 [-82.0, -81.0] | -83.5 [-83.8, -82.7] | -57.2 [-58.3, -56.1] |
| proto | run1 | IN@par -> IN@par_proto | restriction, parallel allowed | -91.7
[-91.9, -91.4] | -82.4 [-82.9, -82.0] | -97.3 [-97.5, -97.3] |
| proto | run1 | IN@par_idx -> IN@par_proto | restriction vs the follow-up's
plan shape | -94.0 [-94.2, -93.8] | -73.9 [-74.8, -73.4] | -98.6 [-98.6, -98.6]
|
| proto | run1 | IN@dpq -> IN@dpq_proto | restriction, identical single-worker
Gather plan | -88.3 [-88.6, -88.1] | -77.7 [-78.1, -77.1] | -90.2 [-90.4,
-90.1] |
| proto | run1 | IN@parF_w0 -> IN@parF | workers launched vs Gather with 0
workers | +34.9 [+32.3, +36.8] | +1.1 [-0.3, +3.8] | +46.5 [+44.6, +49.0] |
| proto | run1Q | IN@ser -> IN@ser_proto | restriction, serial (seq-scan case)
| -84.5 [-85.0, -84.0] | -83.4 [-83.7, -82.8] | -86.3 [-86.5, -85.8] |
| proto | run1Q | VALUESc@nl -> IN@ser_proto | VALUES nested loop vs restricted
IN | +12.6 [+8.6, +17.5] | +104.7 [+96.1, +113.9] | -56.7 [-57.6, -55.0] |
| proto | run1Q | SPLIT@ser -> IN@ser_proto | UNION ALL upper bound vs in-place
restriction | -90.5 [-90.8, -90.1] | -92.7 [-92.8, -92.5] | -14.8 [-19.4,
-10.7] |
| proto | run1Q | VALUESr@nl -> VALUESc@nl | rows vs count(*) form of the
rewrite | +1.1 [-3.0, +3.3] | -0.8 [-4.7, +4.8] | +0.9 [-2.1, +3.3] |
| proto | run2 | IN@parF_w0 -> IN@parF | workers launched vs Gather with 0
workers | +35.9 [+35.0, +37.1] | +0.4 [-2.5, +2.3] | +46.1 [+44.6, +47.4] |
| r18 | run1 | IN@ser_idx -> IN@par_idx | parallel penalty, same full-array
index plan | +239.3 [+230.6, +241.9] | -35.0 [-35.8, -34.0] | +3101.2 [+3057.6,
+3122.4] |
| r18 | run1 | IN@par_idx -> VALUESc@nl | IN (follow-up shape) vs VALUES nested
loop | -94.7 [-94.8, -94.6] | -87.0 [-87.3, -85.7] | -96.7 [-96.8, -96.5] |
| r18 | run1 | VALUESr@nl -> VALUESc@nl | rows vs count(*) form of the rewrite
| +1.6 [-0.4, +3.7] | +1.2 [-2.8, +11.9] | +3.0 [-0.3, +10.0] |
diff --git a/src/backend/optimizer/util/inherit.c
b/src/backend/optimizer/util/inherit.c
index 6e1d2b14bc4..019d94b9150 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -36,9 +36,116 @@
#include "parser/parse_relation.h"
#include "partitioning/partdesc.h"
#include "partitioning/partprune.h"
+#include "nodes/nodeFuncs.h"
+#include "partitioning/partbounds.h"
+#include "utils/array.h"
+#include "utils/guc.h"
+#include "utils/lsyscache.h"
#include "utils/rel.h"
+/*
+ * MEASUREMENT PROTOTYPE ONLY -- not a proposal.
+ *
+ * When the placeholder GUC "proto.restrict_saop" is "on", reduce a live HASH
+ * partition's "partkey = ANY (const array)" qual to the array elements that
+ * hash to that partition, using the same hash computation that pruning uses
+ * to select the partition. Single-column hash partitioning only; any other
+ * shape is left untouched. It exists to bound what a per-child
+ * simplification could gain, measured end to end.
+ */
+static void
+proto_restrict_child_saop(PlannerInfo *root, RelOptInfo *parent,
+ RelOptInfo *child, int
partidx)
+{
+ const char *opt = GetConfigOption("proto.restrict_saop", true, false);
+ PartitionScheme ps = parent->part_scheme;
+ AppendRelInfo *appinfo;
+ Node *keyexpr;
+ ListCell *lc;
+
+ if (opt == NULL || strcmp(opt, "on") != 0)
+ return;
+ if (ps == NULL || ps->strategy != PARTITION_STRATEGY_HASH ||
+ ps->partnatts != 1 || parent->boundinfo == NULL ||
+ parent->partexprs == NULL || parent->partexprs[0] == NIL)
+ return;
+
+ appinfo = root->append_rel_array[child->relid];
+ keyexpr = adjust_appendrel_attrs(root,
+ (Node
*) linitial(parent->partexprs[0]),
+ 1,
&appinfo);
+
+ foreach(lc, child->baserestrictinfo)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+ ScalarArrayOpExpr *saop;
+ ScalarArrayOpExpr *newsaop;
+ Const *arr;
+ ArrayType *at;
+ Oid elemtype;
+ int16 typlen;
+ bool typbyval;
+ char typalign;
+ Datum *elems;
+ bool *nulls;
+ int nelems;
+ int nkeep = 0;
+ Datum *keep;
+
+ if (!IsA(rinfo->clause, ScalarArrayOpExpr))
+ continue;
+ saop = (ScalarArrayOpExpr *) rinfo->clause;
+ if (!saop->useOr || list_length(saop->args) != 2 ||
+ !IsA(lsecond(saop->args), Const) ||
+ !equal(strip_implicit_coercions(linitial(saop->args)),
keyexpr) ||
+ !op_in_opfamily(saop->opno, ps->partopfamily[0]))
+ continue;
+ arr = (Const *) lsecond(saop->args);
+ if (arr->constisnull)
+ continue;
+ at = DatumGetArrayTypeP(arr->constvalue);
+ elemtype = ARR_ELEMTYPE(at);
+ if (elemtype != ps->partopcintype[0])
+ continue;
+ get_typlenbyvalalign(elemtype, &typlen, &typbyval, &typalign);
+ deconstruct_array(at, elemtype, typlen, typbyval, typalign,
+ &elems, &nulls, &nelems);
+ keep = palloc_array(Datum, nelems);
+ for (int k = 0; k < nelems; k++)
+ {
+ bool isnull = false;
+ uint64 h;
+
+ if (nulls[k])
+ continue; /* NULL never satisfies
"=" */
+ h = compute_partition_hash_value(1, ps->partsupfunc,
+
ps->partcollation,
+
&elems[k], &isnull);
+ if (parent->boundinfo->indexes[h %
parent->boundinfo->nindexes] == partidx)
+ keep[nkeep++] = elems[k];
+ }
+ if (nkeep == 0 || nkeep == nelems)
+ continue;
+
+ newsaop = copyObject(saop);
+ newsaop->args = list_make2(linitial(saop->args),
+
makeConst(arr->consttype, arr->consttypmod,
+
arr->constcollid, -1,
+
PointerGetDatum(construct_array(keep, nkeep, elemtype,
+
typlen, typbyval,
+
typalign)),
+
false, false));
+ lfirst(lc) = make_restrictinfo(root, (Expr *) newsaop,
+
rinfo->is_pushed_down,
+
rinfo->has_clone,
+
rinfo->is_clone,
+
rinfo->pseudoconstant,
+
rinfo->security_level,
+
NULL, NULL, NULL);
+ }
+}
+
static void expand_partitioned_rtentry(PlannerInfo *root, RelOptInfo *relinfo,
RangeTblEntry *parentrte,
Index parentRTindex, Relation parentrel,
@@ -404,6 +511,7 @@ expand_partitioned_rtentry(PlannerInfo *root, RelOptInfo
*relinfo,
/* Create the otherrel RelOptInfo too. */
childrelinfo = build_simple_rel(root, childRTindex, relinfo);
relinfo->part_rels[i] = childrelinfo;
+ proto_restrict_child_saop(root, relinfo, childrelinfo, i);
relinfo->all_partrels = bms_add_members(relinfo->all_partrels,
childrelinfo->relids);
