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.

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.

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 that 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 relations 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 relation 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 are included only when required. When a component is omitted, the preceding punctuation must also be omitted. For the first occurrence of a relation 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, pg_plan_advice 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.

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 top level). 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 [ ... ])

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 [ ... ] ) }

 Finalize Aggregate
   ->  Gather
         ->  Partial Aggregate
               ->  Parallel Seq Scan on outer_example
                     Filter: (something = (hashed SubPlan any_1).col1)
                     SubPlan any_1
                       ->  Seq Scan on inner_example
                             Filter: (something_else > 100)

 Aggregate
   ->  Seq Scan on outer_example
         Filter: (something = (hashed SubPlan any_1).col1)
         SubPlan any_1
           ->  Gather
                 -> Parallel Seq Scan on inner_example
                      Filter: (something_else > 100)

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 invalid 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 separately, together with all the feedback applicable to that request type.

Advice feedback can include any of the following:

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

It is currently not possible to control any aspect of the planner's behavior with respect to aggregation. This includes both whether aggregates are computed by sorting or hashing, and also whether strategies such as eager aggregation or partitionwise aggregation are used.

It also is currently not possible to control any aspect of the planner's behavior with respect to set operations such as UNION or INTERSECT.

As discussed above under How It Works, the use of plan advice can only affect which plan the planner chooses from among those it believes to be viable. It can never force the choice of a plan which the planner refused to consider in the first place.

Robert Haas <rhaas@postgresql.org>