On Sun, Mar 1, 2026 at 9:10 PM David G. Johnston <[email protected]>
wrote:
> On Fri, Feb 27, 2026 at 6:16 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Fri, Feb 27, 2026 at 3:46 PM Robert Haas <[email protected]>
>> wrote:
>>
>>> On Thu, Feb 26, 2026 at 8:55 AM Robert Haas <[email protected]>
>>> wrote:
>>> > Thanks, Alex, for the review.
>>>
>>> Here's v18. In addition to fixing the problems pointed out by Alex,
>>> there are a couple of significant changes in this version.
>>>
>>>
>> I have a mind to walk through the readmes and sgmls but its going to be
>> in chunks. Here's one for the readme for pg_plan_advice with a couple of
>> preliminary sgml changes.
>>
>>
> 0003 sgml focus with some readme.
>
>
And now 0004 sgml (no readme):
My OCD wants these named pg_advice_{plan,collect,stash} so they sort
together.
Strongly thinking using "entries" throughout makes more sense than "query
texts and advice string" - it is shorter and more inclusive since the
actual stored info covers IDs and timestamp.
I made one swap where shared was being mentioned before local.
I added some unresearched answers to open questions I had at the end of the
main section. Namely, pertaining to advice feedback output and capturing
explain plans themselves.
David J.
diff --git a/doc/src/sgml/pgcollectadvice.sgml b/doc/src/sgml/pgcollectadvice.sgml
index fd7d879d816..ba2837c3982 100644
--- a/doc/src/sgml/pgcollectadvice.sgml
+++ b/doc/src/sgml/pgcollectadvice.sgml
@@ -11,11 +11,16 @@
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.
+ Collection enablement and entries are described below, but importantly,
+ there is no deduplication involved; each planning event produces one entry,
+ and a mandatory cap on the number of entries allowed must be set before
+ enabling collection.
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>.
+ which performs the actual plan advice generation; this module just handles
+ enabling automatic generation and performing the storage of the generated
+ advice for later examination through the extension's views.
+ Whenever <literal>pg_collect_advice</literal> is loaded, it will automatically
+ load <literal>pg_plan_advice</literal>.
</para>
<para>
@@ -30,12 +35,14 @@
</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.
+ <literal>pg_collect_advice</literal> includes both a local advice
+ collector and a shared advice collector.
+ The local advice collector stores entries locally to the session where those
+ queries were planned and requires the extension to be installed within the
+ database the session is connected to.
+ The shared advice collector stores entries for all databases in the cluster,
+ and authorized users can examine entries from any session connected to a
+ database where the extension is installed.
</para>
<para>
@@ -67,14 +74,26 @@
</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,
+ For each entry in a collector, in addition to the query texts and advice
+ strings, the advice collector 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>.
+ query ID value to be populated in collected advice, be sure the setting
+ <literal>compute_query_id</literal> is set to <literal>on</literal>,
+ otherwise the query ID will appear as <literal>0</literal>.
+ </para>
+
+ <para>
+ The pg_plan_advice module also produces feedback; the collector
+ is unable to capture such feedback as it relies on running explain
+ with PLAN_ADVICE which does not happen during normal execution.
+ </para>
+
+ <para>
+ Plan advice is not a substitute for seeing a query plan. The auto_explain
+ module can be used (imprecisely) in conjunction with this module to add explain
+ output to the corpus of planner data automatically captured for executed queries.
</para>
<sect2 id="pgcollectadvice-functions">
@@ -92,8 +111,7 @@
<listitem>
<para>
- Removes all collected query texts and advice strings from backend-local
- memory.
+ Removes all collected entries from backend-local memory.
</para>
</listitem>
</varlistentry>
@@ -110,8 +128,7 @@
<listitem>
<para>
- Returns all query texts and advice strings stored in the local
- advice collector.
+ Returns all entries stored in the local advice collector.
</para>
</listitem>
</varlistentry>
@@ -126,8 +143,7 @@
<listitem>
<para>
- Removes all collected query texts and advice strings from shared
- memory.
+ Removes all collected entires from shared memory.
</para>
</listitem>
</varlistentry>
@@ -144,8 +160,7 @@
<listitem>
<para>
- Returns all query texts and advice strings stored in the shared
- advice collector.
+ Returns all entries stored in the shared advice collector.
</para>
</listitem>
</varlistentry>
@@ -170,7 +185,7 @@
<listitem>
<para>
<varname>pg_collect_advice.local_collector</varname> enables the
- local advice collector. The default value is <literal>false</literal>.
+ local advice collector. The default value is <literal>false</literal>.
</para>
</listitem>
</varlistentry>
@@ -186,8 +201,8 @@
<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>.
+ maximum number of entries retained by the local advice collector.
+ The default value is <literal>0</literal>.
</para>
</listitem>
</varlistentry>
@@ -203,7 +218,7 @@
<listitem>
<para>
<varname>pg_collect_advice.shared_collector</varname> enables the
- shared advice collector. The default value is <literal>false</literal>.
+ 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>
@@ -221,8 +236,8 @@
<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>.
+ maximum number of entries 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>
