Hi, While poking at INSERT ... ON CONFLICT on partitioned tables, get_partition_ancestors() kept showing up in profiles, it scans pg_inherits once per hierarchy level, and ExecInitPartitionInfo() calls it once per leaf-partition index while sorting out arbiter indexes. There's already an XXX in that loop wondering about a syscache "or some other way to cache".
I tried the "other way": keep the immediate parent OID on the relcache
entry (a lazily-filled rd_partparent), then use the existing walker above
that. This avoids repeatedly scanning the leaf link and eliminates the
pg_inherits scan entirely for the common one-level hierarchy. Patch
attached (fairly small).
Invalidation uses the existing pg_class relcache events. ATTACH and
DETACH update the child index's relispartition flag, while REINDEX
CONCURRENTLY swaps that flag between the old and replacement index;
creation and drop replace the relation altogether. One detail is that an
open index takes RelationReloadIndexInfo() rather than the full relcache
rebuild path, so the patch clears rd_partparent there as well. No explicit
pg_inherits invalidation is needed.
Quick numbers (256-leaf table, one upsert touching every leaf per statement,
median per-statement latency over 10 warm-session runs):
indexes/table master patched speedup
1 3.17 ms 2.35 ms 1.3x
8 10.18 ms 5.51 ms 1.8x
16 22.40 ms 7.70 ms 2.9x
To be upfront, that's the warm case, a cold first statement still scans
pg_inherits once per index, so it's really repeated upserts that benefit.
Measured on an Azure Standard_D32as_v5 (AMD EPYC 7763, 32 vCPU) under WSL2,
-O2 build without asserts.
I checked REINDEX CONCURRENTLY (010_index_concurrently_upsert.pl), the
core regression and isolation suites, and added a regression that warms
the cache, moves a partition between two partitioned tables, and repeats
the exercise across a savepoint rollback.
One thing I'm unsure about is whether rd_partparent, as the first "upward"
partition field in RelationData (the rest point downward), is the right
place for this cache.
Does this seem worth pursuing, thoughts?
Thanks,
Ayush
v1-0001-Cache-partition-index-parent-in-relcache.patch
Description: Binary data
