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;
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 |
| 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.
|
Hints for scan methods |
| Forces sequential scans on the table. |
| Forces tuple identifier (TID) scans on the table. | |
| Forces index scans on the table. You can specify an index. | |
| Forces index-only scans on the table. You can specify an index. | |
| Forces bitmap index scans on the table. | |
| Forbids sequential scans on the table. | |
| Forbids TID scans on the table. | |
| Forbids index scans on the table. | |
| Forbids index-only scans on the 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. |
| Forces nested loops for joins that consist of the specified tables. |
| Forces hash joins for joins that consist of the specified tables. | |
| Forces merge joins for joins that consist of the specified tables. | |
| Forbids nested loops for joins that consist of the specified tables. | |
| Forbids hash joins for joins that consist of the specified tables. | |
| Forbids merge joins for joins that consist of the specified tables. | |
Hints for join order |
| Forces join order as specified. |
| Forces join order and directions as specified. | |
Hints for row number correction |
| Corrects the row number of a result of the joins that consist of the specified tables. The available correction methods are absolute value
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). |
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;
NoteIndex-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)';
NoteTID 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:
|
pg_hint_plan.enable_hint_table | off | Specifies whether to enable the hint registration feature. Valid values:
|
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:
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 ( 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. |
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))
toHashJoin(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;