how to use the query plan customization extension - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL provides the pg_hint_plan extension, which allows you to use special comment statements as hints to modify the execution plans of PostgreSQL.

Prerequisites

The instance runs RDS PostgreSQL 10 or later.
Note
If you cannot create the extension, upgrade the minor engine version first. For example, for an RDS PostgreSQL 17 instance, the minor engine version should be 20241030 or later.
You have created a privileged account for the RDS PostgreSQL instance. For more information, see Create an account.

Background information

PostgreSQL uses a cost-based optimizer that utilizes data statistics rather than static rules. For an SQL statement, the optimizer evaluates the cost of each possible execution plan and selects the one with the lowest cost. The optimizer tries to select the best execution plan, but because it does not understand some inherent relationships that may exist in the data, these execution plans may not be perfect. You can use the pg_hint_plan extension to add hints to SQL statements. The hints specify how you want to execute the SQL statements. This way, you can optimize the execution plans of the SQL statements.

Install and uninstall the extension

Manage the extension in the console

Install the extension
1. Visit the RDS instance list, select a region in the top navigation bar, and then click the ID of the target instance.
2. In the left-side navigation pane, click Plug-ins.
3. On the Extension Management page, click the Uninstalled Extensions tab, search for the pg_hint_plan extension, and click Actions column's Install.
4. In the dialog box that appears, select the target database and privileged account, and then click OK to install the extension to the target database.
  When the instance status changes from Maintaining Instance to Running, the extension is successfully installed.
Update and uninstall the extension
- On the Extension Management page, click the Installed Extensions tab, and click Actions column's Upgrade for the target extension to upgrade it to the latest version.
  Note
  If the Actions column does not have an Upgrade button, the extension is already the latest version.
- On the Extension Management page, click the Installed Extensions tab, and click Actions column's Uninstall to uninstall the target extension.

Manage the extension using SQL commands

Set the instance parameters, and add pg_hint_plan to the Running Value of shared_preload_libraries. For example, change the Running Value to 'pg_stat_statements,auto_explain,pg_hint_plan'.
Use a privileged account to connect to the database where you want to install the extension, and execute the following SQL statements to manage the extension.
- Install the extension
```
CREATE EXTENSION pg_hint_plan;
```
- Uninstall the extension
```
DROP EXTENSION pg_hint_plan;
```

Comment hints

The pg_hint_plan comment hints start with /*+ and end with */.

A hint statement consists of a hint name and its parameters in parentheses, with parameters separated by spaces. For better readability, each hint statement can start on a new line.

Example

Use HashJoin as the join method and use SeqScan to scan the test_table01 table.

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 EXPLAIN SELECT *
    FROM test_table02 b
    JOIN test_table01 a ON b.bid = a.bid
   ORDER BY a.aid;

The following result is returned:

                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on test_table01 a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on test_table02 b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

In SQL queries, if an alias is used for a table name, the same alias should also be used in the pg_hint_plan hint statement.

Example

Hint statement with alias:

/*+ IndexScan(t) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

The following result is returned:

             QUERY PLAN              
-------------------------------------
 Index Scan using tbl_a_idx on tbl t
   Index Cond: (a = 1)

Hint statement without alias:

/*+ IndexScan(tbl) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

The following result is returned:

    QUERY PLAN     
-------------------
 Seq Scan on tbl t
   Filter: (a = 1)

Hint table

Hints can be used to optimize the execution plans of SQL statements. However, this is convenient only when SQL statements are editable. If SQL statements are not editable, you can place hints in a table named hint_plan.hints. The hint_plan.hints table contains the columns that are described in the following table.

Note

By default, the user who creates the pg_hint_plan extension has the permissions on the hint_plan.hints table. The hints in the hint_plan.hints table take precedence over the hints that you add by using the pg_hint_plan extension.

Column	Description
id	The ID of the hint. The ID is unique and is automatically generated.
norm_query_string	The pattern that matches the SQL statement to which you want to add the hint. Constants in the query must be replaced with `?`. Spaces are crucial parts of the pattern.
application_name	The name of the application to which the hint is applied. If this parameter is left empty, the hint is applied to all applications.
hints	The comment that contains the hint. You do not need to include comment marks.

Enable the hint table

SET pg_hint_plan.enable_hint_table = on;

Insert data into the hint table

INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
    'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
    '',
    'SeqScan(t1)'
);

Update data in the hint table

UPDATE hint_plan.hints
SET hints = 'IndexScan(t1)'
WHERE id = 1;

Delete data from the hint table

DELETE FROM hint_plan.hints
WHERE id = 1;

Hint types

Based on how hint phrases affect execution plans, they can be categorized into the following six types:

Hints for scan methods
This type of hint specifies the method that is used to scan the specified table. If the specified table has an alias, the pg_hint_plan extension identifies the table based on the alias. The scan methods include SeqScan, IndexScan, NoSeqScan, and more.
The hints for scan methods are valid on ordinary tables, inherited tables, unlogged tables, temporary tables, and system tables. The hints for scan methods are invalid on external tables, table functions, statements in which the values of constants are specified, universal expressions, views, and subqueries.
Example
```
/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
```
Hints for join methods
This type of hint specifies the method that is used to join the specified tables.
The hints for join methods are valid on ordinary tables, inherited tables, unlogged tables, temporary tables, external tables, system tables, table functions, statements in which the values of constants are specified, and universal expressions. The hints for join methods are invalid on views and subqueries.
Hints for join order
This type of hint specifies the order in which two or more tables are joined. You can use one of the following methods to specify a hint that specifies the join order:
- Specify the order in which you want to join the specified tables without the need to restrict the direction at each join level.
- Specify the order in which you want to join the specified tables and the direction at each join level.
Example
```
-- Specify the join order without restricting the direction at each join level
/*+
    Leading(t1 t2 t3)
 */
SELECT * FROM table1 t1
    JOIN table table2 t2 ON (t1.key = t2.key)
    JOIN table table3 t3 ON (t2.key = t3.key);

-- Specify the join order and direction
/*+
    Leading((t1 t2) t3)
 */
SELECT * FROM table1 t1
    JOIN table table2 t2 ON (t1.key = t2.key)
    JOIN table table3 t3 ON (t2.key = t3.key);
```

Hints for row number correction

This type of hint corrects row number errors that are caused by the optimizer.

Example

/*+ Rows(a b #10) */ SELECT... ;     //Set the row number of the join result to 10.
/*+ Rows(a b +10) */ SELECT... ;     //Increase the row number by 10.
/*+ Rows(a b -10) */ SELECT... ;     //Decrease the row number by 10.
/*+ Rows(a b *10) */ SELECT... ;     //Multiply the row number by 10.

Hints for parallel execution

This type of hint specifies the plan that is used to execute SQL statements in parallel.

The hints for parallel execution are valid on ordinary tables, inherited tables, unlogged tables, and system tables. The hints for parallel execution are invalid on external tables, clauses in which the values of constants are specified, universal expressions, views, and subqueries. You can specify the internal tables of a view based on their real names or aliases.

The following examples show how an SQL statement is executed in a different way on each table:

Example

Method 1

explain /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);

The following result is returned:

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)

Method 2

EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;

The following result is returned:

                                    QUERY PLAN                                  
-----------------------------------------------------------------------------------
 Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)

Hints for GUC parameter setting
This type of hint temporarily changes the value of a GUC parameter. The values of GUC parameters in the execution plan help you achieve the effect that you expect. However, this does not apply if the specified hint conflicts with the execution plans of other SQL statements. If you configure a GUC parameter more than once, the most recent value takes effect.
Example
```
/*+ Set(random_page_cost 2.0) */
SELECT * FROM table1 t1 WHERE key = 'value';
```

Supported hint formats

Category	Format	Description
Hints for scan methods	SeqScan(table)	Forces sequential scan on the table.
	TidScan(table)	Forces tuple identifier (TID) scan on the table.
	IndexScan(table[ index...])	Specifies an index scan. You can specify an index.
	IndexOnlyScan(table[ index...])	Specifies an index-only scan. You can specify an index.
	BitmapScan(table[ index...])	Specifies a bitmap scan.
	NoSeqScan(table)	Prohibits a sequential scan.
	NoTidScan(table)	Prohibits a TID scan.
	NoIndexScan(table)	Prohibits an index scan.
	NoIndexOnlyScan(table)	Prohibits an index scan. Only tables are scanned.
	NoBitmapScan(table)	Prohibits a bitmap scan.
Hints for join methods	NestLoop(table table[ table...])	Specifies a nested loop join.
	HashJoin(table table[ table...])	Specifies a hash join.
	MergeJoin(table table[ table...])	Specifies a merge join.
	NoNestLoop(table table[ table...])	Prohibits a nested loop join.
	NoHashJoin(table table[ table...])	Prohibits a hash join.
	NoMergeJoin(table table[ table...])	Prohibits a merge join.
Hints for join order	Leading(table table[ table...])	Specifies the join order.
Hints for join order	Leading(<join pair>)	Specifies the join order and direction.
Hints for row number correction	Rows(table table[ table...] correction)	Corrects the row number of the join result that is obtained from the specified tables. The available correction methods include absolute value `#<n>`, addition `+ <n>`, subtraction `-<n>`, and multiplication `* <n>`. `<n>` is a number that can be read by the strtod function.
Hints for parallel execution	Parallel(table <# of workers> [soft\|hard])	Specifies or prohibits the parallel execution of the specified tables. `<worker#>` is the number of parallel workers that you want to use. If you set this parameter to 0, parallel execution is prohibited. If you set the third parameter to soft, only the value of the max_parallel_workers_per_gather parameter is changed and the other parameters are specified by the optimizer. If you set the third parameter to hard, the values of all related parameters are changed. The default value of the third parameter is soft.
Hints for GUC parameter setting	Set(GUC-param value)	Specifies the value of a GUC parameter when the optimizer runs.

For more information, see pg_hint_plan.