Re: Caching a partition index's parent OID in the relcache?

Hi,

On Sat, 18 Jul 2026 at 01:57, Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com>
wrote:

>
> v2 attached. It caches the full ancestor list on the index's relcache
> entry using the single-OID-or-array union you suggested, and builds a
> fresh list from it on each call.
>
> For the invalidation we discussed, I had IndexSetParentIndex() invalidate
> the whole descendant-index subtree via find_all_inheritors(), so a
> re-parent can't leave a leaf's cached list stale. Does that feel like the
> right place and granularity to you? And is NoLock there OK, given
> ATTACH/DETACH already hold AccessExclusiveLock over the subtree, or is
> there a path where that doesn't hold?
>
> On performance I used pgbench. The test is INSERT ... ON CONFLICT (i) DO
> NOTHING into a 64-leaf range-partitioned table with all keys
> pre-populated, so
> every statement goes through the arbiter mapping; pgbench -M prepared -c 8,
> median of three 15s runs. The arbiter loop runs once per index on the
> leaf, so the gain grows with the number of indexes per partition:
>
> indexes/leaf master v2
> 4 34553 tps 40232 tps (+16%)
> 8 34128 tps 40246 tps (+18%)
> 15 29906 tps 39283 tps (+31%)
>
> Looking forward to reviews.
>

More benchmarking, mostly the multi-level case that was open with v2, plus
the
ATTACH/DETACH cost and a profile. This was on an Azure VM (AMD EPYC 7763,
8 vCPU, 32 GB), with master c90c9678e53 vs v2, gcc 13.3 -O2, no asserts.

For the TPS tests, I used INSERT ... ON CONFLICT (i) DO NOTHING with all
keys
pre-populated, pgbench -M prepared -c1, and pinned the backend and pgbench
to
separate physical cores. The figures are means +/- 95% CI from five 30s
runs.

Indexes on the leaf (single level):

indexes/leaf master patched
4 14296 +/- 128 17244 +/- 354
8 11781 +/- 113 16635 +/- 138
15 8935 +/- 218 15725 +/- 258

Depth instead (single chain to one leaf, 8 indexes):

levels master patched
1 12179 +/- 59 17008 +/- 145
2 10453 +/- 30 16723 +/- 336
3 9380 +/- 44 16660 +/- 68
4 8442 +/- 17 16359 +/- 204

v2 stays roughly flat while master slides down as the ancestor walk grows.

perf of one backend looping INSERT ... ON CONFLICT with a leaf index having
four ancestors (20s at 999 Hz; perf record --call-graph dwarf; perf report
--children):

master:
Children Self Symbol
91.99% 0.05% ExecInsert
90.08% 0.54% ExecFindPartition
84.67% 0.17% get_partition_ancestors
80.97% 0.34% get_partition_parent_worker
23.79% 0.84% systable_beginscan

v2:
Children Self Symbol
48.16% 0.31% ExecInsert
34.07% 1.63% ExecFindPartition
1.49% 0.42% get_partition_index_ancestors

Separately, I measured ATTACH/DETACH of an empty 16-index partition with K
leaf
subpartitions. One cycle is ATTACH followed by DETACH; these are mean
latencies
from five 10s runs:

leaves K master patched
1 1.92 ms 2.14 ms
64 3.85 ms 4.79 ms
256 9.94 ms 30.77 ms

The added cost grows with the descendant count. IndexSetParentIndex() walks
the
descendant index subtree once per re-parented index, but re-parenting is
rare,
so I think the absolute cost is an acceptable trade.

Regards,
Ayush