> To make a full list safe we'd need a new invalidation path that, on any > index re-parent, invalidates the whole descendant-index subtree rather > than just the re-parented index, and it only helps 3+ level > hierarchies. Should I explore that?
I think we should do that. Reparenting an index is a very uncommon operation, so it doesn't matter if it emits several invalidation messages. On the other hand, if we limit the caching to just the immediate index, then we have good performance for the easy case of a single partitioning level, but everyone using more than that will have to pay a performance cost (on every INSERT ON CONFLICT) that's not easily visible.
Thanks a lot for the direction and help.
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:
