Using the pg_hint_plan extension-PolarDB(PolarDB)-阿里云帮助中心

Background information

PostgreSQL uses a cost-based optimizer that utilizes data statistics rather than static rules. The optimizer evaluates the costs of all possible execution plans for an SQL statement, and executes the execution plan with the lowest cost. The optimizer makes the best effort, but the selected execution plan may not be the best plan, because the optimizer does not consider the underlying relationships among data.

You can specify Grand Unified Scheme (GUC) variables to adjust the execution plan, but this would affect the entire session. If you do not want to affect the entire session, you can use the pg_hint_plan to optimize a single execution plan.

Prerequisites

This feature is available for the following versions of PolarDB for PostgreSQL:

PostgreSQL 16 (kernel minor version 2.0.16.9.6.0 or later)
PostgreSQL 14 (no kernel minor version restriction)
PostgreSQL 11 (no kernel minor version restriction)

Note

You can view the kernel minor version in the console or by running the SHOW polardb_version; command. If your cluster does not meet the version requirement, upgrade its kernel minor version.

Precautions

Data Management Service (DMS) does not support hints in comments. Use another database client to connect to your database.
The pg_hint_plan extension reads hints only from the first comment block in a statement.
The hint scanner stops immediately if it encounters any characters other than letters, numbers, spaces, underscores (_), commas (,), or parentheses (()).
pg_hint_plan handles object names differently than PostgreSQL. It performs a case-sensitive comparison of object names. For example, a hint for an object named TBL matches only an object named TBL, not tbl or Tbl.

Limitations

The following limitations apply when you use the pg_hint_plan extension within a PL/pgSQL stored procedure:

Hints take effect only for the following types of statements:
- Queries that return a single row (SELECT, INSERT, UPDATE, DELETE).
- Queries that return multiple rows (RETURN QUERY).
- SQL statement execution (EXECUTE QUERY).
- Statements that open a cursor (OPEN).
- Loops over query results (FOR).
A hint must be placed immediately after the first word of a query. pg_hint_plan ignores hints placed before the first word.

Create and load the pg_hint_plan extension

Create the extension.
```
CREATE EXTENSION pg_hint_plan;
```
Load the extension.
- To automatically load the extension for a single user:
  - Run the following command to load the extension.
```
ALTER USER xxx set session_preload_libraries='pg_hint_plan';
```
    Note
    Replace xxx with the actual username.
  - Run the following command to load the extension for a specific database.
```
ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
```
  Note
  If a configuration error prevents you from connecting to the database, you must connect to your PolarDB instance with a different user or database and run the following reset commands:
```
ALTER USER xxx reset session_preload_libraries;
ALTER DATABASE xxx reset session_preload_libraries;
```
- To automatically load the extension for a database cluster:
  
  Go to the Quota Center. In the row for the PolarDB PG pg_hint_plan usage quota, click Apply in the Actions column to request access to the pg_hint_plan extension.
- Verify that the extension is loaded.
  1. Run the following commands to send debug output to the client.
```
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
```
  2. Run the following command to verify that the extension is loaded.
```
/*+Set(enable_seqscan 1)*/select 1;
```
    If the extension is loaded, the command returns the following output:
```
NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
```
  3. Run the following commands to disable the debug output.
```
RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;
```

Usage notes

Comment hints

A pg_hint_plan comment block starts with /*+ and ends with */. A hint consists of a hint name followed by parameters in parentheses. Parameters are separated by spaces. For readability, you can place each hint on a new line.

Example

The following example uses the HashJoin join method and a SeqScan scan method for the pgbench_accounts table:

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

The command returns the following output:

                                      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 pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

Hint types

Hint types

Hints are classified into the following six types based on how they affect execution plans:
- Scan method hints
  
  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. Supported scan methods include SeqScan, IndexScan, and so on.
  
  The hints for scan methods are effective on ordinary tables, inherited tables, unlogged tables, temporary tables, and system tables. However, the hints for scan methods are not effective 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);
```
- Join method hints
  
  This type of hint specifies the method that is used to join the specified tables. The hints for join methods are effective 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 not effective on views and subqueries.
- Join order hints
  A join order hint specifies the join order for two or more tables. You can use one of the following methods to force the join order:
  - Force a specific join order without restricting the direction at each join level.
  - Force the join direction.
  Example:
```
 /*+
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    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);
```
  Note
  In this example:
  
  NestLoop(t1 t2): specifies the join method for tables t1 and t2.
  
  MergeJoin(t1 t2 t3): specifies the join method for tables t1, t2, and t3.
  
  Leading(t1 t2 t3): specifies the join order for the three tables.
- Row number correction hints
  
  This type of hint corrects row number errors that are caused by the restrictions of the optimizer.
```
 /*+ Rows(a b #10) */ SELECT... ; # Sets the number of rows in the join result to 10.
 /*+ Rows(a b +10) */ SELECT... ; # Increases the number of rows by 10.
 /*+ Rows(a b -10) */ SELECT... ; # Decreases the number of rows by 10.
 /*+ Rows(a b *10) */ SELECT... ; # Multiplies the number of rows by 10.
```
- Parallel execution hints
  
  This type of hint specifies the plan that is used to execute SQL statements in parallel.
  
  The hints for parallel execution are effective on ordinary tables, inherited tables, unlogged tables, and system tables. However, the hints for parallel execution are not affective 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 by their real names or aliases.
  The following examples show how to run a query with different levels of parallelism on each table:
  - Example 1: Set the degree of parallelism (DOP) for table c1 to 3 and for table c2 to 5.
```
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)
                                        
```
  - Example 2: Set the degree of parallelism (DOP) for table t1 to 5.
```
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)
```
- GUC parameter setting hints
  
  This type of hint temporarily changes the value of a GUC parameter. The values of GUC parameters take effect only when the executor generates execution plans. The values help you improve the query performance without affecting the entire session. If you set more than one hint on a GUC parameter, the latest hint takes effect.
  Example:
```
 /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
```

Hint syntax

The following table describes the syntax for all supported hints. You can add these hints to your queries in comment blocks. Optional parameters are enclosed in square brackets [].

Type	Syntax	Description
Scan method hints	SeqScan(table)	Forces a sequential scan on the specified table.
	TidScan(table)	Forces a Tuple ID (TID) scan on the specified table.
	IndexScan(table[ index...])	Forces an index scan on the specified table. You can specify one or more indexes to use.
	IndexOnlyScan(table[ index...])	Forces an index-only scan on the specified table. You can specify one or more indexes to use.
	BitmapScan(table[ index...])	Forces a bitmap index scan on the specified table. You can specify one or more indexes to use.
	NoSeqScan(table)	Prohibits a sequential scan on the specified table.
	NoTidScan(table)	Prohibits a TID scan on the specified table.
	NoIndexScan(table)	Prohibits an index scan on the specified table.
	NoIndexOnlyScan(table)	Prohibits an index-only scan on the specified table.
	NoBitmapScan(table)	Prohibits a bitmap index scan on the specified table.
Join method hints	NestLoop(table table[ table...])	Forces a nested loop join for the specified tables.
	HashJoin(table table[ table...])	Forces a hash join for the specified tables.
	MergeJoin(table table[ table...])	Forces a merge join for the specified tables.
	NoNestLoop(table table[ table...])	Prohibits a nested loop join for the specified tables.
	NoHashJoin(table table[ table...])	Prohibits a hash join for the specified tables.
	NoMergeJoin(table table[ table...])	Prohibits a merge join for the specified tables.
Join order hints	Leading(table table[ table...])	Specifies the join order for the tables.
Join order hints	Leading(<join pair>)	Specifies the join order and direction for two tables.
Row number correction hints	Rows(table table[ table...] correction)	Corrects the estimated number of rows for a join result. Available correction methods include absolute value (#<n>), addition (+<n>), subtraction (-<n>), and multiplication (*<n>), where <n> is the number of rows.
Parallel execution hints	Parallel(table <# of workers> [soft\|hard])	Forces or disables a parallel scan on the specified table. Note <# of workers> specifies the desired degree of parallelism (DOP), which is the number of parallel worker processes. A value of 0 disables parallelism. If the third parameter is `soft` (the default), the hint only modifies the value of the `max_parallel_workers_per_gather` parameter, and the optimizer determines the actual degree of parallelism. The `hard` parameter forces the specified degree of parallelism.
	PX(<# of workers>)	Specifies a cross-node parallel execution. Note <# of workers> specifies the degree of parallelism (DOP).
	NoPX()	Prevents the query from using cross-node parallel execution.
GUC parameter setting hints	Set(GUC-param value)	Sets a GUC parameter to the specified value while the optimizer generates the execution plan.

Note

You can also use pg_hint_plan to specify execution plans for cross-node parallel execution. However, in cross-node parallel execution scenarios, row number correction hints are not supported. Join method hints can be applied only to joins between two tables, and join order hints can specify the order only for all tables involved.