In order to use this module, the pg_plan_advice module must be loaded. You can do this on a system-wide basis by adding pg_plan_advice to shared_preload_libraries and restarting the server, or by adding it to session_preload_libraries and starting a new session, or by loading it into an individual session using the LOAD command. If you wish to use the collector interface, you must also the pg_plan_advice extension in the database where you wish to use the collector. Use the command CREATE EXTENSION pg_plan_advice to do this. If you do not wish to use the collector interface, this step is not required.

Once the pg_plan_advice module is loaded, EXPLAIN will support a PLAN_ADVICE option. You can use this option to see a plan advice string for the chosen plan. For example:

EXPLAIN (COSTS OFF, PLAN_ADVICE)
        SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
             QUERY PLAN
------------------------------------
 Hash Join
   Hash Cond: (f.dim_id = d.id)
   ->  Seq Scan on join_fact f
   ->  Hash
         ->  Seq Scan on join_dim d
 Generated Plan Advice:
   JOIN_ORDER(f d)
   HASH_JOIN(d)
   SEQ_SCAN(f d)
   NO_GATHER(f d)

In this example, the user has not specified any advice; instead, the planner has been permitted to make whatever decisions it thinks best, and those decisions are memorialized in the form of an advice string. JOIN_ORDER(f d) means that f should be the driving table, and the first table to which it should be joined is d. HASH_JOIN(d) means that d should appear on the inner side of a hash join. SEQ_SCAN(f d) means that both f and d should be accessed via a sequential scan. NO_GATHER(f d) means that neither f nor d should appear beneath a Gather or Gather Merge node. For more details on the plan advice mini-language, see the information on advice targets and advice tags, below.

If you want to see the advice strings for a large number of queries, or an entire workload, running EXPLAIN (PLAN_ADVICE) for each one may not be convenient. In such situations, it can be more convenient to use an advice collector.

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 pg_plan_advice.advice to the advice string you've chosen. This can be an advice string that was generated by the system, 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 those elements that you wish to enforce. In the example above, pg_plan_advice emits advice for the join order, the join method, the scan method, and the use of parallelism, but you might only want to control the join order:

SET pg_plan_advice.advice = 'JOIN_ORDER(f d)';
EXPLAIN (COSTS OFF)
        SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
             QUERY PLAN
------------------------------------
 Hash Join
   Hash Cond: (f.dim_id = d.id)
   ->  Seq Scan on join_fact f
   ->  Hash
         ->  Seq Scan on join_dim d
 Supplied Plan Advice:
   JOIN_ORDER(f d) /* matched */

Since the PLAN_ADVICE option to EXPLAIN was not specified, no advice string is generated for the plan. However, the supplied plan advice is still shown so that anyone looking at the EXPLAIN output knows that the chosen plan was influenced by plan advice. If information about supplied plan advice is not desired, it can be suppressed by configuring pg_plan_advice.always_explain_supplied_advice = false. For each piece of supplied advice, the output shows advice feedback indicating whether or not the advice was successfully applied to the query. In this case, the feedback says /* matched */, which means that f and d were found in the query and that the resulting query plan conforms to the specified advice.

Plan advice is written imperatively; that is, it specifies what should be done. However, at an implementation level, pg_plan_advice 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 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 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 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 SELECT * FROM some_table, the query planner will decide that the use of an index is worthless here before it performs any costing calculations. You cannot force it to use an index for this query even if you set enable_seqscan = false, and you can't force it to use an index using plan advice, either.

Specifying plan advice should never cause planner failure. However, if you specify plan advice that asks for something impossible, you may get a plan where some plan nodes are flagged as Disabled: true in the EXPLAIN output. In some cases, such plans will be 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:

SET pg_plan_advice.advice = 'JOIN_ORDER(x f d)';
EXPLAIN (COSTS OFF)
        SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
                     QUERY PLAN
----------------------------------------------------
 Nested Loop
   Disabled: true
   ->  Seq Scan on join_fact f
   ->  Index Scan using join_dim_pkey on join_dim d
         Index Cond: (id = f.dim_id)
 Supplied Plan Advice:
   JOIN_ORDER(x f d) /* partially matched */

Because neither f nor d is the first table in the JOIN_ORDER() specification, the planner disables all direct joins between the two of them, thinking that the join to x should happen first. Since planning isn't allowed to fail, a disabled plan between the two tables is eventually selected anyway, but here it's a Nested Loop rather than the Hash Join that was chosen in the above example where no advice was specified. There are several different ways that this kind of thing can happen; when it does, the resulting plan is generally worse than if no advice had been specified at all. Therefore, it is a good idea to validate that the advice you specify applies to the query to which it is applied and that the results are as expected.

An advice target uniquely identifies a particular instance of a particular table involved in a particular query. In simple 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. (foo JOIN bar ON foo.a = bar.a) x JOIN foo ON x.b = foo.b. 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.

Because of this, the general syntax for a relation identifier is:

alias_name#occurrence_number/partition_schema.partition_name@plan_name

All components except for the alias_name are optional and included only when required. When a component is omitted, the associated 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 #1, 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.

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 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 obvious. For example, when two queries are joined using an operation such as UNION or INTERSECT, no name for the subqueries is present in the SQL syntax; instead, a system-generated name is assigned to each branch. The easiest way to discover the proper advice targets is to use EXPLAIN (PLAN_ADVICE) and examine the generated advice.

An advice tag specifies a particular behavior that should be enforced for some portion of the query, such as a particular join order or join method. All advice tags take advice targets as arguments, and many allow lists of advice targets, which in some cases can be nested multiple levels deep. Several different classes of advice targets exist, each controlling a different aspect of query planning.

SEQ_SCAN(target [ ... ])
TID_SCAN(target [ ... ])
INDEX_SCAN(target index_name [ ... ])
INDEX_ONLY_SCAN(target index_name [ ... ])
FOREIGN_SCAN((target [ ... ]) [ ... ])
BITMAP_HEAP_SCAN(target bitmap_target [ ... ])

JOIN_ORDER(join_order_item [ ... ])

where join_order_item is:

advice_target |
( join_order_item [ ... ] ) |
{ join_order_item [ ... ] }

Join
  ->  Join
        -> Scan on a
        -> Join
             -> Scan on b
             -> Scan on c
  ->  Scan on d

join_method_name(join_method_item [ ... ])

where join_method_name is:

{ MERGE_JOIN_MATERIALIZE | MERGE_JOIN_PLAIN | NESTED_LOOP_MATERIALIZE | NESTED_LOOP_PLAIN | HASH_JOIN }

and join_method_item is:

advice_target |
( advice_target [ ... ] ) }

PARTITIONWISE(partitionwise_item [ ... ])

where partitionwise_item is:

advice_target |
( advice_target [ ... ] ) }

SEMIJOIN_UNIQUE(sj_unique_item [ ... ])
SEMIJOIN_NON_UNIQUE(sj_unique_item [ ... ])

where sj_unique_item is:

advice_target |
( advice_target [ ... ] ) }

GATHER(gather_item [ ... ])
GATHER_MERGE(gather_item [ ... ])
NO_GATHER(advice_target [ ... ])

where gather_item is:

advice_target |
( advice_target [ ... ] ) }

EXPLAIN provides feedback on whether supplied advice was successfully applied to the query in the form of a comment on each piece of supplied advice. For example:

SET pg_plan_advice.advice = 'hash_join(f g) join_order(f g) index_scan(f no_such_index)';
SET
rhaas=# EXPLAIN (COSTS OFF)                                                     SELECT * FROM jo_fact f
        LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
        LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
        WHERE val1 = 1 AND val2 = 1;
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Join
   Hash Cond: ((d1.id = f.dim1_id) AND (d2.id = f.dim2_id))
   ->  Nested Loop
         ->  Seq Scan on jo_dim2 d2
               Filter: (val2 = 1)
         ->  Materialize
               ->  Seq Scan on jo_dim1 d1
                     Filter: (val1 = 1)
   ->  Hash
         ->  Seq Scan on jo_fact f
 Supplied Plan Advice:
   INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */
   HASH_JOIN(f) /* matched */
   HASH_JOIN(g) /* not matched */
   JOIN_ORDER(f g) /* partially matched */

For this query, f is a valid advice target, but g is not. Therefore, the request to place f on the inner side of a hash join is listed as matched, but the request to place g on the inner side of a hash join is listed as not matched. The JOIN_ORDER advice tag involves one valid target and one invald target, and so is listed as partially matched. Note that HASH_JOIN(f g) is actually a request for two logically separate behaviors, whereas JOIN_ORDER(f g) is a single request. When providing advice feedback, EXPLAIN shows each logical request separtely, together with all the feedback applicable to that request type.

Advice feedback can include any of the folllowing:

All advice should be marked as exactly one of matched, partially matched, or not matched.

pg_plan_advice can be configured to automatically generate advice every time a query is planned and store the query and the generated advice string either in local or shared memory.

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 pg_plan_advice.local_collection_limit to a value greater than zero, and then enable advice collection by setting pg_plan_advice.local_collector = true. For the shared collector, the procedure is the same, except that the names of the settings are pg_plan_advice.shared_collection_limit and pg_plan_advice.shared_collector. 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.

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 SELECT * FROM pg_get_collected_local_advice() or SELECT * FROM pg_get_collected_shared_advice() corresponds to the collector you enabled. To discard the collected advice and release memory, you can call pg_clear_collected_local_advice() or pg_clear_collected_shared_advice().

In addition to the query texts an 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 enable_query_id = on. Otherwise, the query ID may always show as 0.

Note that these functions will only be available if the pg_plan_advice extension has been installed in the current database, which is not mandatory, since much of the functionality of this module can be used without installing the extension.

Robert Haas <rhaas@postgresql.org>