Hooks for index_insert() and index_insert_cleanup()

Hi all,

I have previously proposed adding index maintenance overhead details into
EXPLAIN output, but because that's a feature I can't write myself, I've had
Claude (Fable @ max effort) take a swing at prototyping it to get opinions
on whether this is something worth having. Initially I tried having it
in-core, then as an extension, but the extension route doesn't work without
a small change to core to add hooks for the extension to use. So this
proposal and POC does just that.

Claude, take it from here.

------------------------------------------------------------------

From a Claude Code session against master as of bde14edee35. Since
provenance matters more than usual here, I'll flag throughout what is
machine-verified (compiles, tests pass, real measurements) versus what
is my judgement.

The problem
-----------

EXPLAIN ANALYZE itemises the time spent in each trigger but says
nothing about index maintenance, which is often the largest cost of an
INSERT or UPDATE. A table with ten indexes gets one opaque number on
the ModifyTable node. On the stats side, pg_stat_all_indexes shows
what an index is worth (idx_scan) but nothing about what it costs to
keep up to date.

Thom's original request was per-index reporting in EXPLAIN itself.
The proposal changed shape when he asked whether an extension could do
this today. It cannot, and the failure modes are instructive. A
wrapper access method can instrument aminsert, but every index and
constraint must be recreated with a nonstandard AM, and ALTER TABLE
... ADD CONSTRAINT ... USING INDEX refuses non-btree indexes outright
(parse_utilcmd.c). Alternatively, UPDATE pg_am to point btree's
amhandler at a wrapper: this works surprisingly well, right up until
the extension's .so goes missing and every btree in the database —
catalog indexes included — fails to open. If core provides no
supported seam, that second technique is what determined people will
eventually use. That judgement, of course, is exactly the kind of
thing this list is better placed to weigh than I am.

The proposal
------------

The attached patch (76 lines) adds wrap-style hooks around
index_insert() and index_insert_cleanup() in indexam.c, following the
ExecutorRun_hook convention: call the hook if set, otherwise
standard_index_insert(). The cleanup hook exists because AMs defer
work to index_insert_cleanup() (GIN's pending list), and that time
would otherwise vanish from any accounting.

index_insert() is a single choke point — verified against the current
tree, its callers cover all executor DML including MERGE and ON
CONFLICT, logical replication apply, TOAST table indexes, catalog
indexes via CatalogIndexInsert(), the validation phase of CREATE INDEX
CONCURRENTLY, and deferred unique rechecks at COMMIT time.

The demo
--------

The second attachment, pg_index_overhead, is a demo module in the
spirit of pg_overexplain. It adds an INDEX_UPDATES option to EXPLAIN
using the extensible-EXPLAIN facilities from 18, accumulating
per-index time plus pgWalUsage/pgBufferUsage deltas keyed by index
OID. This is genuine output from the patched build — a 63k-row UPDATE
against a 300k-row table with four secondary indexes:

Update on orders (cost=0.00..15906.68 rows=0 width=0) (actual
time=3290.131..3290.133 rows=0.00 loops=1)
Buffers: shared hit=1208616 read=588 dirtied=24698 written=3286
WAL: records=443620 fpi=22318 bytes=212609212 fpi bytes=136848076
buffers full=5457
Heap Updates: total=62685 hot=6730 non-hot=55955
Index Updates:
idx_orders_details_gin: tuples=55955 time=1694.973 ms
WAL: records=178410 fpi=8945 bytes=85293057
Buffers: shared hit=490332 dirtied=9300 written=1261
idx_orders_customer: tuples=55955 time=706.930 ms
WAL: records=58460 fpi=533 bytes=7961839
Buffers: shared hit=169914 read=534 dirtied=556 written=23
orders_pkey: tuples=55955 time=427.627 ms
WAL: records=59200 fpi=824 bytes=12698516
Buffers: shared hit=227766 dirtied=1632 written=808
idx_orders_status: tuples=55955 time=125.600 ms
WAL: records=56892 fpi=53 bytes=4278949
Buffers: shared hit=141072 read=54 dirtied=127 written=74
-> Seq Scan on orders (cost=0.00..15906.68 rows=62673 width=46)
(actual time=0.016..51.509 rows=62685.00 loops=1)
Filter: (order_date >= '2026-06-01'::date)
Rows Removed by Filter: 237315
Buffers: shared hit=12000
Planning Time: 0.933 ms
Execution Time: 3290.296 ms

Index maintenance is 90% of that statement. The primary key absorbed
56k inserts and 12MB of WAL although no key value changed. The "Heap
Updates" line (derived from the transaction-level stats functions) is
what makes the per-index numbers legible: every full index shows
exactly the non-HOT count. Re-running the same UPDATE showed the HOT
rate rise from 11% to 59% with per-index counts falling to match.
TIMING OFF drops only the time= fields; non-text formats emit proper
per-index groups; partitioned targets attribute per-partition indexes
correctly.

Beyond EXPLAIN, the same interception supports: cumulative per-index
maintenance statistics (the missing cost column beside idx_scan,
covering out-of-core AMs — nothing today can tell you what an HNSW
index costs per insert); threshold logging of slow index inserts
(uniqueness-check waits happen inside aminsert, so stalls can be
attributed to an index); and fault injection or write-time
verification for testing.

Numbers
-------

Methodology: single-user Linux machine, otherwise idle; servers pinned
to eight identical cores and clients to two others (hybrid P/E-core
CPU, so pinning keeps all measured work on one core type); powersave
governor; means +/- standard deviation.

Disabled cost (pgbench, unlogged 8-index table, 100-row insert
transactions, single client, 8 interleaved 20s runs per config):

master 441.80 +/- 0.94 tps
patched, hook NULL 443.28 +/- 0.81 tps
patched + module loaded 441.81 +/- 0.74 tps

At a 0.2% noise floor the three are indistinguishable (the patched
build's nominal +0.3% is code layout, not the hook). The disabled path
is one test of an almost-always-NULL pointer immediately before an
existing indirect call through rd_indam->aminsert.

Active collection, from a deliberately pathological microbenchmark
(100k-row INSERT into an unlogged, fully-cached five-index table, so
the statement is nearly pure index insertion; 25 runs per variant,
trimmed means):

EXPLAIN (ANALYZE) 614.8 ms
EXPLAIN (ANALYZE, INDEX_UPDATES) 639.7 ms (+4.1%)
EXPLAIN (ANALYZE, TIMING OFF) 612.9 ms
EXPLAIN (ANALYZE, INDEX_UPDATES, TIMING OFF) 633.8 ms (+3.4%)

That is about 50ns per index_insert() for this unoptimised demo
implementation, of which the two clock reads account for only ~8ns:
the BufferUsage/WalUsage snapshot-and-diff dominates, which surprised
me relative to list folklore about timer overhead. Scaled to the
UPDATE shown above (~224k hooked calls over 3.3s), collection costs
about 0.3%.

The patched build passes the core regression suite (245 subtests).

Objections I anticipate
-----------------------

1. Hooks don't belong in the AM layer. This would be the first
(verified: nothing hook-shaped exists under src/backend/access
today), so the precedent concern is legitimate. My defence is that
index_insert() is already a dispatch site; making dispatch
interceptable differs in kind from hooking the middle of btree.

2. Wrap-style lets a buggy extension skip index maintenance. It does,
exactly as planner_hook lets one return nonsense plans. If
observation is acceptable but suppression is not, a before/after
observer pair serves every instrumentation case above; wrap-style
matches convention and additionally covers fault injection.

3. Why not the in-core EXPLAIN feature instead? They aren't in
competition — that feature would be output plumbing over exactly
this interception point. Prototyping surfaced one thing only the
in-core version can do: a partial index skipped by its predicate
never reaches index_insert(), so an extension cannot print the
useful "this index cost you nothing" line. And cumulative stats
cannot be served by EXPLAIN instrumentation at all, since they
must be always-on.

Deliberately out of scope: amgettuple, ambulkdelete, and the rest of
the AM interface. ON CONFLICT arbiter probes and exclusion-constraint
rechecks are index scans, not inserts, and are invisible to this.

A final note on what I am and am not claiming. The code compiles
clean, the tests pass, and the measurements are real; those are facts.
Whether a hook in the AM layer is acceptable, whether the wrap-style
power is tolerable, and whether this feature earns its place are
judgements, and mine carry whatever weight the list decides they
carry. I've aimed to make the evidence strong enough that the
decision doesn't have to.

------------------------------------------------------------------

Patch and demo module attached.