Hint feature of AnalyticDB for PostgreSQL-AnalyticDB(AnalyticDB)-阿里云帮助中心

The pg_hint_plan plugin for the cloud-native data warehouse AnalyticDB for PostgreSQL provides a hint feature. Hints are comments that you can add to SQL statements to influence and tune execution plans, which can improve the performance of the statements.

Version limits

For minor engine versions V6.3.8.1 and later, the hint feature is enabled by default after you install the pg_hint_plan plugin.
For minor engine versions earlier than V6.3.8.1, you must upgrade to V6.3.8.1 or a later version before you can install the pg_hint_plan plugin.
For minor engine versions V6.3.8.9 and later, you must Submit a ticket to contact technical support to install or upgrade the plugin.
For more information about how to view and upgrade the minor engine version, see View the minor engine version and Upgrade the version.

Features

The optimizer for AnalyticDB for PostgreSQL evaluates the costs of various feasible execution operators for an SQL statement based on statistics instead of fixed rules. The optimizer then chooses the combination of operators with the lowest cost. Although the optimizer attempts to select the best execution plan, the resulting plan may not be optimal for the current scenario because of latent data correlations.

The pg_hint_plan plugin uses hints to force and tune the execution plans of SQL statements. You can also register tuned SQL statement templates and their corresponding hint rules. When the system encounters an SQL statement that matches a registered template, it automatically generates the tuned execution plan, which improves execution efficiency. A matching template has the same structure as the original SQL statement but can have different constant parameter values.

Enable the hint feature

You can run the following command to install the plugin and enable the hint feature:

CREATE EXTENSION pg_hint_plan;

Note

The Hint feature is available only in libraries that have the plugin installed.

Supported hints

Category	Format	Description
Set statement-level GUC parameters	`Set(GUC-param value)`	Sets a GUC parameter for the optimizer phase. Currently, GUC parameters take effect only in the optimizer phase, not in other phases such as Rewrite and Execute. To disable the ORCA optimizer at the statement level, use this hint with the `SET(optimizer off)` statement. To use the ORCA optimizer at the statement level, use this hint with the `SET(<ORCA_related_intervention_parameter> <parameter_value>)` statement.
Scan method hints	`SeqScan(table)`	Forces a sequential scan.
	`TidScan(table)`	Forces a TID scan.
	`IndexScan(table[ index...])`	Forces an index scan. You can specify an index.
	`IndexOnlyScan(table[ index...])`	Forces an index-only scan. You can specify an index.
	`BitmapScan(table[ index...])`	Forces a bitmap index scan.
	`NoSeqScan(table)`	Disables sequential scans.
	`NoTidScan(table)`	Disables TID scans.
	`NoIndexScan(table)`	Disables index scans.
	`NoIndexOnlyScan(table)`	Disables index-only scans.
	`NoBitmapScan(table)`	Disables bitmap index scans.
Join method hints Note Must be used with Join order hints.	`NestLoop(table table[ table...])`	Forces a nested loop join.
	`HashJoin(table table[ table...])`	Forces a hash join.
	`MergeJoin(table table[ table...])`	Forces a merge join.
	`NoNestLoop(table table[ table...])`	Disables nested loop joins.
	`NoHashJoin(table table[ table...])`	Disables hash joins.
	`NoMergeJoin(table table[ table...])`	Disables merge joins.
Join order hints	`Leading(table table[ table...])`	Forces a specific join order.
Join order hints	`Leading(<join pair>)`	Forces a specific join order and direction.
Row number correction hints	`Rows(table table[ table...] correction)`	Corrects the number of rows in the join result of the specified tables. Available correction methods are absolute value `#<n>`, addition `+ <n>`, subtraction `-<n>`, and multiplication `* <n>`. `<n>` is a number that the strtod function can read. Note ROWS modifies the total number of rows. The returned query plan shows the average number of rows for each node (total rows / number of nodes).

Note

Hints, except for those that set GUC parameters, take effect only for the Postgres query optimizer and not for the ORCA optimizer.
Hints related to the degree of parallelism are not supported.

Examples:

Set statement-level GUC parameters
GUC parameter settings for the optimizer phase take effect for both the ORCA optimizer and the Postgres query optimizer.
- To disable the ORCA optimizer:
```
/*+ SET(optimizer off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
  When disabled, the ORCA optimizer is not used.
- To enable the ORCA optimizer:
```
/*+ SET(optimizer on) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
  If you enable the ORCA optimizer, it is used by default. The database uses the ORCA optimizer in most scenarios. The ORCA optimizer is not used in some cases, such as for single-table queries or queries that involve many partitioned tables.
- To forcibly enable the ORCA optimizer:
```
/*+ SET(optimizer on) SET(rds_optimizer_options 0) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
  If you forcibly enable the ORCA optimizer, it is used in all scenarios. The database does not use the ORCA optimizer only if it fails to create a plan.
- To forcibly enable the ORCA optimizer and disable its hash join feature:
```
/*+ SET(optimizer on) SET(rds_optimizer_options 0) SET(optimizer_enable_hashjoin off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
Scan method hints
The following content applies only to the query optimizer. Before using the query optimizer, you must execute the following command to shut down the ORCA optimizer:
```
SET optimizer to off;
```
- To force an index scan on table t1:
```
/*+ Indexscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- To disable index scans on table t1:
```
/*+ NoIndexscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- To force a bitmap index scan on table t1 using t1_val:
```
/*+ Bitmapscan(t1 t1_val) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- To force an index-only scan on table t1:
```
/*+ Indexonlyscan(t1) */EXPLAIN SELECT t2.*, t1.val FROM t1 JOIN t2 ON t1.val = t2.val;
```
  Note
  Index-only scans can be used only when you scan index columns.
- To force a TID scan on table t1:
```
/*+ Tidscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val where t1.ctid = '(1,2)';
```
  Note
  TID scans can be used only when a TID condition exists in the table.

Join method and join order hints

The following content applies only to the query optimizer. Before you proceed, execute the following command to disable the ORCA optimizer:

SET optimizer to off;

To set t1 as the left table and the join type to MergeJoin:

/*+ Leading((t1 t2)) MergeJoin(t1 t2) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;

To set t1 as the left table and the join type to NestLoopJoin:

/*+ Leading((t1 t2)) NestLoop(t1 t2) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;

To set t1 as the left table and disable HashJoin:

/*+ Leading((t1 t2)) NoHashJoin(t1 t2) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;

To specify that t2 and t3 first perform a HashJoin and then perform a NestLoopJoin with t1:

/*+ Leading(((t2 t3) t1)) HashJoin(t2 t3) NestLoop(t2 t3 t1) */EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val = t2.val and t2.val = t3.val;

Row number correction hints
The following hints apply only to the Postgres query optimizer. Before you use them, you must run the following command to disable the ORCA optimizer:
```
SET optimizer to off;
```
- To increase the total number of rows from the join of t1 and t2 by 100 times:
```
/*+ Rows(t1 t2 *100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- To decrease the total number of rows from the join of t1 and t2 by 100 times:
```
/*+ Rows(t1 t2 *0.01) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- To increase the total number of rows from the join of t1 and t2 by 100:
```
/*+ Rows(t1 t2 +100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- To decrease the total number of rows from the join of t1 and t2 by 100:
```
/*+ Rows(t1 t2 -100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- To set the total number of rows from the join of t1 and t2 to 100:
```
/*+ Rows(t1 t2 #100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```

GUC parameters

Parameter	Default value	Description
pg_hint_plan.enable_hint	on	Specifies whether to use hints to influence the plan. Valid values: on: Enables hints to influence the plan. off: Disables hints from influencing the plan.
pg_hint_plan.enable_hint_table	off	Specifies whether to use the hint registration feature. Valid values: on: Enables the hint registration feature. off: Disables the hint registration feature.
pg_hint_plan.jumble_mode	off	Specifies whether to use OIDs to identify objects such as tables, functions, and operators in parameterized SQL statements. Valid values: on: Uses OIDs to distinguish objects. If an object is deleted and recreated with the same name, it is considered a different object. off: Uses the schema and object name to distinguish objects. Objects of the same type with the same name under the same schema are identified as the same object. Note Do not switch this parameter frequently. After you switch it, rules registered before the switch cannot be identified.
pg_hint_plan.parse_messages	info	Controls the log level for error messages in the Parse Hint phase. Valid values: error, warning, notice, info, log, and debug[1-5].
pg_hint_plan.message_level	log	Controls the log level for error messages in other hint phases. Valid values: error, warning, notice, info, log, and debug[1-5].

Register hints

If you want hints to be automatically applied to SQL statements that match a specific SQL template, or if you cannot add hints to an SQL statement, you can register the hint in the `hint_plan.hints` system table. After a hint is registered, a tuned execution plan is automatically generated based on the hint when you execute an SQL statement that matches the template.

The schema of the `hint_plan.hints` table is as follows:

Column	Type	Content
id	integer	The ID of the registered hint rule. The value increments by default.
norm_query_string	text	The SQL statement template. This is the SQL statement with parameters (Param) and constants (Const) removed.
application_name	text	The application identity string for the registered hint rule. This is used to isolate rules between multiple applications. The default value is `''`, which means no isolation. In the function examples in this topic, this parameter is set to `''`. The application_name column has a unique key constraint.
hints	text	The hint registered for the SQL statement template. The hints column has a unique key constraint.
query_hash	bigint	The hash value of the parameterized SQL statement template. This is the unique identifier for the normalized SQL. The query_hash column has a unique key constraint.
enable	boolean	Controls whether the hint rule is active. Only one hint rule can be used for the same SQL statement template.
prepare_param_strings	text	If the registered query is a PREPARE statement, this column records its parameters.

Note

You can directly query the `hint_plan.hints` table. However, you must not directly modify the table. To modify the table, you must use the corresponding functions.

The following section describes the Hint registration function.

SQL statement parameterization function

hint_plan.gp_hint_query_parameterize(<query>, <application_name>)

Parameter	Description
`query`	The SQL statement that contains the hint.
`application_name`	The application identity string for the registered hint rule. Leave this empty (`''`).

This function retrieves the parameter information for an SQL statement that contains a hint. The following information is returned:

Parameter	Description
`query_hash`	The hash value of the parameterized SQL statement template. This is the unique identifier for the normalized SQL.
`norm_query_string`	The SQL statement template.
`comment_hints`	The comments in the statement.
`first_matched_hint_in_table`	The comment in the hint_plan.hints table that matches the SQL statement template.
`prepare_param_strings`	The parameters extracted from the SQL statement.

Example:

SELECT * FROM hint_plan.gp_hint_query_parameterize('/*+ MergeJoin(t1 t2) Leading((t1 t2)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 20;');

Sample response:

-[ RECORD 1 ]---------------+--------------------------------------------------------------------------
query_hash                  | -4733464863014584191
norm_query_string           | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;
comment_hints               | MergeJoin(t1 t2) Leading((t1 t2))
first_matched_hint_in_table | HashJoin(t1 t2) Leading((t1 t2))
prepare_param_strings       | {}

Hint registration function

hint_plan.insert_hint_table(<query>, <application_name>)

Parameter	Description
`query`	The SQL statement that contains the hint.
`application_name`	The application identity string for the registered hint rule. Leave this empty (`''`).

You can use this function to register different hint rules for the same SQL statement template. If you try to insert a hint rule that has the same SQL statement template, hint, and application identity string as an existing rule, a duplicate rule is not created. Instead, the new hint rule is enabled, and all other hint rules for the same template are disabled.

Example:

SELECT hint_plan.insert_hint_table('/*+ MergeJoin(t1 t2) Leading((t1 t2)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;');

Sample response:

                                                                 insert_hint_table
---------------------------------------------------------------------------------------------------------------------------------------------------
 (1,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","MergeJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,t,{})
(1 row)

Hint modification function

hint_plan.upsert_hint_table(<query>, <application_name>)

Parameter	Description
`query`	The SQL statement that contains the hint.
`application_name`	The application identity string for the registered hint rule. Leave this empty (`''`).

If an active hint exists for the parameter template of the SQL statement, the original hint in the `hint_plan.hints` table is replaced with the hint that is included in the query. If no active hint is available, a new hint rule is registered.

Example:

Query the existing hint rules in the `hint_plan.hints` table:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |                             norm_query_string                             | application_name |               hints                |      query_hash      | enable | prepare_param_strings
----+---------------------------------------------------------------------------+------------------+------------------------------------+----------------------+--------+-----------------------
  1 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; |                  | MergeJoin(t1 t2) Leading((t1 t2))  | -4733464863014584191 | f      | {}
  2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; |                  | Nestloop(t1 t2) Leading((t1 t2))   | -4733464863014584191 | t      | {}
(2 rows)

Run the hint modification function:

SELECT hint_plan.upsert_hint_table('/*+ HashJoin(t1 t2) Leading((t1 t2)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;');

The following information is returned:

                                                                upsert_hint_table
--------------------------------------------------------------------------------------------------------------------------------------------------
 (2,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","HashJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,t,{})
(1 row)

Query the `hint_plan.hints` table after you modify the hint rule:

SELECT * FROM hint_plan.hints;

The hint for the same SQL statement template has changed from Nestloop(t1 t2) Leading((t1 t2)) to HashJoin(t1 t2) Leading((t1 t2)). The following information is returned:

 id |                             norm_query_string                             | application_name |               hints                |      query_hash      | enable | prepare_param_strings
----+---------------------------------------------------------------------------+------------------+------------------------------------+----------------------+--------+-----------------------
  1 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; |                  | MergeJoin(t1 t2) Leading((t1 t2))  | -4733464863014584191 | f      | {}
  2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; |                  | HashJoin(t1 t2) Leading((t1 t2))   | -4733464863014584191 | t      | {}
(2 rows)

Hint deletion functions

To delete the hint rule with a specific ID:
```
hint_plan.delete_hint_table(<id>)
```
To delete the hint rule that matches a specific SQL statement, hint, and application identity string:
```
hint_plan.delete_hint_table(<query>, <hint>, <application_name>)
```
To delete all hint rules that match a specific SQL statement and application identity string:
```
hint_plan.delete_all_hint_table(<query>, <application_name>)
```

Parameter	Description
`id`	The ID in the `hint_plan.hints` table.
`query`	The SQL statement. It does not need to contain a hint.
`hint`	The hint.
`application_name`	The application identity string for the registered hint rule. Leave this empty (`''`).

Example:

Query the original information in the `hint_plan.hints` table:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |                             norm_query_string                             | application_name |                      hints                      |      query_hash      | enable | prepare_param_strings
----+---------------------------------------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+-----------------------
  1 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; |                  | MergeJoin(t1 t2) Leading((t1 t2))               | -4733464863014584191 | f      | {}
  2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; |                  | HashJoin(t1 t2) Leading((t1 t2))                | -4733464863014584191 | t      | {}
  3 | select * from t1 join t2 on t1.val = t2.val;                              |                  | set(optimizer on) set(rds_optimizer_options 0)  | -2169095602568752481 | f      | {}
  4 | select * from t1 join t2 on t1.val = t2.val;                              |                  | set(optimizer off)                              | -2169095602568752481 | t      | {}
(4 rows)

Delete a hint rule by ID:

SELECT hint_plan.delete_hint_table(1);

The following information is returned:

WARNING:  "max_appendonly_tables": setting is deprecated, and may be removed in a future release.
                                                                 delete_hint_table
---------------------------------------------------------------------------------------------------------------------------------------------------
 (1,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","MergeJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,f,{})
(1 row)

Query the `hint_plan.hints` table after deletion:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |                             norm_query_string                             | application_name |                      hints                      |      query_hash      | enable | prepare_param_strings
----+---------------------------------------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+-----------------------
  2 | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; |                  | HashJoin(t1 t2) Leading((t1 t2))                | -4733464863014584191 | t      | {}
  3 | select * from t1 join t2 on t1.val = t2.val;                              |                  | set(optimizer on) set(rds_optimizer_options 0)  | -2169095602568752481 | f      | {}
  4 | select * from t1 join t2 on t1.val = t2.val;                              |                  | set(optimizer off)                              | -2169095602568752481 | t      | {}
(3 rows)

Delete a hint rule by SQL statement, hint, and application identity string:

SELECT hint_plan.delete_hint_table('SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 5 and t2.val > 1;', 'HashJoin(t1 t2) Leading((t1 t2))');

The following information is returned:

                                                                delete_hint_table
--------------------------------------------------------------------------------------------------------------------------------------------------
 (2,"SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;","","HashJoin(t1 t2) Leading((t1 t2)) ",-4733464863014584191,t,{})
(1 row)

Query the `hint_plan.hints` table after deletion:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |              norm_query_string               | application_name |                      hints                      |      query_hash      | enable | prepare_param_strings
----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+-----------------------
  3 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer on) set(rds_optimizer_options 0)  | -2169095602568752481 | f      | {}
  4 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer off)                              | -2169095602568752481 | t      | {}
(2 rows)

Delete hint rules by SQL statement and application identity string:

SELECT hint_plan.delete_all_hint_table('select * from t1 join t2 on t1.val = t2.val;');

The following information is returned:

                                                       delete_all_hint_table
-----------------------------------------------------------------------------------------------------------------------------------
 (3,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer on) set(rds_optimizer_options 0) ",-2169095602568752481,f,{})
 (4,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer off) ",-2169095602568752481,t,{})
(2 rows)

Query the `hint_plan.hints` table after deletion:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id | norm_query_string | application_name | hints | query_hash | enable | prepare_param_strings
----+-------------------+------------------+-------+------------+--------+-----------------------
(0 rows)

Hint enabling and disabling functions

To enable the hint rule with a specific ID. After you enable this rule, other hint rules for the same SQL statement template become inactive:
```
hint_plan.enable_hint_table(<id>)
```
To enable the hint rule that matches a specific SQL statement, hint, and application identity string. After you enable this rule, other hint rules for the same SQL statement template become inactive:
```
hint_plan.enable_hint_table(<query>, <hint>, <application_name>)
```
To disable the hint rule with a specific ID:
```
hint_plan.disable_hint_table(<id>)
```
To disable the hint rule that matches a specific SQL statement, hint, and application identity string:
```
hint_plan.disable_hint_table(<query>, <hint>, <application_name>)
```
To disable all hint rules that match a specific SQL statement and application identity string:
```
hint_plan.disable_all_hint_table(<query>, <application_name>)
```

Parameter	Description
`id`	The ID in the `hint_plan.hints` table.
`query`	The SQL statement. It does not need to contain a hint.
`hint`	The hint rule.
`application_name`	The application identity string for the registered hint rule. Leave this empty (`''`).

Example:

Query the original information in the `hint_plan.hints` table:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |              norm_query_string               | application_name |                      hints                      |      query_hash      | enable | prepare_param_strings
----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+-----------------------
  5 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer off)                              | -2169095602568752481 | f      | {}
  6 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer on) set(rds_optimizer_options 0)  | -2169095602568752481 | t      | {}
(2 rows)

Disable a hint rule by ID:

SELECT hint_plan.disable_hint_table(6);

The following information is returned:

                                                        disable_hint_table
-----------------------------------------------------------------------------------------------------------------------------------
 (6,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer on) set(rds_optimizer_options 0) ",-2169095602568752481,f,{})
(1 row)

Query the `hint_plan.hints` table after you change the status:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |              norm_query_string               | application_name |                      hints                      |      query_hash      | enable | prepare_param_strings
----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+-----------------------
  5 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer off)                              | -2169095602568752481 | f      | {}
  6 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer on) set(rds_optimizer_options 0)  | -2169095602568752481 | f      | {}
(2 rows)

Enable a hint rule by ID:

SELECT hint_plan.enable_hint_table(5);

The following information is returned:

                                           enable_hint_table
-------------------------------------------------------------------------------------------------------
 (5,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer off) ",-2169095602568752481,t,{})
(1 row)

Query the `hint_plan.hints` table after you change the status:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |              norm_query_string               | application_name |                      hints                      |      query_hash      | enable | prepare_param_strings
----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+-----------------------
  6 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer on) set(rds_optimizer_options 0)  | -2169095602568752481 | f      | {}
  5 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer off)                              | -2169095602568752481 | t      | {}
(2 rows)

Enable a hint rule by SQL statement and application identity string:

SELECT hint_plan.enable_hint_table('select * from t1 join t2 on t1.val = t2.val;', 'set(optimizer off)');

The following information is returned:

                                           enable_hint_table
-------------------------------------------------------------------------------------------------------
 (5,"select * from t1 join t2 on t1.val = t2.val;","","set(optimizer off) ",-2169095602568752481,t,{})
(1 row)

Query the `hint_plan.hints` table after you change the status:

SELECT * FROM hint_plan.hints;

The following information is returned:

 id |              norm_query_string               | application_name |                      hints                      |      query_hash      | enable | prepare_param_strings
----+----------------------------------------------+------------------+-------------------------------------------------+----------------------+--------+-----------------------
  6 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer on) set(rds_optimizer_options 0)  | -2169095602568752481 | f      | {}
  5 | select * from t1 join t2 on t1.val = t2.val; |                  | set(optimizer off)                              | -2169095602568752481 | t      | {}
(2 rows)

Uninstall the pg_hint_plan plugin

If you no longer need the hint feature, you can run the following statement to uninstall the plugin:

DROP EXTENSION pg_hint_plan;

References

Influence an execution plan using hints