Hint feature of AnalyticDB for PostgreSQL - AnalyticDB for PostgreSQL

The pg_hint_plan extension of AnalyticDB for PostgreSQL provides the hint feature. This feature allows you to modify and optimize execution plans and therefore improves SQL execution capabilities.

Limits

For AnalyticDB for PostgreSQL instances that run V6.3.8.1 or later, the hint feature is enabled after you install the pg_hint_plan extension.
Before you install the pg_hint_plan extension, we recommend that you update the minor engine version of your instance to V6.3.8.1 or later.
To install or upgrade extensions on an instance that runs V6.3.8.9 or later, Submit a ticket.
For more information about how to view and update the minor version of an instance, see View the minor engine version and Update the minor engine version.

Overview

AnalyticDB for PostgreSQL uses a cost-based optimizer that utilizes data statistics instead of static rules. The optimizer estimates the cost of each possible execution plan for an SQL statement and chooses the minimum-cost plan for execution. Although the optimizer does its best to select the optimal execution plan, the final execution plan may still not be the most suited for your scenario because it cannot predict possible correlations between data.

The pg_hint_plan extension can use hints to modify and optimize SQL execution plans and register optimized SQL patterns and hint rules. This way, optimized execution plans can be automatically generated when SQL statements that use the same registered SQL pattern are executed, which improves the execution efficiency.

Enable the hint feature

Execute the following statement to install the pg_hint_plan extension to enable the hint feature:

CREATE EXTENSION pg_hint_plan;

Note

The hint feature can be used only for databases that have the pg_hint_plan extension installed.

Supported hints

Category	Format	Description
Hints for Grand Unified Configuration (GUC) parameter settings	`Set(GUC-param value)`	Sets GUC parameters when the optimizer is running. GUC parameters take effect only when the optimizer is running and not in other phases such as the rewrite and execute phases. To disable the ORCA optimizer for a statement, we recommend that you add `SET(optimizer off)` to the statement. To enable the ORCA optimizer for a statement, we recommend that you add `SET(<ORCA parameter><value>)` to the statement.
Hints for scan methods	`SeqScan(table)`	Forces sequential scans on the table.
	`TidScan(table)`	Forces tuple identifier (TID) scans on the table.
	`IndexScan(table[ index...])`	Forces index scans on the table. You can specify an index.
	`IndexOnlyScan(table[ index...])`	Forces index-only scans on the table. You can specify an index.
	`BitmapScan(table[ index...])`	Forces bitmap index scans on the table.
	`NoSeqScan(table)`	Forbids sequential scans on the table.
	`NoTidScan(table)`	Forbids TID scans on the table.
	`NoIndexScan(table)`	Forbids index scans on the table.
	`NoIndexOnlyScan(table)`	Forbids index-only scans on the table.
	`NoBitmapScan(table)`	Forbids bitmap index scans on the table.
Hints for join methods Note The hints for join methods must be used together with the hints for join order.	`NestLoop(table table[ table...])`	Forces nested loops for joins that consist of the specified tables.
	`HashJoin(table table[ table...])`	Forces hash joins for joins that consist of the specified tables.
	`MergeJoin(table table[ table...])`	Forces merge joins for joins that consist of the specified tables.
	`NoNestLoop(table table[ table...])`	Forbids nested loops for joins that consist of the specified tables.
	`NoHashJoin(table table[ table...])`	Forbids hash joins for joins that consist of the specified tables.
	`NoMergeJoin(table table[ table...])`	Forbids merge joins for joins that consist of the specified tables.
Hints for join order	`Leading(table table[ table...])`	Forces join order as specified.
Hints for join order	`Leading(<join pair>)`	Forces join order and directions as specified.
Hints for row number correction	`Rows(table table[ table...] correction)`	Corrects the row number of a result of the joins that consist of the specified tables. The available correction methods are absolute value `＃<n>`, addition `+ <n>`, subtraction `-<n>`, and multiplication `* <n>`. `<n>` is a string that strtod() can read. Note The hint Rows corrects the total number of rows, whereas the query result shows the average number of rows per node (total number of rows/number of nodes).

Note

Hints other than the hints for GUC parameter settings take effect only on the PostgreSQL query optimizer, not on the ORCA optimizer.
The hints cannot be used to modify the degree of parallelism (DOP).

Examples:

Hints for GUC parameter settings
GUC parameter settings made when the optimizer is running take effect on both the ORCA and query optimizers.
- Disable the ORCA optimizer.
```
/*+ SET(optimizer off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
  After the ORCA optimizer is disabled, it is not used.
- Enable the ORCA optimizer.
```
/*+ SET(optimizer on) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
  After the ORCA optimizer is enabled, it is used. The ORCA optimizer is used in most cases and not used only in specific scenarios such as queries on a single table or excessive partitioned tables.
- Forcefully enable the ORCA optimizer.
```
/*+ SET(optimizer on) SET(rds_optimizer_options 0) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
  After the ORCA optimizer is forcefully enabled, it is always used. The ORCA optimizer is not used only when it cannot create plans.
- Forcefully enable the ORCA optimizer and disable HashJoin of the ORCA optimizer.
```
/*+ SET(optimizer on) SET(rds_optimizer_options 0) SET(optimizer_enable_hashjoin off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
Hints for scan methods
Hints for scan methods are suitable only for the query optimizer. You must execute the following statement to disable the ORCA optimizer:
```
SET optimizer to off;
```
- Force index scans on table t1.
```
/*+ Indexscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- Forbid index scans on table t1.
```
/*+ NoIndexscan(t1) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- Force bitmap index scans on table t1 by using the t1_val bitmap index.
```
/*+ Bitmapscan(t1 t1_val) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- Force index-only scans 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 on index-only columns.
- Force TID scans 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 on tables that contain TID conditions.

Hints for join methods and join order

Hints for scan methods are suitable only for the query optimizer. You must execute the following statement to disable the ORCA optimizer:

SET optimizer to off;

Force merge joins when table t1 is the left table.

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

Force nested loop joins when table t1 is the left table.

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

Forbid hash joins when table t1 is the left table.

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

Force hash joins for t2 and t3 and then force nest loop joins 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;

Hints for row number correction
Hints for scan methods are suitable only for the query optimizer. You must execute the following statement to disable the ORCA optimizer:
```
SET optimizer to off;
```
- Increase the total number of rows by 100 times in the table obtained by joining t1 and t2.
```
/*+ Rows(t1 t2 *100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- Decrease the total number of rows by 100 times in the table obtained by joining t1 and t2.
```
/*+ Rows(t1 t2 *0.01) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- Increase the total number of rows by 100 in the table obtained by joining t1 and t2.
```
/*+ Rows(t1 t2 +100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- Subtract 100 from the total number of rows in the table obtained by joining t1 and t2.
```
/*+ Rows(t1 t2 -100) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
```
- Correct the total number of rows to 100 in the table obtained by joining t1 and t2.
```
/*+ 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 enable the hint feature to modify execution plans. Valid values: on off
pg_hint_plan.enable_hint_table	off	Specifies whether to enable the hint registration feature. Valid values: on off
pg_hint_plan.jumble_mode	off	Specifies whether to use object identifiers (OIDs) to identify objects in parameterized SQL statements, such as tables, functions, and operators. Valid values: on: uses OIDs to identify objects. After an object is deleted, another object created by using the same name as the deleted object is considered a different object. off: uses schemas and object names to identify objects. Objects with the same name in the same schema are considered the same objects. Note We recommend that you do not change this parameter frequently. If you change this parameter, previous registered hints cannot be used.
pg_hint_plan.parse_messages	info	The log level of the hint parse error. Valid values: error, warning, notice, info, log, and debug[1-5].
pg_hint_plan.message_level	log	The log level of errors in phases other than hint parse. 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 use the same SQL pattern or if hints cannot be added to SQL statements, you can register the hints by adding them to the hint_plan.hints system table. After hints are registered, hint-optimized execution plans are automatically generated when SQL statements that use the same SQL pattern are executed.

The following table describes the columns in the hint_plan.hints table.

Column	Type	Description
id	integer	The unique number that identifies a hint. This column is filled in automatically by sequence.
norm_query_string	text	The SQL pattern to which the hint applies. SQL patterns are SQL statements that do not contain parameters or constants.
application_name	text	The application registered with the hint. The default value is an empty string (`''`), which indicates that the hint applies to all applications. In the following examples, the value of this column is an empty string (`''`). The application_name column has the UNIQUE constraint.
hints	text	The hint to be registered. The hints column has the UNIQUE constraint.
query_hash	bigint	The hash value of the parameterized SQL pattern, which is the unique identifier of standard SQL statements. The query_hash column has the UNIQUE constraint.
enable	boolean	Specifies whether to enable the hint. You can apply only a single hint to an SQL pattern.
prepare_param_strings	text	The parameters recorded if the PREPARE statement is used.

Note

You can query the hint_plan.hints table, but we recommend that you do not modify it directly. We recommend that you modify the table by means of modifying functions.

The following section describes the functions used for hint registration.

Function used to obtain the parameters of SQL statements

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

Parameter	Description
`query`	The SQL statement that contains the hint.
`application_name`	The application registered with the hint. The value is an empty string (`''`).

This function is used to obtain parameters of the SQL statement that contains the hint. The following table describes the parameters that can be obtained.

Parameter	Description
`query_hash`	The hash value of the parameterized SQL pattern, which is the unique identifier of standard SQL statements.
`norm_query_string`	The SQL pattern.
`comment_hints`	The hint.
`first_matched_hint_in_table`	The hints in the hint_plan.hints table that match the SQL pattern.
`prepare_param_strings`	The parameters in 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;');

The following information is returned:

-[ 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       | {}

Function used to register hints

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

Parameter	Description
`query`	The SQL statement that contains the hint.
`application_name`	The application registered with the hint. The value is an empty string (`''`).

This function can be used to register different hints for the same SQL pattern. When you insert hints with the same SQL pattern, hint name, and application name, no new hints are added to the hint_plan.hints table. The inserted hint is enabled while existing hints 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;');

The following information is returned:

                                                                 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)

Function used to modify hints

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

Parameter	Description
`query`	The SQL statement that contains the hint.
`application_name`	The application registered with the hint. The value is an empty string (`''`).

If the SQL pattern used by an SQL statement contains a hint, the existing hint in the hint_plan.hints table is replaced with the hint contained in the SQL statement specified by query. If the SQL pattern does not contain a hint, a new hint is registered.

Examples:

Query the existing hint 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)

Invoke a function to modify the hint.

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 modified hint in the hint_plan.hints table.

SELECT * FROM hint_plan.hints;

The hint of the SQL pattern changes 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)

Function used to delete hints

Delete the hint with the specified ID.
```
hint_plan.delete_hint_table(<id>)
```
Delete the hint defined by the specified SQL statement, hint name, and application name.
```
hint_plan.delete_hint_table(<query>, <hint>, <application_name>)
```
Delete the hint defined by the specified SQL statement and application name.
```
hint_plan.delete_all_hint_table(<query>, <application_name>)
```

Parameter	Description
`id`	The hint ID in the hint_plan.hints table.
`query`	The SQL statement that may or may not contain the hint.
`hint`	The hint.
`application_name`	The application registered with the hint. The value is an empty string (`''`).

Examples:

Query the original 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 the hint with the specified 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 with the specified hint deleted.

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 the hint defined by the specified SQL statement, hint name, and application name.

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 with the specified hint deleted.

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 the hint defined by the specified SQL statement and application name.

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 with the specified hint deleted.

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)

Function used to enable hints

Enable the hint with the specified ID. After the specified hint is enabled, other hints for the same SQL pattern are not applied.
```
hint_plan.enable_hint_table(<id>)
```
Enable the hint defined by the specified SQL statement, hint name, and application name. After the specified hint is enabled, other hints for the same SQL pattern are disabled.
```
hint_plan.enable_hint_table(<query>, <hint>, <application_name>)
```
Disable the hint with the specified ID.
```
hint_plan.disable_hint_table(<id>)
```
Disable the hint defined by the specified SQL statement, hint name, and application name.
```
hint_plan.disable_hint_table(<query>, <hint>, <application_name>)
```
Disable the hint defined by the specified SQL statement and application name.
```
hint_plan.disable_all_hint_table(<query>, <application_name>)
```

Parameter	Description
`id`	The hint ID in the hint_plan.hints table.
`query`	The SQL statement that may or may not contain the hint.
`hint`	The hint.
`application_name`	The application registered with the hint. The value is an empty string (`''`).

Examples:

Query the original 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 the hint with the specified 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 with the specified hint disabled.

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 the hint with the specified 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 with the specified hint enabled.

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 the hint defined by the specified SQL statement and application name.

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 with the specified hint enabled.

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 extension

If you no longer need the hint feature, execute the following statement to uninstall the pg_hint_plan extension:

DROP EXTENSION pg_hint_plan;

References

Use hints to modify execution plans