On Wed, Jan 28, 2026 at 10:14 AM Robert Haas <[email protected]> wrote:
> On Mon, Jan 12, 2026 at 12:13 PM Robert Haas <[email protected]> > wrote: > > If not, I'll rearrange > > the series to move 0004 to the front, and plan to commit that first. > > That rearrangement got done in v11, and I've now committed that patch > after fixing a few typos that I found. Cautiously, woohoo, but let's > see if anything breaks. > > Here's v13. > Documentation review patch attached. Overall it flowed well. Most of the changes are just typos or similar. I found the idea of seeing "Disabled: true" to not make immediate sense. I've left some comments near that section and plan to come back to it. The comment about collector memory usage was duplicated more-or-less. I kept the first, less loud, instance. Removed the few uses of "we" in favor of pg_plan_advice being the actor. David J.
From bc40cb9dd2c1c3ac27c44ecbbae6c450745879d6 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" <[email protected]> Date: Wed, 28 Jan 2026 21:47:45 -0700 Subject: [PATCH] Docs review --- doc/src/sgml/pgplanadvice.sgml | 115 +++++++++++++++++---------------- 1 file changed, 58 insertions(+), 57 deletions(-) diff --git a/doc/src/sgml/pgplanadvice.sgml b/doc/src/sgml/pgplanadvice.sgml index b319f90febe..07c3d2a0121 100644 --- a/doc/src/sgml/pgplanadvice.sgml +++ b/doc/src/sgml/pgplanadvice.sgml @@ -21,7 +21,7 @@ underlying data changes, the planner normally has the option to adjust the plan in an attempt to preserve good performance. If the plan advice prevents this, a very poor plan may be chosen. It is important to use plan advice - only when risks of constraining the planner's choices are outweighed by + only when the risks of constraining the planner's choices are outweighed by the benefits. </para> @@ -39,7 +39,7 @@ <link linkend="sql-load"><literal>LOAD</literal></link> command. If you wish to use the <link linkend="pgplanadvice-collector">collector interface</link>, - you must also the <literal>pg_plan_advice</literal> extension + you must also install the <literal>pg_plan_advice</literal> extension in the database where you wish to use the collector. Use the command <literal>CREATE EXTENSION pg_plan_advice</literal> to do this. If you do not wish to use the collector interface, this step is not required. @@ -48,8 +48,8 @@ <para> Once the <literal>pg_plan_advice</literal> module is loaded, <link linkend="sql-explain"><literal>EXPLAIN</literal></link> will support - a <literal>PLAN_ADVICE</literal> option. You can use this option to see - a plan advice string for the chosen plan. For example: + a <literal>PLAN_ADVICE</literal> option. You can use this option to generate + the plan advice pertaining to the chosen plan. For example: </para> <programlisting> @@ -99,9 +99,9 @@ EXPLAIN (COSTS OFF, PLAN_ADVICE) Once you have an advice string for a query, you can use it to control how that query is planned. You can do this by setting <literal>pg_plan_advice.advice</literal> to the advice string you've - chosen. This can be an advice string that was generated by the system, + chosen. This can be an advice string that was generated by <literal>pg_plan_advice</literal>, or one you've written yourself. One good way of creating your own advice - string is to take the string generated by the system and pick out just + string is to take the string generated by <literal>pg_plan_advice</literal> and pick out just those elements that you wish to enforce. In the example above, <literal>pg_plan_advice</literal> emits advice for the join order, the join method, the scan method, and the use of parallelism, but you might @@ -146,19 +146,19 @@ EXPLAIN (COSTS OFF) <para> Plan advice is written imperatively; that is, it specifies what should be - done. However, at an implementation level, + done. However, at the implementation level, <literal>pg_plan_advice</literal> works by telling the core planner what should not be done. In other words, it operates by constraining the - planner's choices, not by replacing it. Therefore, no matter what advice + planner's choices, not by dictating them. Therefore, no matter what advice you provide, you will only ever get a plan that the core planner would have considered for the query in question. If you attempt to force what you believe to be the correct plan by supplying an advice string, and the - planner still fails to produce the desired plan, this means that either - there is a bug in your advice string, or the plan in question was not + planner fails to produce the desired plan, there is either + a bug in your advice string, or the plan in question was not considered viable by the core planner. This commonly happens for one of two reasons. First, it might be the planner believes that the plan you're trying - to force would be semantically incorrect -- that is, it would produce the - wrong results -- and for that reason it wasn't considered. Second, it might + to force would be semantically incorrect - that is, it would produce the + wrong results - and for that reason it wasn't considered. Second, it might be that the planner rejected the plan you were hoping to generate on some grounds other than cost. For example, given a very simple query such as <literal>SELECT * FROM some_table</literal>, the query planner will @@ -176,6 +176,12 @@ EXPLAIN (COSTS OFF) basically the same plan you would have gotten with no supplied advice at all, but in other cases, they may be much worse. For example: </para> + <!-- this whole disabled thing needs work for me to understand + the output "Advice Ignored: JOIN_ORDER(x f d)" would be nicer. + but maybe it cannot be that specific...noting that when Advice + is ultimately ignored the chosen option may not match the choice + that would have happened had no advice been given. + --> <programlisting> SET pg_plan_advice.advice = 'JOIN_ORDER(x f d)'; @@ -218,8 +224,8 @@ EXPLAIN (COSTS OFF) cases, such as the examples shown above, the advice target is simply the relation alias. However, a more complex syntax is required when subqueries are used, when tables are partitioned, or when the same relation alias is - mentioned more than once in the same subquery (e.g. <literal>(foo JOIN bar - ON foo.a = bar.a) x JOIN foo ON x.b = foo.b</literal>. Any combination of + mentioned more than once in the same subquery (e.g., <literal>(foo JOIN bar + ON foo.a = bar.a) x JOIN foo ON x.b = foo.b</literal>). Any combination of these three things can occur simultaneously: a relation could be mentioned more than once, be partitioned, and be used inside of a subquery. </para> @@ -234,22 +240,22 @@ alias_name#occurrence_number/partition_schema.partition_name@plan_name <para> All components except for the <literal>alias_name</literal> are optional - and included only when required. When a component is omitted, the associated + and are included only when required. When a component is omitted, the preceding punctuation must also be omitted. For the first occurrence of a table within a given subquery, generated advice will omit the occurrence number, but it is legal to write <literal>#1</literal>, if desired. The partition schema and partition name are included only for children of partitioned - tables. In generated advice, we always incude both, but it is legal to - omit the schema. The plan name is omitted for the toplevel plan, and must - be included for any subplan. + tables. In generated advice, <literal>pg_plan_advice</literal> always includes + both, but it is legal to omit the schema. The plan name is omitted for the + top-level plan, and must be included for any subplan. </para> <para> It is not always easy to determine the correct advice target by examining - the query; for instance, if the planner pulls up a subquery into the parent + the query. For instance, if the planner pulls up a subquery into the parent query level, everything inside of it becomes part of the parent query level, and uses the parent query's subplan name (or no subplan name, if pulled up - to the toplevel). Furthermore, the correct subquery name is sometimes not + to the top level). Furthermore, the correct subquery name is sometimes not obvious. For example, when two queries are joined using an operation such as <literal>UNION</literal> or <literal>INTERSECT</literal>, no name for the subqueries is present in the SQL syntax; instead, a system-generated name is @@ -301,23 +307,21 @@ BITMAP_HEAP_SCAN(<replaceable>target</replaceable> [ ... ])</synopsis> </para> <para> - <literal>FOREIGN_SCAN</literal> specifies that a foreign join between - a several foreign tables should be pushed down to a remote server so + <literal>FOREIGN_SCAN</literal> specifies that a join between + two or more foreign tables should be pushed down to a remote server so that it can be implenented as a single <literal>Foreign Scan</literal>. - Specifying <literal>FOREIGN_SCAN</literal> for a single foreign table is - neither necessary nor permissible: a <literal>Foreign Scan</literal> will - need to be used regardless. If you want to prevent a join from being + If you want to prevent a join from being pushed down, consider using the <literal>JOIN_ORDER</literal> tag for that purpose. </para> <para> - The planner supports many types of scans other than those listed here; - however, in most of those cases, there is no meaningful decision to be - made, and hence no need for advice. For example, the output of a + While the planner support additional scan types most are omitted as + there are no meaningful options available to them to be advised to + prefer. + For example, the output of a set-returning function that appears in the <literal>FROM</literal> clause - can only ever be scanned using a <literal>Function Scan</literal>, so - there is no opportunity for advice to change anything. + can only ever be scanned using a <literal>Function Scan</literal>. </para> </sect3> @@ -337,7 +341,7 @@ JOIN_ORDER(<replaceable>join_order_item</replaceable> [ ... ]) When <literal>JOIN_ORDER</literal> is used without any sublists, it specifies an outer-deep join with the first advice target as the driving table, joined to each subsequent advice target in turn in the order - specified. For instance, <literal>JOIN_ORDER(a b c)</literal> means that + specified. For example, <literal>JOIN_ORDER(a b c)</literal> means that <literal>a</literal> should be the driving table, and that it should be joined first to <literal>b</literal> and then to <literal>c</literal>. If there are more tables in the query than <literal>a</literal>, @@ -348,7 +352,7 @@ JOIN_ORDER(<replaceable>join_order_item</replaceable> [ ... ]) <para> If a <literal>JOIN_ORDER</literal> list contains a parenthesized sublist, it specifies a non-outer-deep join. The tables in the sublist must first - be joined to each other much as if the sublist were a toplevel + be joined to each other much as if the sublist were a top-level <literal>JOIN_ORDER</literal> list, and the resulting join product must then appear on the inner side of a join at the appropriate point in the join order. For example, <literal>JOIN_ORDER(a (b c) d)</literal> requires @@ -366,12 +370,12 @@ Join </programlisting> <para> - If a <literal>JOIN_ORDER</literal> list contains a sublist surronded by + If a <literal>JOIN_ORDER</literal> list contains a sublist surrounded by curly braces, this also specifies a non-outer-deep join. However, the join order within the sublist is not constrained. For example, specifiying - <literal>JOIN_ORDER(a {b c} d</literal> would allow the scans of + <literal>JOIN_ORDER(a {b c} d)</literal> would allow the scans of <literal>b</literal> and <literal>c</literal> to be swapped in the - previous example, which is not allowed when parenthese are used. + previous example, which is not allowed when parentheses are used. </para> <para> @@ -401,7 +405,7 @@ join_method_name(<replaceable>join_method_item</replaceable> [ ... ]) <phrase>and <replaceable>join_method_item</replaceable> is:</phrase> -<replaceable>advice_target</replaceable> | +{ <replaceable>advice_target</replaceable> | ( <replaceable>advice_target</replaceable> [ ... ] ) }</synopsis> <para> @@ -409,13 +413,13 @@ join_method_name(<replaceable>join_method_item</replaceable> [ ... ]) appear on the inner side of a join using the named join method. For example, <literal>HASH_JOIN(a b)</literal> means that each of <literal>a</literal> and <literal>b</literal> should appear on the inner - side of a hash join; a confirming plan must contain at least two hash + side of a hash join; a conforming plan must contain at least two hash joins, one of which has <literal>a</literal> and nothing else on the - inner side, and the other of which has <literal>b</literal> and nothing + inner side, and the other having <literal>b</literal> and nothing else on the inner side. On the other hand, <literal>HASH_JOIN((a b))</literal> means that the join product of <literal>a</literal> and <literal>b</literal> should appear together - on the inner side of a a single hash join. + on the inner side of a single hash join. </para> <para> @@ -442,7 +446,7 @@ PARTITIONWISE(<replaceable>partitionwise_item</replaceable> [ ... ]) <phrase>where <replaceable>partitionwise_item</replaceable> is:</phrase> -<replaceable>advice_target</replaceable> | +{ <replaceable>advice_target</replaceable> | ( <replaceable>advice_target</replaceable> [ ... ] ) }</synopsis> <para> @@ -465,7 +469,7 @@ SEMIJOIN_NON_UNIQUE(<replaceable>sj_unique_item</replaceable> [ ... ]) <phrase>where <replaceable>sj_unique_item</replaceable> is:</phrase> -<replaceable>advice_target</replaceable> | +{ <replaceable>advice_target</replaceable> | ( <replaceable>advice_target</replaceable> [ ... ] ) }</synopsis> <para> @@ -488,7 +492,7 @@ NO_GATHER(<replaceable>advice_target</replaceable> [ ... ]) <phrase>where <replaceable>gather_item</replaceable> is:</phrase> -<replaceable>advice_target</replaceable> | +{ <replaceable>advice_target</replaceable> | ( <replaceable>advice_target</replaceable> [ ... ] ) }</synopsis> <para> @@ -559,7 +563,7 @@ rhaas=# EXPLAIN (COSTS OFF) <literal>HASH_JOIN(f g)</literal> is actually a request for two logically separate behaviors, whereas <literal>JOIN_ORDER(f g)</literal> is a single request. When providing advice feedback, <literal>EXPLAIN</literal> shows - each logical request separtely, together with all the feedback applicable + each logical request separately, together with all the feedback applicable to that request type. </para> @@ -579,7 +583,7 @@ rhaas=# EXPLAIN (COSTS OFF) <listitem> <para> <literal>partially matched</literal> means that some but not all of the - specified advice targetes were observed during query planning. + specified advice targets were observed during query planning. </para> </listitem> @@ -587,7 +591,7 @@ rhaas=# EXPLAIN (COSTS OFF) <para> <literal>not matched</literal> means that none of the specified advice targets were observed during query planning. This may - be happen if the advice simply doesn't match the query, or it may + happen if the advice simply doesn't match the query, or it may occur if the relevant portion of the query was not planned, perhaps because it was gated by a condition that was simplified to constant false. </para> @@ -626,7 +630,7 @@ rhaas=# EXPLAIN (COSTS OFF) plan that can comply with the advice. It is important to note that the sanity checks performed by <literal>pg_plan_advice</literal> are fairly superficial and focused mostly on looking for logical inconsistencies in - the advice string; only the planner knows what will actually work. + the advice string; only the planner knows what actually works. </para> </listitem> @@ -651,18 +655,15 @@ rhaas=# EXPLAIN (COSTS OFF) <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 + limit, the oldest queries and corresponding advice will be discarded. + Then, you must separately enable advice collection. For the local collector, set the collection limit by configuring <literal>pg_plan_advice.local_collection_limit</literal> to a value greater than zero, and then enable advice collection by setting <literal>pg_plan_advice.local_collector = true</literal>. For the shared collector, the procedure is the same, except that the names of the settings are <literal>pg_plan_advice.shared_collection_limit</literal> and - <literal>pg_plan_advice.shared_collector</literal>. Note that the local - collector stores query texts and advice strings in backend-local memory, - and the shared collector does the same in dynamic shared memory, so - configuring large limits may result in considerable memory consumption. + <literal>pg_plan_advice.shared_collector</literal>. </para> <para> @@ -672,7 +673,7 @@ rhaas=# EXPLAIN (COSTS OFF) <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 + and release memory, you call <literal>pg_clear_collected_local_advice()</literal> or <literal>pg_clear_collected_shared_advice()</literal>. </para> @@ -684,8 +685,8 @@ rhaas=# EXPLAIN (COSTS OFF) 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>enable_query_id = on</literal>. Otherwise, the query ID may - always show as <literal>0</literal>. + <literal>enable_query_id = on</literal>. Otherwise, the query ID will + show as <literal>0</literal>. </para> </sect2> @@ -829,9 +830,9 @@ rhaas=# EXPLAIN (COSTS OFF) <varname>pg_plan_advice.always_store_advice_details</varname> allows <literal>EXPLAIN</literal> to show details related to plan advice even when prepared queries are used. The default value is - <literal>false</literal>. When planning a prepared query, we do not + <literal>false</literal>. When planning a prepared query, <literal>pg_plan_advice</literal> does not know whether <literal>EXPLAIN</literal> will be used, so by default, to - reduce overhead, we do not generate plan advice, and we do not generate + reduce overhead, it does not generate plan advice, and does not generate feedback on supplied advice. This means that if <literal>EXPLAIN EXECUTE</literal> is later used on the prepared query, it will not be able to show this information. Changing this setting to -- 2.43.0
