v23-0003-Add-pg_collect_advice-contrib-module.patch

From 20331ab5502c48c05c4e26e5656c5e15ff91c9e5 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 26 Feb 2026 16:51:16 -0500
Subject: [PATCH v23 3/4] Add pg_collect_advice contrib module.

This module allows for bulk collection of queries and the associated
plan advice strings using either backend-local memory or dynamic
shared memory. In either case, memory usage can be limited by
restriction the maximum number of queries and advice strings stored.
Care should be taken with these values, and with the use of this
module in general, because it's easy to chew up an unreasonably large
amount of memory. Unlike pg_stat_statements, this module does not
provide for query normalization or even deduplication; it simply makes
a record for every query planned.

It can be useful to enable query ID computaton before using the
module, but it's not required. If not done, all queries will simply
show a query ID of zero.

Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com>
---
 contrib/Makefile                              |   1 +
 contrib/meson.build                           |   1 +
 contrib/pg_collect_advice/Makefile            |  26 +
 contrib/pg_collect_advice/collector.c         | 649 ++++++++++++++++++
 .../expected/local_collector.out              |  69 ++
 contrib/pg_collect_advice/interface.c         | 303 ++++++++
 contrib/pg_collect_advice/meson.build         |  41 ++
 .../pg_collect_advice--1.0.sql                |  43 ++
 .../pg_collect_advice.control                 |   5 +
 contrib/pg_collect_advice/pg_collect_advice.h |  39 ++
 .../pg_collect_advice/sql/local_collector.sql |  46 ++
 .../t/001_shared_collector.pl                 | 154 +++++
 doc/src/sgml/contrib.sgml                     |   1 +
 doc/src/sgml/filelist.sgml                    |   1 +
 doc/src/sgml/pgcollectadvice.sgml             | 244 +++++++
 src/tools/pgindent/typedefs.list              |   6 +
 16 files changed, 1629 insertions(+)
 create mode 100644 contrib/pg_collect_advice/Makefile
 create mode 100644 contrib/pg_collect_advice/collector.c
 create mode 100644 contrib/pg_collect_advice/expected/local_collector.out
 create mode 100644 contrib/pg_collect_advice/interface.c
 create mode 100644 contrib/pg_collect_advice/meson.build
 create mode 100644 contrib/pg_collect_advice/pg_collect_advice--1.0.sql
 create mode 100644 contrib/pg_collect_advice/pg_collect_advice.control
 create mode 100644 contrib/pg_collect_advice/pg_collect_advice.h
 create mode 100644 contrib/pg_collect_advice/sql/local_collector.sql
 create mode 100644 contrib/pg_collect_advice/t/001_shared_collector.pl
 create mode 100644 doc/src/sgml/pgcollectadvice.sgml

diff --git a/contrib/Makefile b/contrib/Makefile
index dd04c20acd2..22071034e51 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -31,6 +31,7 @@ SUBDIRS = \
 		pageinspect	\
 		passwordcheck	\
 		pg_buffercache	\
+		pg_collect_advice \
 		pg_freespacemap \
 		pg_logicalinspect \
 		pg_overexplain \
diff --git a/contrib/meson.build b/contrib/meson.build
index 5a752eac347..ff422d9b7fc 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -45,6 +45,7 @@ subdir('pageinspect')
 subdir('passwordcheck')
 subdir('pg_buffercache')
 subdir('pgcrypto')
+subdir('pg_collect_advice')
 subdir('pg_freespacemap')
 subdir('pg_logicalinspect')
 subdir('pg_overexplain')
diff --git a/contrib/pg_collect_advice/Makefile b/contrib/pg_collect_advice/Makefile
new file mode 100644
index 00000000000..594c1bf82b2
--- /dev/null
+++ b/contrib/pg_collect_advice/Makefile
@@ -0,0 +1,26 @@
+# contrib/pg_collect_advice/Makefile
+
+MODULE_big = pg_collect_advice
+OBJS = \
+	$(WIN32RES) \
+	collector.o \
+	interface.o
+
+EXTENSION = pg_collect_advice
+DATA = pg_collect_advice--1.0.sql
+PGFILEDESC = "pg_collect_advice - collect queries and their plan advice strings"
+
+REGRESS = local_collector
+EXTRA_INSTALL = contrib/pg_plan_advice
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_collect_advice
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_collect_advice/collector.c b/contrib/pg_collect_advice/collector.c
new file mode 100644
index 00000000000..d9fc3238fbd
--- /dev/null
+++ b/contrib/pg_collect_advice/collector.c
@@ -0,0 +1,649 @@
+/*-------------------------------------------------------------------------
+ *
+ * collector.c
+ *	  workhorse for saving plan advice in backend-local or shared memory
+ *
+ * Copyright (c) 2016-2026, PostgreSQL Global Development Group
+ *
+ *	  contrib/pg_collect_advice/collector.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "pg_collect_advice.h"
+
+#include "datatype/timestamp.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "nodes/pg_list.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/timestamp.h"
+#include "utils/tuplestore.h"
+
+PG_FUNCTION_INFO_V1(pg_clear_collected_local_advice);
+PG_FUNCTION_INFO_V1(pg_clear_collected_shared_advice);
+PG_FUNCTION_INFO_V1(pg_get_collected_local_advice);
+PG_FUNCTION_INFO_V1(pg_get_collected_shared_advice);
+
+#define ADVICE_CHUNK_SIZE		1024
+#define ADVICE_CHUNK_ARRAY_SIZE	64
+
+#define	PG_GET_ADVICE_COLUMNS	7
+
+/*
+ * Advice extracted from one query plan, together with the query string
+ * and various other identifying details.
+ */
+typedef struct pgca_collected_advice
+{
+	Oid			userid;			/* user OID */
+	Oid			dbid;			/* database OID */
+	uint64		queryid;		/* query identifier */
+	TimestampTz timestamp;		/* query timestamp */
+	int			advice_offset;	/* start of advice in textual data */
+	char		textual_data[FLEXIBLE_ARRAY_MEMBER];
+} pgca_collected_advice;
+
+/*
+ * A bunch of pointers to pgca_collected_advice objects, stored in
+ * backend-local memory.
+ */
+typedef struct pgca_local_advice_chunk
+{
+	pgca_collected_advice *entries[ADVICE_CHUNK_SIZE];
+} pgca_local_advice_chunk;
+
+/*
+ * Information about all of the pgca_collected_advice objects that we're
+ * storing in local memory.
+ *
+ * We assign consecutive IDs, starting from 0, to each pgca_collected_advice
+ * object that we store. The actual storage is an array of chunks, which
+ * helps keep memcpy() overhead low when we start discarding older data.
+ */
+typedef struct pgca_local_advice
+{
+	uint64		next_id;
+	uint64		oldest_id;
+	uint64		base_id;
+	int			chunk_array_allocated_size;
+	pgca_local_advice_chunk **chunks;
+} pgca_local_advice;
+
+/*
+ * Just like pgca_local_advice_chunk, but stored in a dynamic shared area,
+ * so we must use dsa_pointer instead of native pointers.
+ */
+typedef struct pgca_shared_advice_chunk
+{
+	dsa_pointer entries[ADVICE_CHUNK_SIZE];
+} pgca_shared_advice_chunk;
+
+/*
+ * Just like pgca_local_advice, but stored in a dynamic shared area, so
+ * we must use dsa_pointer instead of native pointers.
+ */
+typedef struct pgca_shared_advice
+{
+	uint64		next_id;
+	uint64		oldest_id;
+	uint64		base_id;
+	int			chunk_array_allocated_size;
+	dsa_pointer chunks;
+} pgca_shared_advice;
+
+/* Pointers to local and shared collectors */
+static pgca_local_advice *local_collector = NULL;
+static pgca_shared_advice *shared_collector = NULL;
+
+/* Static functions */
+static pgca_collected_advice *make_collected_advice(Oid userid,
+													Oid dbid,
+													uint64 queryId,
+													TimestampTz timestamp,
+													const char *query_string,
+													const char *advice_string,
+													dsa_area *area,
+													dsa_pointer *result);
+static void store_local_advice(pgca_collected_advice *ca);
+static void trim_local_advice(int limit);
+static void store_shared_advice(dsa_pointer ca_pointer);
+static void trim_shared_advice(dsa_area *area, int limit);
+
+/* Helper function to extract the query string from pgca_collected_advice */
+static inline const char *
+query_string(pgca_collected_advice *ca)
+{
+	return ca->textual_data;
+}
+
+/* Helper function to extract the advice string from pgca_collected_advice */
+static inline const char *
+advice_string(pgca_collected_advice *ca)
+{
+	return ca->textual_data + ca->advice_offset;
+}
+
+/*
+ * Store collected query advice into the local or shared advice collector,
+ * as appropriate.
+ */
+void
+pg_collect_advice_save(uint64 queryId, const char *query_string,
+					   const char *advice_string)
+{
+	Oid			userid = GetUserId();
+	Oid			dbid = MyDatabaseId;
+	TimestampTz now = GetCurrentTimestamp();
+
+	if (pg_collect_advice_local_collector &&
+		pg_collect_advice_local_collection_limit > 0)
+	{
+		pgca_collected_advice *ca;
+		MemoryContext oldcontext;
+
+		oldcontext = MemoryContextSwitchTo(pg_collect_advice_get_mcxt());
+		ca = make_collected_advice(userid, dbid, queryId, now,
+								   query_string, advice_string,
+								   NULL, NULL);
+		store_local_advice(ca);
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	if (pg_collect_advice_shared_collector &&
+		pg_collect_advice_shared_collection_limit > 0)
+	{
+		dsa_area   *area = pg_collect_advice_dsa_area();
+		dsa_pointer ca_pointer = InvalidDsaPointer; /* placate compiler */
+
+		make_collected_advice(userid, dbid, queryId, now,
+							  query_string, advice_string, area,
+							  &ca_pointer);
+		store_shared_advice(ca_pointer);
+	}
+}
+
+/*
+ * Allocate and fill a new pgca_collected_advice object.
+ *
+ * If area != NULL, it is used to allocate the new object, and the resulting
+ * dsa_pointer is returned via *result.
+ *
+ * If area == NULL, the new object is allocated in the current memory context,
+ * and result is not examined or modified.
+ */
+static pgca_collected_advice *
+make_collected_advice(Oid userid, Oid dbid, uint64 queryId,
+					  TimestampTz timestamp,
+					  const char *query_string,
+					  const char *advice_string,
+					  dsa_area *area, dsa_pointer *result)
+{
+	size_t		query_string_length = strlen(query_string) + 1;
+	size_t		advice_string_length = strlen(advice_string) + 1;
+	size_t		total_length;
+	pgca_collected_advice *ca;
+
+	total_length = offsetof(pgca_collected_advice, textual_data)
+		+ query_string_length + advice_string_length;
+
+	if (area == NULL)
+		ca = palloc(total_length);
+	else
+	{
+		*result = dsa_allocate(area, total_length);
+		ca = dsa_get_address(area, *result);
+	}
+
+	ca->userid = userid;
+	ca->dbid = dbid;
+	ca->queryid = queryId;
+	ca->timestamp = timestamp;
+	ca->advice_offset = query_string_length;
+
+	memcpy(ca->textual_data, query_string, query_string_length);
+	memcpy(&ca->textual_data[ca->advice_offset],
+		   advice_string, advice_string_length);
+
+	return ca;
+}
+
+/*
+ * Add a pgca_collected_advice object to our backend-local advice collection.
+ *
+ * Caller is responsible for switching to the appropriate memory context;
+ * the provided object should have been allocated in that same context.
+ */
+static void
+store_local_advice(pgca_collected_advice *ca)
+{
+	uint64		chunk_number;
+	uint64		chunk_offset;
+	pgca_local_advice *la = local_collector;
+
+	/* If the local advice collector isn't initialized yet, do that now. */
+	if (la == NULL)
+	{
+		la = palloc0(sizeof(pgca_local_advice));
+		la->chunk_array_allocated_size = ADVICE_CHUNK_ARRAY_SIZE;
+		la->chunks = palloc0_array(pgca_local_advice_chunk *,
+								   la->chunk_array_allocated_size);
+		local_collector = la;
+	}
+
+	/* Compute chunk and offset at which to store this advice. */
+	chunk_number = (la->next_id - la->base_id) / ADVICE_CHUNK_SIZE;
+	chunk_offset = (la->next_id - la->base_id) % ADVICE_CHUNK_SIZE;
+
+	/* Extend chunk array, if needed. */
+	if (chunk_number >= la->chunk_array_allocated_size)
+	{
+		int			new_size;
+
+		new_size = la->chunk_array_allocated_size + ADVICE_CHUNK_ARRAY_SIZE;
+		la->chunks = repalloc0_array(la->chunks,
+									 pgca_local_advice_chunk *,
+									 la->chunk_array_allocated_size,
+									 new_size);
+		la->chunk_array_allocated_size = new_size;
+	}
+
+	/* Allocate new chunk, if needed. */
+	if (la->chunks[chunk_number] == NULL)
+		la->chunks[chunk_number] = palloc0_object(pgca_local_advice_chunk);
+
+	/* Save pointer and bump next-id counter. */
+	Assert(la->chunks[chunk_number]->entries[chunk_offset] == NULL);
+	la->chunks[chunk_number]->entries[chunk_offset] = ca;
+	++la->next_id;
+
+	/* If we've exceeded the storage limit, discard old data. */
+	trim_local_advice(pg_collect_advice_local_collection_limit);
+}
+
+/*
+ * Add a pgca_collected_advice object to the shared advice collection.
+ *
+ * 'ca_pointer' should have been allocated from the pg_collect_advice DSA area
+ * and should point to an object of type pgca_collected_advice.
+ */
+static void
+store_shared_advice(dsa_pointer ca_pointer)
+{
+	uint64		chunk_number;
+	uint64		chunk_offset;
+	pgca_shared_state *state = pg_collect_advice_attach();
+	dsa_area   *area = pg_collect_advice_dsa_area();
+	pgca_shared_advice *sa = shared_collector;
+	dsa_pointer *chunk_array;
+	pgca_shared_advice_chunk *chunk;
+
+	/* Lock the shared state. */
+	LWLockAcquire(&state->lock, LW_EXCLUSIVE);
+
+	/*
+	 * If we're not attached to the shared advice collector yet, fix that now.
+	 * If we're the first ones to attach, we may need to create the object.
+	 */
+	if (sa == NULL)
+	{
+		if (state->shared_collector == InvalidDsaPointer)
+			state->shared_collector =
+				dsa_allocate0(area, sizeof(pgca_shared_advice));
+		shared_collector = sa = dsa_get_address(area, state->shared_collector);
+	}
+
+	/*
+	 * It's possible that some other backend may have succeeded in creating
+	 * the main collector object but failed to allocate an initial chunk
+	 * array, so we must be prepared to allocate the chunk array here whether
+	 * or not we created the collector object.
+	 */
+	if (shared_collector->chunk_array_allocated_size == 0)
+	{
+		sa->chunks =
+			dsa_allocate0(area,
+						  sizeof(dsa_pointer) * ADVICE_CHUNK_ARRAY_SIZE);
+		sa->chunk_array_allocated_size = ADVICE_CHUNK_ARRAY_SIZE;
+	}
+
+	/* Compute chunk and offset at which to store this advice. */
+	chunk_number = (sa->next_id - sa->base_id) / ADVICE_CHUNK_SIZE;
+	chunk_offset = (sa->next_id - sa->base_id) % ADVICE_CHUNK_SIZE;
+
+	/* Get the address of the chunk array and, if needed, extend it. */
+	if (chunk_number >= sa->chunk_array_allocated_size)
+	{
+		int			new_size;
+		dsa_pointer new_chunks;
+
+		/*
+		 * DSA can't enlarge an existing allocation, so we must make a new
+		 * allocation and copy data over.
+		 */
+		new_size = sa->chunk_array_allocated_size + ADVICE_CHUNK_ARRAY_SIZE;
+		new_chunks = dsa_allocate0(area, sizeof(dsa_pointer) * new_size);
+		chunk_array = dsa_get_address(area, new_chunks);
+		memcpy(chunk_array, dsa_get_address(area, sa->chunks),
+			   sizeof(dsa_pointer) * sa->chunk_array_allocated_size);
+		dsa_free(area, sa->chunks);
+		sa->chunks = new_chunks;
+		sa->chunk_array_allocated_size = new_size;
+	}
+	else
+		chunk_array = dsa_get_address(area, sa->chunks);
+
+	/* Get the address of the desired chunk, allocating it if needed. */
+	if (chunk_array[chunk_number] == InvalidDsaPointer)
+		chunk_array[chunk_number] =
+			dsa_allocate0(area, sizeof(pgca_shared_advice_chunk));
+	chunk = dsa_get_address(area, chunk_array[chunk_number]);
+
+	/* Save pointer and bump next-id counter. */
+	Assert(chunk->entries[chunk_offset] == InvalidDsaPointer);
+	chunk->entries[chunk_offset] = ca_pointer;
+	++sa->next_id;
+
+	/* If we've exceeded the storage limit, discard old data. */
+	trim_shared_advice(area, pg_collect_advice_shared_collection_limit);
+
+	/* Release lock on shared state. */
+	LWLockRelease(&state->lock);
+}
+
+/*
+ * Discard collected advice stored in backend-local memory in excess of the
+ * specified limit.
+ */
+static void
+trim_local_advice(int limit)
+{
+	pgca_local_advice *la = local_collector;
+	uint64		current_count;
+	uint64		trim_count;
+	uint64		total_chunk_count;
+	uint64		trim_chunk_count;
+	uint64		remaining_chunk_count;
+
+	/* If we haven't yet reached the limit, there's nothing to do. */
+	current_count = la->next_id - la->oldest_id;
+	if (current_count <= limit)
+		return;
+
+	/* Free enough entries to get us back down to the limit. */
+	trim_count = current_count - limit;
+	while (trim_count > 0)
+	{
+		uint64		chunk_number;
+		uint64		chunk_offset;
+
+		chunk_number = (la->oldest_id - la->base_id) / ADVICE_CHUNK_SIZE;
+		chunk_offset = (la->oldest_id - la->base_id) % ADVICE_CHUNK_SIZE;
+
+		Assert(la->chunks[chunk_number]->entries[chunk_offset] != NULL);
+		pfree(la->chunks[chunk_number]->entries[chunk_offset]);
+		la->chunks[chunk_number]->entries[chunk_offset] = NULL;
+		++la->oldest_id;
+		--trim_count;
+	}
+
+	/* Free any chunks that are now entirely unused. */
+	trim_chunk_count = (la->oldest_id - la->base_id) / ADVICE_CHUNK_SIZE;
+	for (uint64 n = 0; n < trim_chunk_count; ++n)
+		pfree(la->chunks[n]);
+
+	/* Slide remaining chunk pointers back toward the base of the array. */
+	total_chunk_count = (la->next_id - la->base_id +
+						 ADVICE_CHUNK_SIZE - 1) / ADVICE_CHUNK_SIZE;
+	remaining_chunk_count = total_chunk_count - trim_chunk_count;
+	if (remaining_chunk_count > 0)
+		memmove(&la->chunks[0], &la->chunks[trim_chunk_count],
+				sizeof(pgca_local_advice_chunk *) * remaining_chunk_count);
+
+	/* Don't leave stale pointers around. */
+	memset(&la->chunks[remaining_chunk_count], 0,
+		   sizeof(pgca_local_advice_chunk *)
+		   * (total_chunk_count - remaining_chunk_count));
+
+	/* Adjust base ID value accordingly. */
+	la->base_id += trim_chunk_count * ADVICE_CHUNK_SIZE;
+}
+
+/*
+ * Discard collected advice stored in shared memory in excess of the
+ * specified limit.
+ */
+static void
+trim_shared_advice(dsa_area *area, int limit)
+{
+	pgca_shared_advice *sa = shared_collector;
+	uint64		current_count;
+	uint64		trim_count;
+	uint64		total_chunk_count;
+	uint64		trim_chunk_count;
+	uint64		remaining_chunk_count;
+	dsa_pointer *chunk_array;
+
+	/* If we haven't yet reached the limit, there's nothing to do. */
+	current_count = sa->next_id - sa->oldest_id;
+	if (current_count <= limit)
+		return;
+
+	/* Get a pointer to the chunk array. */
+	chunk_array = dsa_get_address(area, sa->chunks);
+
+	/* Free enough entries to get us back down to the limit. */
+	trim_count = current_count - limit;
+	while (trim_count > 0)
+	{
+		uint64		chunk_number;
+		uint64		chunk_offset;
+		pgca_shared_advice_chunk *chunk;
+
+		chunk_number = (sa->oldest_id - sa->base_id) / ADVICE_CHUNK_SIZE;
+		chunk_offset = (sa->oldest_id - sa->base_id) % ADVICE_CHUNK_SIZE;
+
+		chunk = dsa_get_address(area, chunk_array[chunk_number]);
+		Assert(chunk->entries[chunk_offset] != InvalidDsaPointer);
+		dsa_free(area, chunk->entries[chunk_offset]);
+		chunk->entries[chunk_offset] = InvalidDsaPointer;
+		++sa->oldest_id;
+		--trim_count;
+	}
+
+	/* Free any chunks that are now entirely unused. */
+	trim_chunk_count = (sa->oldest_id - sa->base_id) / ADVICE_CHUNK_SIZE;
+	for (uint64 n = 0; n < trim_chunk_count; ++n)
+		dsa_free(area, chunk_array[n]);
+
+	/* Slide remaining chunk pointers back toward the base of the array. */
+	total_chunk_count = (sa->next_id - sa->base_id +
+						 ADVICE_CHUNK_SIZE - 1) / ADVICE_CHUNK_SIZE;
+	remaining_chunk_count = total_chunk_count - trim_chunk_count;
+	if (remaining_chunk_count > 0)
+		memmove(&chunk_array[0], &chunk_array[trim_chunk_count],
+				sizeof(dsa_pointer) * remaining_chunk_count);
+
+	/* Don't leave stale pointers around. */
+	memset(&chunk_array[remaining_chunk_count], 0,
+		   sizeof(dsa_pointer) * (total_chunk_count - remaining_chunk_count));
+
+	/* Adjust base ID value accordingly. */
+	sa->base_id += trim_chunk_count * ADVICE_CHUNK_SIZE;
+}
+
+/*
+ * SQL-callable function to discard advice collected in backend-local memory
+ */
+Datum
+pg_clear_collected_local_advice(PG_FUNCTION_ARGS)
+{
+	if (local_collector != NULL)
+		trim_local_advice(0);
+
+	PG_RETURN_VOID();
+}
+
+/*
+ * SQL-callable function to discard advice collected in shared memory
+ */
+Datum
+pg_clear_collected_shared_advice(PG_FUNCTION_ARGS)
+{
+	pgca_shared_state *state = pg_collect_advice_attach();
+	dsa_area   *area = pg_collect_advice_dsa_area();
+
+	LWLockAcquire(&state->lock, LW_EXCLUSIVE);
+
+	/*
+	 * If we're not attached to the shared advice collector yet, fix that now;
+	 * but if the collector doesn't even exist, we can return without doing
+	 * anything else.
+	 */
+	if (shared_collector == NULL)
+	{
+		if (state->shared_collector == InvalidDsaPointer)
+		{
+			LWLockRelease(&state->lock);
+			return (Datum) 0;
+		}
+		shared_collector = dsa_get_address(area, state->shared_collector);
+	}
+
+	/* Do the real work */
+	trim_shared_advice(area, 0);
+
+	LWLockRelease(&state->lock);
+
+	PG_RETURN_VOID();
+}
+
+/*
+ * SQL-callable SRF to return advice collected in backend-local memory
+ */
+Datum
+pg_get_collected_local_advice(PG_FUNCTION_ARGS)
+{
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	pgca_local_advice *la = local_collector;
+	Oid			userid = GetUserId();
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	if (la == NULL)
+		return (Datum) 0;
+
+	/* Loop over all entries. */
+	for (uint64 id = la->oldest_id; id < la->next_id; ++id)
+	{
+		uint64		chunk_number;
+		uint64		chunk_offset;
+		pgca_collected_advice *ca;
+		Datum		values[PG_GET_ADVICE_COLUMNS];
+		bool		nulls[PG_GET_ADVICE_COLUMNS] = {0};
+
+		chunk_number = (id - la->base_id) / ADVICE_CHUNK_SIZE;
+		chunk_offset = (id - la->base_id) % ADVICE_CHUNK_SIZE;
+
+		ca = la->chunks[chunk_number]->entries[chunk_offset];
+
+		if (!member_can_set_role(userid, ca->userid))
+			continue;
+
+		values[0] = UInt64GetDatum(id);
+		values[1] = ObjectIdGetDatum(ca->userid);
+		values[2] = ObjectIdGetDatum(ca->dbid);
+		values[3] = UInt64GetDatum(ca->queryid);
+		values[4] = TimestampTzGetDatum(ca->timestamp);
+		values[5] = CStringGetTextDatum(query_string(ca));
+		values[6] = CStringGetTextDatum(advice_string(ca));
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+							 values, nulls);
+	}
+
+	return (Datum) 0;
+}
+
+/*
+ * SQL-callable SRF to return advice collected in shared memory
+ */
+Datum
+pg_get_collected_shared_advice(PG_FUNCTION_ARGS)
+{
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	pgca_shared_state *state = pg_collect_advice_attach();
+	dsa_area   *area = pg_collect_advice_dsa_area();
+	dsa_pointer *chunk_array;
+	pgca_shared_advice *sa = shared_collector;
+	Oid			userid = GetUserId();
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	/* Lock the shared state. */
+	LWLockAcquire(&state->lock, LW_SHARED);
+
+	/*
+	 * If we're not attached to the shared advice collector yet, fix that now;
+	 * but if the collector doesn't even exist, we can return without doing
+	 * anything else.
+	 */
+	if (sa == NULL)
+	{
+		if (state->shared_collector == InvalidDsaPointer)
+		{
+			LWLockRelease(&state->lock);
+			return (Datum) 0;
+		}
+		shared_collector = sa = dsa_get_address(area, state->shared_collector);
+	}
+
+	/* If there's no chunk array yet, there's nothing to do. */
+	if (sa->chunks == InvalidDsaPointer)
+	{
+		LWLockRelease(&state->lock);
+		return (Datum) 0;
+	}
+
+	/* Get a pointer to the chunk array. */
+	chunk_array = dsa_get_address(area, sa->chunks);
+
+	/* Loop over all entries. */
+	for (uint64 id = sa->oldest_id; id < sa->next_id; ++id)
+	{
+		uint64		chunk_number;
+		uint64		chunk_offset;
+		pgca_shared_advice_chunk *chunk;
+		pgca_collected_advice *ca;
+		Datum		values[PG_GET_ADVICE_COLUMNS];
+		bool		nulls[PG_GET_ADVICE_COLUMNS] = {0};
+
+		chunk_number = (id - sa->base_id) / ADVICE_CHUNK_SIZE;
+		chunk_offset = (id - sa->base_id) % ADVICE_CHUNK_SIZE;
+
+		chunk = dsa_get_address(area, chunk_array[chunk_number]);
+		ca = dsa_get_address(area, chunk->entries[chunk_offset]);
+
+		if (!member_can_set_role(userid, ca->userid))
+			continue;
+
+		values[0] = UInt64GetDatum(id);
+		values[1] = ObjectIdGetDatum(ca->userid);
+		values[2] = ObjectIdGetDatum(ca->dbid);
+		values[3] = UInt64GetDatum(ca->queryid);
+		values[4] = TimestampTzGetDatum(ca->timestamp);
+		values[5] = CStringGetTextDatum(query_string(ca));
+		values[6] = CStringGetTextDatum(advice_string(ca));
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+							 values, nulls);
+	}
+
+	/* Release lock on shared state. */
+	LWLockRelease(&state->lock);
+
+	return (Datum) 0;
+}
diff --git a/contrib/pg_collect_advice/expected/local_collector.out b/contrib/pg_collect_advice/expected/local_collector.out
new file mode 100644
index 00000000000..f57b96ee835
--- /dev/null
+++ b/contrib/pg_collect_advice/expected/local_collector.out
@@ -0,0 +1,69 @@
+CREATE EXTENSION pg_collect_advice;
+SET debug_parallel_query = off;
+-- Try clearing advice before we've collected any.
+SELECT pg_clear_collected_local_advice();
+ pg_clear_collected_local_advice 
+---------------------------------
+ 
+(1 row)
+
+-- Set a small advice collection limit so that we'll exceed it.
+SET pg_collect_advice.local_collection_limit = 2;
+-- Enable the collector.
+SET pg_collect_advice.local_collector = on;
+-- Set up a dummy table.
+CREATE TABLE dummy_table (a int primary key, b text)
+	WITH (autovacuum_enabled = false, parallel_workers = 0);
+-- Test queries.
+SELECT * FROM dummy_table a, dummy_table b;
+ a | b | a | b 
+---+---+---+---
+(0 rows)
+
+SELECT * FROM dummy_table;
+ a | b 
+---+---
+(0 rows)
+
+-- Should return the advice from the second test query.
+SET pg_collect_advice.local_collector = off;
+SELECT advice FROM pg_get_collected_local_advice() ORDER BY id DESC LIMIT 1;
+         advice         
+------------------------
+ SEQ_SCAN(dummy_table) +
+ NO_GATHER(dummy_table)
+(1 row)
+
+-- Now try clearing advice again.
+SELECT pg_clear_collected_local_advice();
+ pg_clear_collected_local_advice 
+---------------------------------
+ 
+(1 row)
+
+-- Raise the collection limit so that the collector uses multiple chunks.
+SET pg_collect_advice.local_collection_limit = 2000;
+SET pg_collect_advice.local_collector = on;
+-- Push a bunch of queries through the collector.
+DO $$
+BEGIN
+	FOR x IN 1..2000 LOOP
+		EXECUTE 'SELECT * FROM dummy_table';
+	END LOOP;
+END
+$$;
+-- Check that the collector worked.
+SELECT COUNT(*) FROM pg_get_collected_local_advice();
+ count 
+-------
+  2000
+(1 row)
+
+-- And clear one more time, to verify that this doesn't cause a problem
+-- even with a larger number of entries.
+SELECT pg_clear_collected_local_advice();
+ pg_clear_collected_local_advice 
+---------------------------------
+ 
+(1 row)
+
diff --git a/contrib/pg_collect_advice/interface.c b/contrib/pg_collect_advice/interface.c
new file mode 100644
index 00000000000..feb11974152
--- /dev/null
+++ b/contrib/pg_collect_advice/interface.c
@@ -0,0 +1,303 @@
+/*-------------------------------------------------------------------------
+ *
+ * interface.c
+ *	  interface routines for the plan advice collector
+ *
+ * Copyright (c) 2016-2026, PostgreSQL Global Development Group
+ *
+ *	  contrib/pg_collect_advice/interface.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "pg_collect_advice.h"
+
+#include "funcapi.h"
+#include "optimizer/planner.h"
+#include "storage/dsm_registry.h"
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+/* Shared memory pointers */
+static pgca_shared_state *pgca_state = NULL;
+static dsa_area *pgca_dsa_area = NULL;
+
+/* GUC variables */
+bool		pg_collect_advice_local_collector = false;
+int			pg_collect_advice_local_collection_limit = 0;
+bool		pg_collect_advice_shared_collector = false;
+int			pg_collect_advice_shared_collection_limit = 0;
+
+/* Shadow variables for GUC assign hooks */
+static bool pg_collect_advice_local_collector_as_assigned = false;
+static bool pg_collect_advice_shared_collector_as_assigned = false;
+
+/* Other file-level globals */
+static void (*request_advice_generation_fn) (bool activate) = NULL;
+static planner_shutdown_hook_type prev_planner_shutdown = NULL;
+static MemoryContext pgca_memory_context = NULL;
+
+/* Function prototypes */
+static void pgca_init_shared_state(void *ptr, void *arg);
+static void pgca_planner_shutdown(PlannerGlobal *glob, Query *parse,
+								  const char *query_string,
+								  PlannedStmt *pstmt);
+static void pg_collect_advice_local_collector_assign_hook(bool newval,
+														  void *extra);
+static void pg_collect_advice_shared_collector_assign_hook(bool newval,
+														   void *extra);
+static DefElem *find_defelem_by_defname(List *deflist, char *defname);
+
+/*
+ * Initialize this module.
+ */
+void
+_PG_init(void)
+{
+	/*
+	 * Get a pointer so we can call pg_plan_advice_request_advice_generation.
+	 *
+	 * We need to do this before defining custom GUCs; otherwise, our assign
+	 * hook will try to use this function pointer before it's initialized.
+	 *
+	 * We also need to do this before installing our own hooks, so that if
+	 * pg_plan_advice is not yet loaded, it will install its hooks before we
+	 * install ours. (See comments in pgca_planner_shutdown.)
+	 */
+	request_advice_generation_fn =
+		load_external_function("pg_plan_advice",
+							   "pg_plan_advice_request_advice_generation",
+							   true, NULL);
+
+	/* Define our GUCs. */
+	DefineCustomBoolVariable("pg_collect_advice.local_collector",
+							 "Enable the local advice collector.",
+							 NULL,
+							 &pg_collect_advice_local_collector,
+							 false,
+							 PGC_USERSET,
+							 0,
+							 NULL,
+							 pg_collect_advice_local_collector_assign_hook,
+							 NULL);
+
+	DefineCustomIntVariable("pg_collect_advice.local_collection_limit",
+							"# of advice entries to retain in per-backend memory",
+							NULL,
+							&pg_collect_advice_local_collection_limit,
+							0,
+							0, INT_MAX,
+							PGC_USERSET,
+							0,
+							NULL,
+							NULL,
+							NULL);
+
+	DefineCustomBoolVariable("pg_collect_advice.shared_collector",
+							 "Enable the shared advice collector.",
+							 NULL,
+							 &pg_collect_advice_shared_collector,
+							 false,
+							 PGC_SUSET,
+							 0,
+							 NULL,
+							 pg_collect_advice_shared_collector_assign_hook,
+							 NULL);
+
+	DefineCustomIntVariable("pg_collect_advice.shared_collection_limit",
+							"# of advice entries to retain in shared memory",
+							NULL,
+							&pg_collect_advice_shared_collection_limit,
+							0,
+							0, INT_MAX,
+							PGC_SUSET,
+							0,
+							NULL,
+							NULL,
+							NULL);
+
+	MarkGUCPrefixReserved("pg_collect_advice");
+
+	/* Install hooks */
+	prev_planner_shutdown = planner_shutdown_hook;
+	planner_shutdown_hook = pgca_planner_shutdown;
+}
+
+/*
+ * Initialize shared state when first created.
+ */
+static void
+pgca_init_shared_state(void *ptr, void *arg)
+{
+	pgca_shared_state *state = (pgca_shared_state *) ptr;
+
+	LWLockInitialize(&state->lock,
+					 LWLockNewTrancheId("pg_collect_advice_lock"));
+	state->dsa_tranche = LWLockNewTrancheId("pg_collect_advice_dsa");
+	state->area = DSA_HANDLE_INVALID;
+	state->shared_collector = InvalidDsaPointer;
+}
+
+/*
+ * Return a pointer to a memory context where long-lived data managed by this
+ * module can be stored.
+ */
+MemoryContext
+pg_collect_advice_get_mcxt(void)
+{
+	if (pgca_memory_context == NULL)
+		pgca_memory_context = AllocSetContextCreate(TopMemoryContext,
+													"pg_collect_advice",
+													ALLOCSET_DEFAULT_SIZES);
+
+	return pgca_memory_context;
+}
+
+/*
+ * Get a pointer to our shared state.
+ *
+ * If no shared state exists, create and initialize it. If it does exist but
+ * this backend has not yet accessed it, attach to it. Otherwise, just return
+ * our cached pointer.
+ */
+pgca_shared_state *
+pg_collect_advice_attach(void)
+{
+	if (pgca_state == NULL)
+	{
+		bool		found;
+
+		pgca_state =
+			GetNamedDSMSegment("pg_collect_advice", sizeof(pgca_shared_state),
+							   pgca_init_shared_state, &found, NULL);
+	}
+
+	return pgca_state;
+}
+
+/*
+ * Return a pointer to pg_collect_advice's DSA area, creating it if needed.
+ */
+dsa_area *
+pg_collect_advice_dsa_area(void)
+{
+	if (pgca_dsa_area == NULL)
+	{
+		pgca_shared_state *state = pg_collect_advice_attach();
+		dsa_handle	area_handle;
+		MemoryContext oldcontext;
+
+		oldcontext = MemoryContextSwitchTo(pg_collect_advice_get_mcxt());
+
+		LWLockAcquire(&state->lock, LW_EXCLUSIVE);
+		area_handle = state->area;
+		if (area_handle == DSA_HANDLE_INVALID)
+		{
+			pgca_dsa_area = dsa_create(state->dsa_tranche);
+			dsa_pin(pgca_dsa_area);
+			state->area = dsa_get_handle(pgca_dsa_area);
+			LWLockRelease(&state->lock);
+		}
+		else
+		{
+			LWLockRelease(&state->lock);
+			pgca_dsa_area = dsa_attach(area_handle);
+		}
+
+		dsa_pin_mapping(pgca_dsa_area);
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	return pgca_dsa_area;
+}
+
+/*
+ * After planning is complete, retrieve the advice string, if present, and
+ * pass it through to the collector.
+ */
+static void
+pgca_planner_shutdown(PlannerGlobal *glob, Query *parse,
+					  const char *query_string, PlannedStmt *pstmt)
+{
+	DefElem    *pgpa_item;
+	DefElem    *advice_string_item;
+	char	   *advice_string;
+
+	/*
+	 * Pass call to previous hook.
+	 *
+	 * We want to be called after pg_plan_advice's shutdown hook has already
+	 * executed. Our _PG_init() makes sure that pg_plan_advice's hooks are
+	 * always loaded before ours, and here we pass the hook call down first,
+	 * before doing our own work. The combination of those two things should
+	 * be good enough to ensure that the advice string is already present when
+	 * we go looking for it.
+	 */
+	if (prev_planner_shutdown)
+		(*prev_planner_shutdown) (glob, parse, query_string, pstmt);
+
+	/* Fish out the advice string. If not found, do nothing. */
+	pgpa_item = find_defelem_by_defname(pstmt->extension_state,
+										"pg_plan_advice");
+	if (pgpa_item == NULL)
+		return;
+	advice_string_item = find_defelem_by_defname((List *) pgpa_item->arg,
+												 "advice_string");
+	if (advice_string_item == NULL)
+		return;
+	advice_string = strVal(advice_string_item->arg);
+
+	/*
+	 * Pass it through to the actual collector. But, if it's the empty string,
+	 * we assume that collecting it is uninteresting.
+	 */
+	if (advice_string[0] != '\0')
+		pg_collect_advice_save(pstmt->queryId, query_string, advice_string);
+}
+
+/*
+ * pgca_planner_shutdown won't find any advice to collect unless we've
+ * requested that it be generated. So, whenever the effective value of
+ * pg_collect_advice.local_collector changes, either make or
+ * revoke a request for advice generation.
+ */
+static void
+pg_collect_advice_local_collector_assign_hook(bool newval, void *extra)
+{
+	if (pg_collect_advice_local_collector_as_assigned && !newval)
+		(*request_advice_generation_fn) (false);
+	if (!pg_collect_advice_local_collector_as_assigned && newval)
+		(*request_advice_generation_fn) (true);
+	pg_collect_advice_local_collector_as_assigned = newval;
+}
+
+/*
+ * Same as above, but for pg_collect_advice.shared_collector
+ */
+static void
+pg_collect_advice_shared_collector_assign_hook(bool newval, void *extra)
+{
+	if (pg_collect_advice_shared_collector_as_assigned && !newval)
+		(*request_advice_generation_fn) (false);
+	if (!pg_collect_advice_shared_collector_as_assigned && newval)
+		(*request_advice_generation_fn) (true);
+	pg_collect_advice_shared_collector_as_assigned = newval;
+}
+
+/*
+ * Search a list of DefElem objects for a given defname.
+ */
+static DefElem *
+find_defelem_by_defname(List *deflist, char *defname)
+{
+	foreach_node(DefElem, item, deflist)
+	{
+		if (strcmp(item->defname, defname) == 0)
+			return item;
+	}
+
+	return NULL;
+}
diff --git a/contrib/pg_collect_advice/meson.build b/contrib/pg_collect_advice/meson.build
new file mode 100644
index 00000000000..102dc65d260
--- /dev/null
+++ b/contrib/pg_collect_advice/meson.build
@@ -0,0 +1,41 @@
+# Copyright (c) 2022-2026, PostgreSQL Global Development Group
+
+pg_collect_advice_sources = files(
+  'collector.c',
+  'interface.c',
+)
+
+if host_system == 'windows'
+  pg_collect_advice_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+    '--NAME', 'pg_collect_advice',
+    '--FILEDESC', 'pg_collect_advice - collect queries and their plan advice strings',])
+endif
+
+pg_collect_advice = shared_module('pg_collect_advice',
+  pg_collect_advice_sources,
+  include_directories: include_directories('.'),
+  kwargs: contrib_mod_args,
+)
+contrib_targets += pg_collect_advice
+
+install_data(
+  'pg_collect_advice--1.0.sql',
+  'pg_collect_advice.control',
+  kwargs: contrib_data_args,
+)
+
+tests += {
+  'name': 'pg_collect_advice',
+  'sd': meson.current_source_dir(),
+  'bd': meson.current_build_dir(),
+  'regress': {
+    'sql': [
+      'local_collector',
+    ],
+  },
+  'tap': {
+    'tests': [
+      't/001_shared_collector.pl',
+    ],
+  },
+}
diff --git a/contrib/pg_collect_advice/pg_collect_advice--1.0.sql b/contrib/pg_collect_advice/pg_collect_advice--1.0.sql
new file mode 100644
index 00000000000..0be86c54fc1
--- /dev/null
+++ b/contrib/pg_collect_advice/pg_collect_advice--1.0.sql
@@ -0,0 +1,43 @@
+/* contrib/pg_collect_advice/pg_collect_advice--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_collect_advice" to load this file. \quit
+
+CREATE FUNCTION pg_clear_collected_local_advice()
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_clear_collected_local_advice'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_clear_collected_shared_advice()
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_clear_collected_shared_advice'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_get_collected_local_advice(
+	OUT id bigint,
+	OUT userid oid,
+	OUT dbid oid,
+	OUT queryid bigint,
+	OUT collection_time timestamptz,
+	OUT query text,
+	OUT advice text
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_collected_local_advice'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_get_collected_shared_advice(
+	OUT id bigint,
+	OUT userid oid,
+	OUT dbid oid,
+	OUT queryid bigint,
+	OUT collection_time timestamptz,
+	OUT query text,
+	OUT advice text
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_collected_shared_advice'
+LANGUAGE C STRICT;
+
+REVOKE ALL ON FUNCTION pg_clear_collected_shared_advice() FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_get_collected_shared_advice() FROM PUBLIC;
diff --git a/contrib/pg_collect_advice/pg_collect_advice.control b/contrib/pg_collect_advice/pg_collect_advice.control
new file mode 100644
index 00000000000..601e5e24ea1
--- /dev/null
+++ b/contrib/pg_collect_advice/pg_collect_advice.control
@@ -0,0 +1,5 @@
+# pg_collect_advice extension
+comment = 'collect queries and the associated plan advice'
+default_version = '1.0'
+module_pathname = '$libdir/pg_collect_advice'
+relocatable = true
diff --git a/contrib/pg_collect_advice/pg_collect_advice.h b/contrib/pg_collect_advice/pg_collect_advice.h
new file mode 100644
index 00000000000..480c2c633c4
--- /dev/null
+++ b/contrib/pg_collect_advice/pg_collect_advice.h
@@ -0,0 +1,39 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_collect_advice.h
+ *	  definitions and declarations for pg_collect_advice module
+ *
+ * Copyright (c) 2016-2026, PostgreSQL Global Development Group
+ *
+ *	  contrib/pg_collect_advice/pg_collect_advice.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_COLLECT_ADVICE_H
+#define PG_COLLECT_ADVICE_H
+
+#include "storage/lwlock.h"
+#include "utils/dsa.h"
+
+typedef struct pgca_shared_state
+{
+	LWLock		lock;
+	int			dsa_tranche;
+	dsa_handle	area;
+	dsa_pointer shared_collector;
+} pgca_shared_state;
+
+/* GUC variables */
+extern bool pg_collect_advice_local_collector;
+extern int	pg_collect_advice_local_collection_limit;
+extern bool pg_collect_advice_shared_collector;
+extern int	pg_collect_advice_shared_collection_limit;
+
+/* Function prototypes */
+extern MemoryContext pg_collect_advice_get_mcxt(void);
+extern pgca_shared_state *pg_collect_advice_attach(void);
+extern dsa_area *pg_collect_advice_dsa_area(void);
+extern void pg_collect_advice_save(uint64 queryId, const char *query_string,
+								   const char *advice_string);
+
+#endif
diff --git a/contrib/pg_collect_advice/sql/local_collector.sql b/contrib/pg_collect_advice/sql/local_collector.sql
new file mode 100644
index 00000000000..41b187c5375
--- /dev/null
+++ b/contrib/pg_collect_advice/sql/local_collector.sql
@@ -0,0 +1,46 @@
+CREATE EXTENSION pg_collect_advice;
+SET debug_parallel_query = off;
+
+-- Try clearing advice before we've collected any.
+SELECT pg_clear_collected_local_advice();
+
+-- Set a small advice collection limit so that we'll exceed it.
+SET pg_collect_advice.local_collection_limit = 2;
+
+-- Enable the collector.
+SET pg_collect_advice.local_collector = on;
+
+-- Set up a dummy table.
+CREATE TABLE dummy_table (a int primary key, b text)
+	WITH (autovacuum_enabled = false, parallel_workers = 0);
+
+-- Test queries.
+SELECT * FROM dummy_table a, dummy_table b;
+SELECT * FROM dummy_table;
+
+-- Should return the advice from the second test query.
+SET pg_collect_advice.local_collector = off;
+SELECT advice FROM pg_get_collected_local_advice() ORDER BY id DESC LIMIT 1;
+
+-- Now try clearing advice again.
+SELECT pg_clear_collected_local_advice();
+
+-- Raise the collection limit so that the collector uses multiple chunks.
+SET pg_collect_advice.local_collection_limit = 2000;
+SET pg_collect_advice.local_collector = on;
+
+-- Push a bunch of queries through the collector.
+DO $$
+BEGIN
+	FOR x IN 1..2000 LOOP
+		EXECUTE 'SELECT * FROM dummy_table';
+	END LOOP;
+END
+$$;
+
+-- Check that the collector worked.
+SELECT COUNT(*) FROM pg_get_collected_local_advice();
+
+-- And clear one more time, to verify that this doesn't cause a problem
+-- even with a larger number of entries.
+SELECT pg_clear_collected_local_advice();
diff --git a/contrib/pg_collect_advice/t/001_shared_collector.pl b/contrib/pg_collect_advice/t/001_shared_collector.pl
new file mode 100644
index 00000000000..bba0c883e5a
--- /dev/null
+++ b/contrib/pg_collect_advice/t/001_shared_collector.pl
@@ -0,0 +1,154 @@
+# Copyright (c) 2021-2026, PostgreSQL Global Development Group
+
+# Test the shared advice collector.
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Helper function, to avoid depending on exact line-break behavior.
+sub smash_whitespace
+{
+	my $s = shift;
+	$s =~ s/^\s+//;
+	$s =~ s/\s+$//;
+	$s =~ s/\s+/ /g;
+	return $s;
+}
+
+# Retrieve all collected shared advice as an array of whitespace-normalized
+# strings, ordered by id.
+sub get_collected_shared_advice
+{
+	my $psql = shift;
+	my $output = $psql->query_safe(
+		"SELECT string_agg(advice, '!SEPARATOR!' ORDER BY id) "
+		. "FROM pg_get_collected_shared_advice()");
+	return () if $output eq '';
+	return map { smash_whitespace($_) } split(/!SEPARATOR!/, $output);
+}
+
+# Initialize the primary node
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init();
+
+# Load pg_collect_advice and configure a shared collection limit of 5.
+$node->append_conf('postgresql.conf', <<EOM);
+shared_preload_libraries=pg_collect_advice
+pg_collect_advice.shared_collection_limit=5
+EOM
+$node->start;
+
+# Create the extension so we can access the collector
+my $test_db = 'collection_test';
+my $test_role = 'collection_role';
+$node->safe_psql('postgres', <<EOM);
+CREATE DATABASE $test_db;
+CREATE USER $test_role;
+ALTER ROLE $test_role SET pg_collect_advice.shared_collector = on;
+EOM
+$node->safe_psql($test_db, 'CREATE EXTENSION pg_collect_advice');
+
+# Set up two connections, one to control the testing process, and the other
+# to execute the queries under test.
+my $psql_control = $node->background_psql($test_db, on_error_stop => 1);
+my $psql_test =
+	$node->background_psql($test_db, on_error_stop => 1,
+						   extra_params => [ '--username' => $test_role ]);
+
+# Initial setup.
+$psql_control->query_safe(<<EOM);
+GRANT CREATE ON SCHEMA public TO $test_role;
+GRANT SET ON PARAMETER pg_collect_advice.shared_collection_limit TO $test_role;
+SET ROLE $test_role;
+CREATE TABLE sac_dim (id serial primary key, dim text)
+	WITH (autovacuum_enabled = false);
+INSERT INTO sac_dim (dim) SELECT random()::text FROM generate_series(1,100) g;
+VACUUM ANALYZE sac_dim;
+
+CREATE TABLE sac_fact (
+	id int primary key,
+	dim_id integer not null references sac_dim (id)
+) WITH (autovacuum_enabled = false);
+INSERT INTO sac_fact
+SELECT g, (g%3)+1 FROM generate_series(1,100000) g;
+CREATE INDEX sac_fact_dim_id ON sac_fact (dim_id);
+VACUUM ANALYZE sac_fact;
+RESET ROLE;
+EOM
+
+# Run a few test queries.
+$psql_test->query_safe(<<'EOM');
+SELECT * FROM sac_fact WHERE id = 42;
+SELECT * FROM sac_dim d JOIN sac_fact f ON d.id = f.dim_id;
+SELECT * FROM sac_dim d
+    WHERE d.id IN (SELECT f.dim_id FROM sac_fact f);
+EOM
+
+# Check that we got three advice collections, and the right values for each.
+my @advice = get_collected_shared_advice($psql_control);
+is(scalar @advice, 3, "three advice entries collected");
+is($advice[0], 'INDEX_SCAN(sac_fact public.sac_fact_pkey) NO_GATHER(sac_fact)',
+	"correct advice for query 1");
+is($advice[1], 'JOIN_ORDER(f d) HASH_JOIN(d) SEQ_SCAN(f d) NO_GATHER(d f)',
+	"correct advice for query 2");
+is($advice[2], 'JOIN_ORDER(d f) NESTED_LOOP_PLAIN(f) SEQ_SCAN(d) INDEX_ONLY_SCAN(f public.sac_fact_dim_id) SEMIJOIN_NON_UNIQUE(f) NO_GATHER(d f)',
+	"correct advice for query 3");
+
+# Run a few more test queries, overrunning the limit. (SET and PREPARE don't
+# trigger planning, but EXECUTE does.)
+$psql_test->query_safe(<<'EOM');
+BEGIN;
+SET LOCAL min_parallel_table_scan_size = 0;
+SET LOCAL parallel_setup_cost = 0;
+SET LOCAL parallel_tuple_cost = 0;
+SELECT count(*) FROM sac_fact;
+COMMIT;
+EXPLAIN SELECT * FROM sac_dim;
+PREPARE test_stmt AS SELECT * FROM sac_fact WHERE id = $1;
+EXECUTE test_stmt(42);
+EOM
+
+# Check that advice collection was trimmed to the configured limit.
+@advice = get_collected_shared_advice($psql_control);
+is(scalar @advice, 5, "advice trimmed to collection limit");
+
+# Check the advice for queries 4, 5, and 6.
+is($advice[2], 'SEQ_SCAN(sac_fact) GATHER(sac_fact)',
+	"correct advice for query 4");
+is($advice[3], 'SEQ_SCAN(sac_dim) NO_GATHER(sac_dim)',
+	"correct advice for query 5");
+is($advice[4],
+	'INDEX_SCAN(sac_fact public.sac_fact_pkey) NO_GATHER(sac_fact)',
+	"correct advice for query 6");
+
+# Raise the collection limit so that we can collect enough advice to need
+# multiple chunks, and then revert back to the old value, so that we try
+# to free an entire chunk.
+$psql_test->query_safe("SET pg_collect_advice.shared_collection_limit = 1500");
+$psql_test->query_safe(<<'EOM');
+DO $$
+BEGIN
+	FOR i IN 1..1500 LOOP
+		EXECUTE 'SELECT 1';
+	END LOOP;
+END $$;
+EOM
+@advice = get_collected_shared_advice($psql_control);
+is(scalar @advice, 1500, "increased collection limit reached");
+$psql_test->query_safe("RESET pg_collect_advice.shared_collection_limit");
+$psql_test->query_safe("SELECT * FROM sac_dim");
+@advice = get_collected_shared_advice($psql_control);
+is(scalar @advice, 5, "advice trimmed across chunk boundary");
+
+# Try clearing all the advice.
+$psql_control->query_safe("SELECT pg_clear_collected_shared_advice()");
+@advice = get_collected_shared_advice($psql_control);
+is(scalar @advice, 0, "all shared advice cleared");
+
+# Clean up.
+$psql_test->quit;
+$psql_control->quit;
+done_testing();
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index bdd4865f53f..2ab6fafbab1 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -152,6 +152,7 @@ CREATE EXTENSION <replaceable>extension_name</replaceable>;
  &pageinspect;
  &passwordcheck;
  &pgbuffercache;
+ &pgcollectadvice;
  &pgcrypto;
  &pgfreespacemap;
  &pglogicalinspect;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index d90b4338d2a..407ff3abffe 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -145,6 +145,7 @@
 <!ENTITY pageinspect     SYSTEM "pageinspect.sgml">
 <!ENTITY passwordcheck   SYSTEM "passwordcheck.sgml">
 <!ENTITY pgbuffercache   SYSTEM "pgbuffercache.sgml">
+<!ENTITY pgcollectadvice SYSTEM "pgcollectadvice.sgml">
 <!ENTITY pgcrypto        SYSTEM "pgcrypto.sgml">
 <!ENTITY pgfreespacemap  SYSTEM "pgfreespacemap.sgml">
 <!ENTITY pglogicalinspect  SYSTEM "pglogicalinspect.sgml">
diff --git a/doc/src/sgml/pgcollectadvice.sgml b/doc/src/sgml/pgcollectadvice.sgml
new file mode 100644
index 00000000000..220aabe78c6
--- /dev/null
+++ b/doc/src/sgml/pgcollectadvice.sgml
@@ -0,0 +1,244 @@
+<!-- doc/src/sgml/pgcollectadvice.sgml -->
+
+<sect1 id="pgcollectadvice" xreflabel="pg_collect_advice">
+ <title>pg_collect_advice &mdash; collect queries and their plan advice strings</title>
+
+ <indexterm zone="pgcollectadvice">
+  <primary>pg_collect_advice</primary>
+ </indexterm>
+
+ <para>
+  The <filename>pg_collect_advice</filename> extension allows you to
+  automatically generate plan advice each time a query is planned and store
+  the query and the generated advice string either in local or shared memory.
+  Note that this extension requires the <xref linkend="pgplanadvice" /> module,
+  which performs the actual plan advice generation; this module only knows
+  how to store the generated advice for later examination. Whenever
+  <literal>pg_collect_advice</literal> is loaded, it will automatically load
+  <literal>pg_plan_advice</literal>.
+ </para>
+
+ <para>
+  In order to use this module, you will need to execute
+  <literal>CREATE EXTENSION pg_collect_advice</literal> in at least
+  one database, so that you have a way to examine the collected advice.
+  You will also need the <literal>pg_collect_advice</literal> module
+  to be loaded in all sessions where advice is to be collected. It will
+  usually be best to do this by adding <literal>pg_collect_advice</literal>
+  to <xref linkend="guc-shared-preload-libraries"/> and restarting the
+  server.
+ </para>
+
+ <para>
+  <literal>pg_collect_advice</literal> includes both a shared advice
+  collector and a local advice collector. The local advice collector makes
+  queries and their advice strings visible only to the session where those
+  queries were planned, while the shared advice collector collects data
+  on a system-wide basis, and authorized users can examine data from all
+  sessions.
+ </para>
+
+ <para>
+  To enable a collector, you must first set a collection limit. When the
+  number of queries for which advice has been stored exceeds the collection
+  limit, the oldest queries and the corresponding advice will be discarded.
+  Then, you must adjust a separate setting to actually enable advice
+  collection. For the local collector, set the collection limit by configuring
+  <literal>pg_collect_advice.local_collection_limit</literal> to a value
+  greater than zero, and then enable advice collection by setting
+  <literal>pg_collect_advice.local_collector = true</literal>. For the shared
+  collector, the procedure is the same, except that the names of the settings
+  are <literal>pg_collect_advice.shared_collection_limit</literal> and
+  <literal>pg_collect_advice.shared_collector</literal>. Note that in both
+  cases, query texts and advice strings are stored in memory, so
+  configuring large limits may result in considerable memory consumption.
+ </para>
+
+ <para>
+  Once the collector is enabled, you can run any queries for which you wish
+  to see the generated plan advice. Then, you can examine what has been
+  collected using whichever of
+  <literal>SELECT * FROM pg_get_collected_local_advice()</literal> or
+  <literal>SELECT * FROM pg_get_collected_shared_advice()</literal>
+  corresponds to the collector you enabled. To discard the collected advice
+  and release memory, you can call
+  <literal>pg_clear_collected_local_advice()</literal>
+  or <literal>pg_clear_collected_shared_advice()</literal>.
+ </para>
+
+ <para>
+  In addition to the query texts and advice strings, the advice collectors
+  will also store the OID of the role that caused the query to be planned,
+  the OID of the database in which the query was planned, the query ID,
+  and the time at which the collection occurred. This module does not
+  automatically enable query ID computation; therefore, if you want the
+  query ID value to be populated in collected advice, be sure to configure
+  <literal>compute_query_id = on</literal>. Otherwise, the query ID may
+  always show as <literal>0</literal>.
+ </para>
+
+ <sect2 id="pgcollectadvice-functions">
+  <title>Functions</title>
+
+  <variablelist>
+
+   <varlistentry>
+    <term>
+     <function>pg_clear_collected_local_advice() returns void</function>
+     <indexterm>
+      <primary>pg_clear_collected_local_advice</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      Removes all collected query texts and advice strings from backend-local
+      memory.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <function>pg_get_collected_local_advice() returns setof (id bigint,
+        userid oid, dbid oid, queryid bigint, collection_time timestamptz,
+        query text, advice text)</function>
+     <indexterm>
+      <primary>pg_get_collected_local_advice</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      Returns all query texts and advice strings stored in the local
+      advice collector.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <function>pg_clear_collected_shared_advice() returns void</function>
+     <indexterm>
+      <primary>pg_clear_collected_shared_advice</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      Removes all collected query texts and advice strings from shared
+      memory.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <function>pg_get_collected_shared_advice() returns setof (id bigint,
+        userid oid, dbid oid, queryid bigint, collection_time timestamptz,
+        query text, advice text)</function>
+     <indexterm>
+      <primary>pg_get_collected_shared_advice</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      Returns all query texts and advice strings stored in the shared
+      advice collector.
+     </para>
+    </listitem>
+   </varlistentry>
+
+  </variablelist>
+
+ </sect2>
+
+ <sect2 id="pgcollectadvice-config-params">
+  <title>Configuration Parameters</title>
+
+  <variablelist>
+
+   <varlistentry>
+    <term>
+     <varname>pg_collect_advice.local_collector</varname> (<type>boolean</type>)
+     <indexterm>
+      <primary><varname>pg_collect_advice.local_collector</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      <varname>pg_collect_advice.local_collector</varname> enables the
+      local advice collector. The default value is <literal>false</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <varname>pg_collect_advice.local_collection_limit</varname> (<type>integer</type>)
+     <indexterm>
+      <primary><varname>pg_collect_advice.local_collection_limit</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      <varname>pg_collect_advice.local_collection_limit</varname> sets the
+      maximum number of query texts and advice strings retained by the
+      local advice collector. The default value is <literal>0</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <varname>pg_collect_advice.shared_collector</varname> (<type>boolean</type>)
+     <indexterm>
+      <primary><varname>pg_collect_advice.shared_collector</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      <varname>pg_collect_advice.shared_collector</varname> enables the
+      shared advice collector. The default value is <literal>false</literal>.
+      Only superusers and users with the appropriate <literal>SET</literal>
+      privilege can change this setting.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <varname>pg_collect_advice.shared_collection_limit</varname> (<type>integer</type>)
+     <indexterm>
+      <primary><varname>pg_collect_advice.shared_collection_limit</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      <varname>pg_collect_advice.shared_collection_limit</varname> sets the
+      maximum number of query texts and advice strings retained by the
+      shared advice collector. The default value is <literal>0</literal>.
+      Only superusers and users with the appropriate <literal>SET</literal>
+      privilege can change this setting.
+     </para>
+    </listitem>
+   </varlistentry>
+
+  </variablelist>
+
+ </sect2>
+
+ <sect2 id="pgcollectadvice-author">
+  <title>Author</title>
+
+  <para>
+   Robert Haas <email>rhaas@postgresql.org</email>
+  </para>
+ </sect2>
+
+</sect1>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index decc9f7a572..d41dbbfa801 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -4019,6 +4019,12 @@ pg_uuid_t
 pg_wchar
 pg_wchar_tbl
 pgp_armor_headers_state
+pgca_collected_advice
+pgca_local_advice
+pgca_local_advice_chunk
+pgca_shared_advice
+pgca_shared_advice_chunk
+pgca_shared_state
 pgpa_advice_item
 pgpa_advice_tag_type
 pgpa_advice_target
-- 
2.51.0