Product Documentation

Appearance

All platforms

Plan Hinting: Overview, Setup, and General Information ​

Overview ​

Plan Hinting is a powerful feature that allows users to influence the query planner by enforcing specific plan shapes using various hints. These hints can guide the query execution strategy by specifying join methods, ordering joins, modifying expected row counts or changing certain configuration parameter values for the query duration.

Plan Hinting is especially useful for complex queries where the default planner may not generate the most optimal plan, giving users more control over performance.

Enabling and Disabling Plan Hinting ​

Two configuration parameters control whether Plan Hinting is enabled or disabled:

  1. enable_query_hint_injection: Enables or disables the Plan Hinting feature.
  2. enable_wlm_query_hint_injection: Allows hints to be injected via Workload Manager (WLM) rules.

By default, both configuration parameters are set to off and need to be set to on to activate the feature.

Example : ​

To set these properties for only the current session, one can run:

sql
SET enable_query_hint_injection = on;
SET enable_wlm_query_hint_injection = on;

Note: These two parameters can not be directly injected in a query using SET hints.

Referring to Tables and Joins ​

When referring to tables or joins in a hint, you can use:

  • Table names (qualified or unqualified).
  • Table aliases (if used in the query).
  • Subquery aliases or CTE names to target subqueries or CTEs.

Case Sensitivity ​

By default, table names are not case-sensitive. To enforce case sensitivity, place quotes around the table names.

sql
SETTING (ROWS(orders, c # 42)) SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

Here, we're telling the planner the output of join in between orders and c is estimated to contain 42 rows.

WLM Hint Injection ​

Plan Hinting can also be applied via Workload Manager (WLM) rules of type hinting. This allows hints to be injected into the query plan without modifying the original query text by using the syntax :

javascript
w.addPlanHint("hint");

where hint must be one of the available hints :

However, if manual hints are embedded in the query, WLM-injected hints will not be applied.

Note: Hinting rules can only be created via the SQL commandCREATE WLM RULE

Example of creation of a hinting WLM Rule: ​

sql
DROP WLM RULE IF EXISTS "hinting_rule";
CREATE WLM RULE "hinting_rule" (
    TYPE hinting, RULE_ORDER 100, SUPERUSER true, ENABLED true,
    JAVASCRIPT
    $$
        w.addPlanHint("LEADING ( ( (A, C), B))");
        w.addPlanHint("HASHJOIN (A, C)");
        w.addPlanHint("NESTLOOP (A, C, B)");
    $$
);
ALTER WLM PROFILE "default" ACTIVATE;
SET enable_wlm_query_hint_injection = true;

To track the hints that were added to a query during the hinting stage, you can check sys.query_rule_event:

sql
select * from sys.query_rule_event where rule_type='hinting';

Viewing Applied Hints ​

To see which hints were applied to a query, you can use the EXPLAIN(hint) command. To view both applied and non-applied hints, use the verbose option in conjunction the the hint option.

Example: ​

sql
EXPLAIN (hint) SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
EXPLAIN (verbose, hint) SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

Understanding why a hint is not applied ​

Most errors related to query hints are silent to prevent query execution failures.

As mentioned above, you can usually determine which hints were applied or encountered errors by using the EXPLAIN(hint, verbose) statement.

In the case of an error, it will be displayed next to the hint in the output:

sql
yellowbrick_test=# EXPLAIN (stable, hint, verbose) SETTING ("inlist_threshol" = 2) SELECT * FROM Foo WHERE i IN (1, 2, 3, 4);
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 workers   node
     all   SELECT
             (foo.i)
             distribute on (foo.i)
     all   SCAN foo
             (foo.i)
             distribute on (foo.i)
             ((foo.i = $3) OR ((foo.i = $2) OR ((foo.i = $0) OR (foo.i = $1))))

 -------------------------------------- Query Hints --------------------------------------

 Raised error hints:
         inlist_threshol = 2 -> unrecognized configuration parameter "inlist_threshol"

The one exception to that are typos and syntax errors:

  • When query hints are manually injected, syntax errors will prevent the query from being parsed and will result in an immediate error:
sql
yellowbrick_test=# EXPLAIN (confidence, hint) SETTING ( LEADING ( ( (A, C)  B) )  ) SELECT * from A INNER JOIN B USING (c) INNER JOIN A as C using(c);
ERROR:  syntax error at or near "B"
LINE 1: ... (confidence, hint) SETTING ( LEADING ( ( (A, C)  B) )  ) SE...
  • For WLM (Workload Management) injection, the system does not terminate queries that could otherwise run. By default, WLM hint syntax errors are logged and ignored. You can change this behavior and adjust the level of messages received by modifying the configuration parameter query_hint_report_level.
sql
yellowbrick_test=# set query_hint_report_level = error;
SET
yellowbrick_test=# EXPLAIN (verbose,  hint) SELECT * from A INNER JOIN B USING (c) INNER JOIN A as C using(c);
ERROR:  Skipping WLM hint "LEADING ( ( (A, C) , B )": syntax error at end of input

Important Considerations When Using Plan Hinting ​

While Plan Hinting provides powerful tools to control the execution of queries, it’s important to be mindful of a few key considerations to ensure the best performance and maintainability.

1. Balance Manual Control with Automatic Optimizations ​

The query planner is built to make intelligent decisions based on your data and workload. While forcing specific strategies can offer benefits, in some cases, overriding the planner may result in suboptimal performance. We recommend applying hints only when needed and in areas where you’re confident that the forced behavior is better suited to your query.

2. Consider Planning Time ​

For complex queries with many joins, forcing specific join methods or orders can lead to increased planning time. While this may be a necessary trade-off for improving query execution, it's important to ensure the additional planning time does not outweigh the benefits.

3. Verify Hint Application ​

Not all hints may be applied, depending on the query and the context. We recommend using EXPLAIN(hint) to verify that your hints are recognized and applied as expected. In some cases, the planner may ignore hints that cannot be followed, and EXPLAIN(verbose, hint) will help you identify these scenarios.

4. Monitor Changing Workloads ​

As your data and query patterns evolve, previously applied hints may no longer provide the best results. Periodic review of hint usage ensures that your queries remain optimal over time and minimizes the need for future troubleshooting.