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.
From e83351119281c4f3e67e1b3c92bf5a4538c62f35 Mon Sep 17 00:00:00 2001
From: Thom Brown <[email protected]>
Date: Sun, 12 Jul 2026 07:35:13 +0100
Subject: [PATCH 1/2] Add hooks for index_insert() and index_insert_cleanup()
Provide wrap-style hooks following the ExecutorRun_hook convention:
index_insert() and index_insert_cleanup() call the hook if set, and
otherwise the new standard_index_insert() respectively
standard_index_insert_cleanup(). This gives extensions a supported
interception point for index maintenance, enabling per-index
instrumentation (timing, WAL and buffer attribution), cumulative
per-index write statistics, and fault-injection testing, none of which
are currently possible without recreating every index under a wrapper
access method or modifying pg_am in place.
index_insert() is the single choke point for all index tuple
insertion: executor DML (including MERGE and ON CONFLICT), logical
replication apply, TOAST and catalog indexes, the CREATE INDEX
CONCURRENTLY validation phase, and deferred unique rechecks. The
cleanup hook covers work deferred to index_insert_cleanup(), such as
GIN pending-list processing.
The cost when no hook is installed is a test of a global pointer
before the existing indirect call through rd_indam->aminsert;
benchmarks could not distinguish patched from unpatched builds.
Co-Authored-By: Claude Fable 5 <[email protected]>
---
src/backend/access/index/indexam.c | 52 ++++++++++++++++++++++++++++++
src/include/access/genam.h | 24 ++++++++++++++
2 files changed, 76 insertions(+)
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index 7967e939847..91c23106691 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -206,6 +206,14 @@ validate_relation_as_index(Relation r)
}
+/*
+ * Hooks for plugins to get control in index_insert() and
+ * index_insert_cleanup(). A hook is expected to call the corresponding
+ * standard_ function to perform the actual insertion or cleanup.
+ */
+index_insert_hook_type index_insert_hook = NULL;
+index_insert_cleanup_hook_type index_insert_cleanup_hook = NULL;
+
/* ----------------
* index_insert - insert an index tuple into a relation
* ----------------
@@ -219,6 +227,32 @@ index_insert(Relation indexRelation,
IndexUniqueCheck checkUnique,
bool indexUnchanged,
IndexInfo *indexInfo)
+{
+ if (index_insert_hook)
+ return (*index_insert_hook) (indexRelation, values, isnull,
+ heap_t_ctid, heapRelation,
+ checkUnique, indexUnchanged,
+ indexInfo);
+
+ return standard_index_insert(indexRelation, values, isnull,
+ heap_t_ctid, heapRelation,
+ checkUnique, indexUnchanged,
+ indexInfo);
+}
+
+/* ----------------
+ * standard_index_insert - standard implementation of index_insert
+ * ----------------
+ */
+bool
+standard_index_insert(Relation indexRelation,
+ Datum *values,
+ bool *isnull,
+ ItemPointer heap_t_ctid,
+ Relation heapRelation,
+ IndexUniqueCheck checkUnique,
+ bool indexUnchanged,
+ IndexInfo *indexInfo)
{
RELATION_CHECKS;
CHECK_REL_PROCEDURE(aminsert);
@@ -241,6 +275,24 @@ index_insert(Relation indexRelation,
void
index_insert_cleanup(Relation indexRelation,
IndexInfo *indexInfo)
+{
+ if (index_insert_cleanup_hook)
+ {
+ (*index_insert_cleanup_hook) (indexRelation, indexInfo);
+ return;
+ }
+
+ standard_index_insert_cleanup(indexRelation, indexInfo);
+}
+
+/* -------------------------
+ * standard_index_insert_cleanup - standard implementation of
+ * index_insert_cleanup
+ * -------------------------
+ */
+void
+standard_index_insert_cleanup(Relation indexRelation,
+ IndexInfo *indexInfo)
{
RELATION_CHECKS;
diff --git a/src/include/access/genam.h b/src/include/access/genam.h
index 68bfe405db3..81dd0775a89 100644
--- a/src/include/access/genam.h
+++ b/src/include/access/genam.h
@@ -144,6 +144,21 @@ extern Relation index_open(Oid relationId, LOCKMODE lockmode);
extern Relation try_index_open(Oid relationId, LOCKMODE lockmode);
extern void index_close(Relation relation, LOCKMODE lockmode);
+/* Hook for plugins to get control in index_insert() */
+typedef bool (*index_insert_hook_type) (Relation indexRelation,
+ Datum *values, bool *isnull,
+ ItemPointer heap_t_ctid,
+ Relation heapRelation,
+ IndexUniqueCheck checkUnique,
+ bool indexUnchanged,
+ IndexInfo *indexInfo);
+extern PGDLLIMPORT index_insert_hook_type index_insert_hook;
+
+/* Hook for plugins to get control in index_insert_cleanup() */
+typedef void (*index_insert_cleanup_hook_type) (Relation indexRelation,
+ IndexInfo *indexInfo);
+extern PGDLLIMPORT index_insert_cleanup_hook_type index_insert_cleanup_hook;
+
extern bool index_insert(Relation indexRelation,
Datum *values, bool *isnull,
ItemPointer heap_t_ctid,
@@ -151,8 +166,17 @@ extern bool index_insert(Relation indexRelation,
IndexUniqueCheck checkUnique,
bool indexUnchanged,
IndexInfo *indexInfo);
+extern bool standard_index_insert(Relation indexRelation,
+ Datum *values, bool *isnull,
+ ItemPointer heap_t_ctid,
+ Relation heapRelation,
+ IndexUniqueCheck checkUnique,
+ bool indexUnchanged,
+ IndexInfo *indexInfo);
extern void index_insert_cleanup(Relation indexRelation,
IndexInfo *indexInfo);
+extern void standard_index_insert_cleanup(Relation indexRelation,
+ IndexInfo *indexInfo);
extern IndexScanDesc index_beginscan(Relation heapRelation,
Relation indexRelation,
--
2.47.3
From 81a2e11824bf05a3f23f59e3b1ed56184b82f136 Mon Sep 17 00:00:00 2001
From: Thom Brown <[email protected]>
Date: Sun, 12 Jul 2026 07:35:47 +0100
Subject: [PATCH 2/2] Add pg_index_overhead demonstration module
A demonstration consumer of index_insert_hook and
index_insert_cleanup_hook. It adds an INDEX_UPDATES option to EXPLAIN
via the extensible-EXPLAIN facilities: used with ANALYZE on a
data-modifying statement, each ModifyTable node is annotated with
per-index insert counts, time, WAL usage and buffer usage, plus a
summary of heap-level modifications including HOT update counts.
Collection brackets each index_insert() call with instr_time readings
and pgWalUsage/pgBufferUsage snapshots, accumulated in a backend-local
hash table keyed by index OID.
This module is demonstration-grade, in the spirit of pg_overexplain,
and is not proposed for commit as-is.
Co-Authored-By: Claude Fable 5 <[email protected]>
---
contrib/Makefile | 1 +
contrib/meson.build | 1 +
contrib/pg_index_overhead/Makefile | 18 +
contrib/pg_index_overhead/README | 27 +
contrib/pg_index_overhead/meson.build | 17 +
contrib/pg_index_overhead/pg_index_overhead.c | 698 ++++++++++++++++++
6 files changed, 762 insertions(+)
create mode 100644 contrib/pg_index_overhead/Makefile
create mode 100644 contrib/pg_index_overhead/README
create mode 100644 contrib/pg_index_overhead/meson.build
create mode 100644 contrib/pg_index_overhead/pg_index_overhead.c
diff --git a/contrib/Makefile b/contrib/Makefile
index 7d91fe77db3..27152c319f2 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -32,6 +32,7 @@ SUBDIRS = \
passwordcheck \
pg_buffercache \
pg_freespacemap \
+ pg_index_overhead \
pg_logicalinspect \
pg_overexplain \
pg_plan_advice \
diff --git a/contrib/meson.build b/contrib/meson.build
index ebb7f83d8c5..9c334384e13 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -46,6 +46,7 @@ subdir('passwordcheck')
subdir('pg_buffercache')
subdir('pgcrypto')
subdir('pg_freespacemap')
+subdir('pg_index_overhead')
subdir('pg_logicalinspect')
subdir('pg_overexplain')
subdir('pg_plan_advice')
diff --git a/contrib/pg_index_overhead/Makefile b/contrib/pg_index_overhead/Makefile
new file mode 100644
index 00000000000..95dc5f35c68
--- /dev/null
+++ b/contrib/pg_index_overhead/Makefile
@@ -0,0 +1,18 @@
+# contrib/pg_index_overhead/Makefile
+
+MODULE_big = pg_index_overhead
+OBJS = \
+ $(WIN32RES) \
+ pg_index_overhead.o
+PGFILEDESC = "pg_index_overhead - per-index maintenance instrumentation for EXPLAIN"
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_index_overhead
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_index_overhead/README b/contrib/pg_index_overhead/README
new file mode 100644
index 00000000000..24a66b959f7
--- /dev/null
+++ b/contrib/pg_index_overhead/README
@@ -0,0 +1,27 @@
+pg_index_overhead
+=================
+
+Demonstration module for the proposed index_insert_hook and
+index_insert_cleanup_hook.
+
+Adds an INDEX_UPDATES option to EXPLAIN. Used together with ANALYZE
+on a data-modifying statement, each ModifyTable node is annotated with
+per-index insert counts and time, and (with the corresponding EXPLAIN
+options) per-index WAL and shared-buffer usage, plus heap-level
+modification counts including HOT updates.
+
+Usage:
+
+ LOAD 'pg_index_overhead'; -- or session_preload_libraries
+ EXPLAIN (ANALYZE, WAL, INDEX_UPDATES) UPDATE ...;
+
+Notes:
+
+ - INDEX_UPDATES requires ANALYZE. TIMING OFF suppresses only the
+ time= fields; counts, WAL and buffer usage are still collected.
+ - A partial index skipped by its predicate never reaches
+ index_insert(), so it does not appear in the output at all.
+ - Demonstration-grade attribution: entries not attributable to a
+ ModifyTable node's result relations (TOAST indexes, tuple-routed
+ partitions, indexes touched by trigger-fired statements) are
+ attached to the top plan node; catalog indexes are suppressed.
diff --git a/contrib/pg_index_overhead/meson.build b/contrib/pg_index_overhead/meson.build
new file mode 100644
index 00000000000..58d454efc2b
--- /dev/null
+++ b/contrib/pg_index_overhead/meson.build
@@ -0,0 +1,17 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+pg_index_overhead_sources = files(
+ 'pg_index_overhead.c',
+)
+
+if host_system == 'windows'
+ pg_index_overhead_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'pg_index_overhead',
+ '--FILEDESC', 'pg_index_overhead - per-index maintenance instrumentation for EXPLAIN',])
+endif
+
+pg_index_overhead = shared_module('pg_index_overhead',
+ pg_index_overhead_sources,
+ kwargs: contrib_mod_args,
+)
+contrib_targets += pg_index_overhead
diff --git a/contrib/pg_index_overhead/pg_index_overhead.c b/contrib/pg_index_overhead/pg_index_overhead.c
new file mode 100644
index 00000000000..2447948c534
--- /dev/null
+++ b/contrib/pg_index_overhead/pg_index_overhead.c
@@ -0,0 +1,698 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_index_overhead.c
+ * Per-index maintenance instrumentation for EXPLAIN, built on the
+ * proposed index_insert_hook / index_insert_cleanup_hook.
+ *
+ * This module adds an INDEX_UPDATES option to EXPLAIN. When used together
+ * with ANALYZE on a data-modifying statement, each ModifyTable node is
+ * annotated with, for every index that received tuples, the number of
+ * index tuples inserted, the time spent inserting them, and the WAL and
+ * shared-buffer traffic attributable to that index. A summary of
+ * heap-level modifications (including HOT update counts, which explain why
+ * per-index insert counts differ from the row count) is also shown.
+ *
+ * Collection works by interposing on index_insert(): while an instrumented
+ * statement is running, each call is timed and bracketed with snapshots of
+ * pgWalUsage and pgBufferUsage, accumulated into a backend-local hash table
+ * keyed by index OID. Work deferred to index_insert_cleanup() (such as GIN
+ * pending-list flushes) is attributed to the same index. Reporting uses
+ * the extensible-EXPLAIN facilities added in PostgreSQL 18.
+ *
+ * This is a demonstration module for the proposed hooks; it is not
+ * intended to be a polished tool.
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/genam.h"
+#include "catalog/catalog.h"
+#include "commands/defrem.h"
+#include "commands/explain.h"
+#include "commands/explain_format.h"
+#include "commands/explain_state.h"
+#include "executor/executor.h"
+#include "executor/instrument.h"
+#include "fmgr.h"
+#include "nodes/plannodes.h"
+#include "parser/parsetree.h"
+#include "pgstat.h"
+#include "portability/instr_time.h"
+#include "utils/hsearch.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+#include "utils/rel.h"
+
+PG_MODULE_MAGIC_EXT(
+ .name = "pg_index_overhead",
+ .version = "1.0"
+);
+
+/* Per-ExplainState options */
+typedef struct pioOptions
+{
+ bool index_updates;
+} pioOptions;
+
+/* Accumulated costs for one index */
+typedef struct pioIndexEntry
+{
+ Oid indexoid; /* hash key; must be first */
+ Oid heapoid; /* table the index belongs to */
+ int64 tuples; /* index_insert() calls */
+ instr_time itime; /* time inside insert + cleanup */
+ WalUsage wal;
+ BufferUsage buf;
+} pioIndexEntry;
+
+/* Before-execution snapshot of a target table's transaction stats */
+typedef struct pioHeapEntry
+{
+ Oid heapoid;
+ int64 ins0;
+ int64 upd0;
+ int64 hot0;
+ int64 del0;
+} pioHeapEntry;
+
+static int es_extension_id;
+static bool pio_collecting = false;
+static bool pio_timing = false;
+static HTAB *pio_hash = NULL;
+static List *pio_heap_list = NIL;
+static bool pio_heap_snapped = false;
+static MemoryContext pio_cxt = NULL;
+
+static ExplainOneQuery_hook_type prev_ExplainOneQuery_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static index_insert_hook_type prev_index_insert_hook;
+static index_insert_cleanup_hook_type prev_index_insert_cleanup_hook;
+static ExecutorStart_hook_type prev_ExecutorStart_hook;
+
+static void pio_index_updates_handler(ExplainState *es, DefElem *opt,
+ ParseState *pstate);
+static void pio_validate_options(ExplainState *es, List *options,
+ ParseState *pstate);
+static void pio_ExplainOneQuery(Query *query, int cursorOptions,
+ IntoClause *into, ExplainState *es,
+ const char *queryString, ParamListInfo params,
+ QueryEnvironment *queryEnv);
+static void pio_ExecutorStart(QueryDesc *queryDesc, int eflags);
+static bool pio_index_insert(Relation indexRelation,
+ Datum *values, bool *isnull,
+ ItemPointer heap_t_ctid,
+ Relation heapRelation,
+ IndexUniqueCheck checkUnique,
+ bool indexUnchanged,
+ IndexInfo *indexInfo);
+static void pio_index_insert_cleanup(Relation indexRelation,
+ IndexInfo *indexInfo);
+static void pio_per_node_hook(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name, ExplainState *es);
+
+void
+_PG_init(void)
+{
+ es_extension_id = GetExplainExtensionId("pg_index_overhead");
+
+ RegisterExtensionExplainOption("index_updates",
+ pio_index_updates_handler,
+ GUCCheckBooleanExplainOption);
+
+ prev_ExplainOneQuery_hook = ExplainOneQuery_hook;
+ ExplainOneQuery_hook = pio_ExplainOneQuery;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = pio_validate_options;
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = pio_per_node_hook;
+ prev_index_insert_hook = index_insert_hook;
+ index_insert_hook = pio_index_insert;
+ prev_index_insert_cleanup_hook = index_insert_cleanup_hook;
+ index_insert_cleanup_hook = pio_index_insert_cleanup;
+ prev_ExecutorStart_hook = ExecutorStart_hook;
+ ExecutorStart_hook = pio_ExecutorStart;
+}
+
+/*
+ * Fetch or create our options struct in an ExplainState.
+ */
+static pioOptions *
+pio_ensure_options(ExplainState *es)
+{
+ pioOptions *options;
+
+ options = GetExplainExtensionState(es, es_extension_id);
+
+ if (options == NULL)
+ {
+ options = palloc0_object(pioOptions);
+ SetExplainExtensionState(es, es_extension_id, options);
+ }
+
+ return options;
+}
+
+/*
+ * Parse handler for EXPLAIN (INDEX_UPDATES).
+ */
+static void
+pio_index_updates_handler(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ pioOptions *options = pio_ensure_options(es);
+
+ options->index_updates = defGetBoolean(opt);
+}
+
+/*
+ * INDEX_UPDATES requires ANALYZE, like WAL does.
+ */
+static void
+pio_validate_options(ExplainState *es, List *options, ParseState *pstate)
+{
+ pioOptions *opts;
+
+ if (prev_explain_validate_options_hook)
+ (*prev_explain_validate_options_hook) (es, options, pstate);
+
+ opts = GetExplainExtensionState(es, es_extension_id);
+ if (opts != NULL && opts->index_updates && !es->analyze)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN option %s requires ANALYZE",
+ "INDEX_UPDATES")));
+}
+
+/*
+ * Reset collection state ahead of an instrumented statement.
+ */
+static void
+pio_reset(void)
+{
+ HASHCTL ctl;
+
+ if (pio_cxt == NULL)
+ pio_cxt = AllocSetContextCreate(TopMemoryContext,
+ "pg_index_overhead",
+ ALLOCSET_DEFAULT_SIZES);
+
+ pio_hash = NULL;
+ pio_heap_list = NIL;
+ pio_heap_snapped = false;
+ MemoryContextReset(pio_cxt);
+
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(pioIndexEntry);
+ ctl.hcxt = pio_cxt;
+ pio_hash = hash_create("pg_index_overhead entries", 64, &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+}
+
+/*
+ * ExplainOneQuery hook: turn collection on around the execution of a
+ * statement being explained with (ANALYZE, INDEX_UPDATES).
+ */
+static void
+pio_ExplainOneQuery(Query *query, int cursorOptions, IntoClause *into,
+ ExplainState *es, const char *queryString,
+ ParamListInfo params, QueryEnvironment *queryEnv)
+{
+ pioOptions *options = GetExplainExtensionState(es, es_extension_id);
+ bool activate;
+
+ activate = (options != NULL && options->index_updates &&
+ es->analyze && !pio_collecting);
+
+ if (activate)
+ {
+ pio_reset();
+ pio_collecting = true;
+ pio_timing = es->timing;
+ }
+
+ PG_TRY();
+ {
+ if (prev_ExplainOneQuery_hook)
+ (*prev_ExplainOneQuery_hook) (query, cursorOptions, into, es,
+ queryString, params, queryEnv);
+ else
+ standard_ExplainOneQuery(query, cursorOptions, into, es,
+ queryString, params, queryEnv);
+ }
+ PG_FINALLY();
+ {
+ if (activate)
+ pio_collecting = false;
+ }
+ PG_END_TRY();
+}
+
+/*
+ * ExecutorStart hook: snapshot the transaction-level stats counters of the
+ * target relations, so that heap-level modification counts (notably HOT
+ * updates) can be computed at reporting time. Only the outermost
+ * statement of an instrumented EXPLAIN is snapshotted.
+ */
+static void
+pio_ExecutorStart(QueryDesc *queryDesc, int eflags)
+{
+ if (prev_ExecutorStart_hook)
+ (*prev_ExecutorStart_hook) (queryDesc, eflags);
+ else
+ standard_ExecutorStart(queryDesc, eflags);
+
+ if (pio_collecting && !pio_heap_snapped &&
+ !bms_is_empty(queryDesc->plannedstmt->resultRelationRelids))
+ {
+ MemoryContext oldcxt = MemoryContextSwitchTo(pio_cxt);
+ int rti = -1;
+
+ while ((rti = bms_next_member(queryDesc->plannedstmt->resultRelationRelids,
+ rti)) >= 0)
+ {
+ RangeTblEntry *rte = rt_fetch(rti, queryDesc->plannedstmt->rtable);
+ pioHeapEntry *h;
+ PgStat_TableStatus *ts;
+
+ h = palloc0_object(pioHeapEntry);
+ h->heapoid = rte->relid;
+ ts = find_tabstat_entry(rte->relid);
+ if (ts != NULL)
+ {
+ h->ins0 = ts->counts.tuples_inserted;
+ h->upd0 = ts->counts.tuples_updated;
+ h->hot0 = ts->counts.tuples_hot_updated;
+ h->del0 = ts->counts.tuples_deleted;
+ }
+ pio_heap_list = lappend(pio_heap_list, h);
+ }
+ MemoryContextSwitchTo(oldcxt);
+ pio_heap_snapped = true;
+ }
+}
+
+/*
+ * Find or create the accumulator for an index.
+ */
+static pioIndexEntry *
+pio_get_entry(Oid indexoid, Oid heapoid)
+{
+ pioIndexEntry *entry;
+ bool found;
+
+ entry = hash_search(pio_hash, &indexoid, HASH_ENTER, &found);
+ if (!found)
+ {
+ entry->heapoid = heapoid;
+ entry->tuples = 0;
+ INSTR_TIME_SET_ZERO(entry->itime);
+ memset(&entry->wal, 0, sizeof(WalUsage));
+ memset(&entry->buf, 0, sizeof(BufferUsage));
+ }
+ return entry;
+}
+
+/*
+ * index_insert hook: bracket the real insertion with time/WAL/buffer
+ * accounting when collection is active.
+ */
+static bool
+pio_index_insert(Relation indexRelation, Datum *values, bool *isnull,
+ ItemPointer heap_t_ctid, Relation heapRelation,
+ IndexUniqueCheck checkUnique, bool indexUnchanged,
+ IndexInfo *indexInfo)
+{
+ instr_time start;
+ instr_time end;
+ WalUsage walstart;
+ BufferUsage bufstart;
+ pioIndexEntry *entry;
+ bool ret;
+
+ if (!pio_collecting)
+ {
+ if (prev_index_insert_hook)
+ return (*prev_index_insert_hook) (indexRelation, values, isnull,
+ heap_t_ctid, heapRelation,
+ checkUnique, indexUnchanged,
+ indexInfo);
+ return standard_index_insert(indexRelation, values, isnull,
+ heap_t_ctid, heapRelation,
+ checkUnique, indexUnchanged,
+ indexInfo);
+ }
+
+ walstart = pgWalUsage;
+ bufstart = pgBufferUsage;
+ INSTR_TIME_SET_ZERO(start);
+ if (pio_timing)
+ INSTR_TIME_SET_CURRENT(start);
+
+ if (prev_index_insert_hook)
+ ret = (*prev_index_insert_hook) (indexRelation, values, isnull,
+ heap_t_ctid, heapRelation,
+ checkUnique, indexUnchanged,
+ indexInfo);
+ else
+ ret = standard_index_insert(indexRelation, values, isnull,
+ heap_t_ctid, heapRelation,
+ checkUnique, indexUnchanged,
+ indexInfo);
+
+ entry = pio_get_entry(RelationGetRelid(indexRelation),
+ RelationGetRelid(heapRelation));
+ entry->tuples++;
+ if (pio_timing)
+ {
+ INSTR_TIME_SET_CURRENT(end);
+ INSTR_TIME_ACCUM_DIFF(entry->itime, end, start);
+ }
+ WalUsageAccumDiff(&entry->wal, &pgWalUsage, &walstart);
+ BufferUsageAccumDiff(&entry->buf, &pgBufferUsage, &bufstart);
+
+ return ret;
+}
+
+/*
+ * index_insert_cleanup hook: attribute deferred insertion work (e.g. GIN
+ * pending-list flushes) to the index as well.
+ */
+static void
+pio_index_insert_cleanup(Relation indexRelation, IndexInfo *indexInfo)
+{
+ instr_time start;
+ instr_time end;
+ WalUsage walstart;
+ BufferUsage bufstart;
+ pioIndexEntry *entry;
+
+ if (!pio_collecting)
+ {
+ if (prev_index_insert_cleanup_hook)
+ (*prev_index_insert_cleanup_hook) (indexRelation, indexInfo);
+ else
+ standard_index_insert_cleanup(indexRelation, indexInfo);
+ return;
+ }
+
+ walstart = pgWalUsage;
+ bufstart = pgBufferUsage;
+ INSTR_TIME_SET_ZERO(start);
+ if (pio_timing)
+ INSTR_TIME_SET_CURRENT(start);
+
+ if (prev_index_insert_cleanup_hook)
+ (*prev_index_insert_cleanup_hook) (indexRelation, indexInfo);
+ else
+ standard_index_insert_cleanup(indexRelation, indexInfo);
+
+ entry = pio_get_entry(RelationGetRelid(indexRelation),
+ indexRelation->rd_index->indrelid);
+ if (pio_timing)
+ {
+ INSTR_TIME_SET_CURRENT(end);
+ INSTR_TIME_ACCUM_DIFF(entry->itime, end, start);
+ }
+ WalUsageAccumDiff(&entry->wal, &pgWalUsage, &walstart);
+ BufferUsageAccumDiff(&entry->buf, &pgBufferUsage, &bufstart);
+}
+
+/*
+ * Sort helper: most expensive index first.
+ */
+static int
+pio_entry_cmp(const void *a, const void *b)
+{
+ const pioIndexEntry *ea = *(const pioIndexEntry *const *) a;
+ const pioIndexEntry *eb = *(const pioIndexEntry *const *) b;
+ double ta = INSTR_TIME_GET_DOUBLE(ea->itime);
+ double tb = INSTR_TIME_GET_DOUBLE(eb->itime);
+
+ if (ta != tb)
+ return (ta < tb) ? 1 : -1;
+ if (ea->tuples != eb->tuples)
+ return (ea->tuples < eb->tuples) ? 1 : -1;
+ if (ea->indexoid != eb->indexoid)
+ return (ea->indexoid < eb->indexoid) ? -1 : 1;
+ return 0;
+}
+
+/*
+ * Emit the "Heap ..." summary lines for one ModifyTable node.
+ */
+static void
+pio_show_heap(List *relids, ExplainState *es)
+{
+ int64 ins = 0;
+ int64 upd = 0;
+ int64 hot = 0;
+ int64 del = 0;
+ bool have = false;
+ ListCell *lc;
+
+ foreach(lc, pio_heap_list)
+ {
+ pioHeapEntry *h = (pioHeapEntry *) lfirst(lc);
+ PgStat_TableStatus *ts;
+
+ if (!list_member_oid(relids, h->heapoid))
+ continue;
+ have = true;
+ ts = find_tabstat_entry(h->heapoid);
+ if (ts != NULL)
+ {
+ ins += ts->counts.tuples_inserted - h->ins0;
+ upd += ts->counts.tuples_updated - h->upd0;
+ hot += ts->counts.tuples_hot_updated - h->hot0;
+ del += ts->counts.tuples_deleted - h->del0;
+ }
+ }
+
+ if (!have)
+ return;
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ if (ins > 0)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Heap Inserts: %lld\n",
+ (long long) ins);
+ }
+ if (upd > 0)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str,
+ "Heap Updates: total=%lld hot=%lld non-hot=%lld\n",
+ (long long) upd, (long long) hot,
+ (long long) (upd - hot));
+ }
+ if (del > 0)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Heap Deletes: %lld\n",
+ (long long) del);
+ }
+ }
+ else
+ {
+ ExplainPropertyInteger("Heap Tuples Inserted", NULL, ins, es);
+ ExplainPropertyInteger("Heap Tuples Updated", NULL, upd, es);
+ ExplainPropertyInteger("Heap Tuples Hot Updated", NULL, hot, es);
+ ExplainPropertyInteger("Heap Tuples Deleted", NULL, del, es);
+ }
+}
+
+/*
+ * Emit the per-index lines for one ModifyTable node.
+ */
+static void
+pio_show_indexes(pioIndexEntry **entries, int nentries, ExplainState *es)
+{
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfoString(es->str, "Index Updates:\n");
+ es->indent++;
+ }
+ else
+ ExplainOpenGroup("Index Updates", "Index Updates", false, es);
+
+ for (int i = 0; i < nentries; i++)
+ {
+ pioIndexEntry *e = entries[i];
+ char *name = get_rel_name(e->indexoid);
+
+ if (name == NULL)
+ name = "(unknown)";
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ if (es->verbose)
+ {
+ char *nsp =
+ get_namespace_name(get_rel_namespace(e->indexoid));
+
+ appendStringInfo(es->str, "%s.%s", nsp ? nsp : "?", name);
+ }
+ else
+ appendStringInfoString(es->str, name);
+ appendStringInfo(es->str, ": tuples=%lld", (long long) e->tuples);
+ if (es->timing)
+ appendStringInfo(es->str, " time=%.3f ms",
+ INSTR_TIME_GET_MILLISEC(e->itime));
+ appendStringInfoChar(es->str, '\n');
+
+ if (es->wal &&
+ (e->wal.wal_records > 0 || e->wal.wal_fpi > 0 ||
+ e->wal.wal_bytes > 0))
+ {
+ es->indent++;
+ ExplainIndentText(es);
+ appendStringInfoString(es->str, "WAL:");
+ if (e->wal.wal_records > 0)
+ appendStringInfo(es->str, " records=%lld",
+ (long long) e->wal.wal_records);
+ if (e->wal.wal_fpi > 0)
+ appendStringInfo(es->str, " fpi=%lld",
+ (long long) e->wal.wal_fpi);
+ if (e->wal.wal_bytes > 0)
+ appendStringInfo(es->str, " bytes=%llu",
+ (unsigned long long) e->wal.wal_bytes);
+ appendStringInfoChar(es->str, '\n');
+ es->indent--;
+ }
+
+ if (es->buffers &&
+ (e->buf.shared_blks_hit > 0 || e->buf.shared_blks_read > 0 ||
+ e->buf.shared_blks_dirtied > 0 ||
+ e->buf.shared_blks_written > 0))
+ {
+ es->indent++;
+ ExplainIndentText(es);
+ appendStringInfoString(es->str, "Buffers: shared");
+ if (e->buf.shared_blks_hit > 0)
+ appendStringInfo(es->str, " hit=%lld",
+ (long long) e->buf.shared_blks_hit);
+ if (e->buf.shared_blks_read > 0)
+ appendStringInfo(es->str, " read=%lld",
+ (long long) e->buf.shared_blks_read);
+ if (e->buf.shared_blks_dirtied > 0)
+ appendStringInfo(es->str, " dirtied=%lld",
+ (long long) e->buf.shared_blks_dirtied);
+ if (e->buf.shared_blks_written > 0)
+ appendStringInfo(es->str, " written=%lld",
+ (long long) e->buf.shared_blks_written);
+ appendStringInfoChar(es->str, '\n');
+ es->indent--;
+ }
+ }
+ else
+ {
+ ExplainOpenGroup("Index Update", NULL, true, es);
+ ExplainPropertyText("Index Name", name, es);
+ ExplainPropertyInteger("Tuples", NULL, e->tuples, es);
+ if (es->timing)
+ ExplainPropertyFloat("Time", "ms",
+ INSTR_TIME_GET_MILLISEC(e->itime),
+ 3, es);
+ if (es->wal)
+ {
+ ExplainPropertyInteger("WAL Records", NULL,
+ e->wal.wal_records, es);
+ ExplainPropertyInteger("WAL FPI", NULL, e->wal.wal_fpi, es);
+ ExplainPropertyUInteger("WAL Bytes", NULL,
+ e->wal.wal_bytes, es);
+ }
+ if (es->buffers)
+ {
+ ExplainPropertyInteger("Shared Hit Blocks", NULL,
+ e->buf.shared_blks_hit, es);
+ ExplainPropertyInteger("Shared Read Blocks", NULL,
+ e->buf.shared_blks_read, es);
+ ExplainPropertyInteger("Shared Dirtied Blocks", NULL,
+ e->buf.shared_blks_dirtied, es);
+ ExplainPropertyInteger("Shared Written Blocks", NULL,
+ e->buf.shared_blks_written, es);
+ }
+ ExplainCloseGroup("Index Update", NULL, true, es);
+ }
+ }
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ es->indent--;
+ else
+ ExplainCloseGroup("Index Updates", "Index Updates", false, es);
+}
+
+/*
+ * Per-node hook: annotate ModifyTable nodes.
+ *
+ * Entries are attached to the node whose result relations own their index.
+ * Anything left over (TOAST indexes, indexes on partitions the tuple router
+ * created result relations for at runtime, indexes updated by trigger-fired
+ * statements) is attached to the top node of the plan, except for catalog
+ * indexes, which are suppressed.
+ */
+static void
+pio_per_node_hook(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ pioOptions *options;
+ ModifyTable *mt;
+ List *relids = NIL;
+ ListCell *lc;
+ pioIndexEntry **entries;
+ pioIndexEntry *entry;
+ int nentries = 0;
+ bool is_top;
+ HASH_SEQ_STATUS seq;
+
+ if (prev_explain_per_node_hook)
+ (*prev_explain_per_node_hook) (planstate, ancestors, relationship,
+ plan_name, es);
+
+ options = GetExplainExtensionState(es, es_extension_id);
+ if (options == NULL || !options->index_updates || !es->analyze)
+ return;
+ if (pio_hash == NULL)
+ return;
+ if (!IsA(planstate->plan, ModifyTable))
+ return;
+
+ mt = (ModifyTable *) planstate->plan;
+ is_top = (relationship == NULL);
+
+ foreach(lc, mt->resultRelations)
+ {
+ Index rti = lfirst_int(lc);
+ RangeTblEntry *rte = rt_fetch(rti, es->rtable);
+
+ relids = lappend_oid(relids, rte->relid);
+ }
+
+ pio_show_heap(relids, es);
+
+ entries = palloc(hash_get_num_entries(pio_hash) * sizeof(pioIndexEntry *));
+ hash_seq_init(&seq, pio_hash);
+ while ((entry = hash_seq_search(&seq)) != NULL)
+ {
+ if (list_member_oid(relids, entry->heapoid))
+ entries[nentries++] = entry;
+ else if (is_top && !IsCatalogRelationOid(entry->heapoid))
+ entries[nentries++] = entry;
+ }
+
+ if (nentries > 0)
+ {
+ qsort(entries, nentries, sizeof(pioIndexEntry *), pio_entry_cmp);
+ pio_show_indexes(entries, nentries, es);
+ }
+
+ pfree(entries);
+ list_free(relids);
+}
--
2.47.3
