pg_hint_plan - PolarDB - Alibaba Cloud Documentation Center

The pg_hint_plan extension adjusts execution plans using hints in special comment statements.

Background information

PolarDB for PostgreSQL (Compatible with Oracle) uses a cost-based optimizer. This optimizer uses statistical data instead of fixed rules to determine the best execution path. For each SQL statement, the optimizer evaluates all possible execution plans and selects the one with the lowest cost. The optimizer does its best to choose the optimal execution plan. However, the chosen plan may not be perfect because the optimizer is unaware of some underlying data relationships.

You can adjust execution plans by setting Grand Unified Configuration (GUC) variables, but these changes affect the entire session. In contrast, pg_hint_plan adjusts only a single execution plan. This lets you optimize specific execution plans without affecting the entire session.

Precautions

Data Management Service (DMS) does not support hint comments. You must use other methods to connect to the database.
The pg_hint_plan extension recognizes content only within the first comment block.
During a scan, parsing stops immediately if a character other than a letter, number, space, underscore (_), comma (,), or parenthesis (()) is encountered.
pg_hint_plan handles objects differently than PostgreSQL. It performs a case-sensitive comparison of object names. For example, an object named TBL in a hint statement matches only TBL, not tbl or Tbl.

Limits

The following limits apply when you use the pg_hint_plan extension in PL/pgSQL stored procedures:

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).
- Cursor opening (OPEN).
- Traversal of query results (FOR).
A hint statement must be placed immediately after the first word of a query. A hint placed any earlier is not considered part of that query.

Create the extension

Create the extension.
```
CREATE EXTENSION pg_hint_plan;
```
Load the extension.
- 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 logon username.
  - Run the following command to automatically 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 logging on to the database, log on to PolarDB as another user or connect to another database to reset the parameter:
```
ALTER USER xxx reset session_preload_libraries;
ALTER DATABASE xxx reset session_preload_libraries;
```
- 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 the pg_hint_plan extension.
- Check if the extension is loaded.
  1. Run the following commands to output debug information 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 check if the extension was loaded successfully.
```
/*+Set(enable_seqscan 1)*/select 1;
```
    The following result indicates that the extension is loaded.
```
NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
```
  3. Run the following commands to turn off debug output.
```
RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;
```

Usage notes

Comment Hints

A pg_hint_plan comment starts with /*+ and ends with */. A hint statement includes a hint name and its parameters. The parameters are enclosed in parentheses and separated by spaces. For readability, you can place each hint on a new line.

Example

Use HashJoin as the join method and SeqScan to scan 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 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 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
Supported hint types are classified by how they affect an execution plan. They include hints for scan methods, join methods, join order, row number correction, parallel execution, and GUC parameter settings.
- Scan Method Hints
  Scan method hints force a specific scan method on a target table. pg_hint_plan identifies the target table by its alias, if an alias exists. Examples of scan methods include sequential scan and index scan.
  Scan hints are effective for standard tables, inherited tables, unlogged tables, temporary tables, and system tables. They are not effective for foreign tables, table functions, constant value statements, common table expressions, views, or subqueries.
  The following is an example command:
```
/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
```
- Join Method Hints
  Join method hints force a specific method for joining related tables. They are effective for standard tables, inherited tables, unlogged tables, temporary tables, foreign tables, system tables, table functions, constant value commands, and common table expressions. They are not effective for views or subqueries.
- Join Order Hints
  Join order hints specify the join order for two or more tables. You can force the join order in two ways:
  - Force a specific join order without restricting the direction at each join level.
  - Force the join direction.
  The following is an example command:
```
 /*+
    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 between tables t1, t2, and t3.
  Leading(t1 t2 t3): Specifies the join order of the three tables.
- Row Number Correction Hints
  Row number correction hints correct row count errors caused by query optimizer limitations.
```
 /*+ 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
  Parallel execution hints specify a parallel execution plan.
  Parallel degree hints are effective for standard tables, inherited tables, unlogged tables, and system tables. They are not effective for foreign tables, constant clauses, common table expressions, views, or subqueries. The internal tables of a view can be targeted by their real names or aliases.
  The following two examples show different ways to run a query on the tables:
  - Method 1: Specify a degree of parallelism of 3 for table c1 and 5 for table c2.
```
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: Specify a degree of parallelism of 5 for table t1.
```
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
  You can change the value of a GUC parameter during query execution. This value is effective only while the executor generates the query plan and does not affect other statements. If you set the same GUC parameter multiple times, the last setting takes effect.
  The following is an example command:
```
 /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
```

List of Hint Formats

The following table lists the supported formats for all hints. To use a feature, add a hint in the corresponding format to your comment. Brackets [] indicate optional parameters.

Type	Format	Description
Scan method	SeqScan(table)	Forces a sequential scan on the table named 'table'.
	TidScan(table)	Forces a TID scan on the table named 'table'.
	IndexScan(table[ index...])	Forces an index scan on the table named 'table'. You can specify which index to use by adding the index name.
	IndexOnlyScan(table[ index...])	Forces an index-only scan on the table named 'table'. You can specify which index to use by adding the index name.
	BitmapScan(table[ index...])	Forces a bitmap index scan on the table named 'table'. You can specify which index to use by adding the index name.
	NoSeqScan(table)	Prevents a sequential scan on the table named 'table'.
	NoTidScan(table)	Prevents a TID scan on the table named 'table'.
	NoIndexScan(table)	Prevents an index scan on the table named 'table'.
	NoIndexOnlyScan(table)	Prevents an index-only scan on the table named 'table'.
	NoBitmapScan(table)	Prevents a bitmap index scan on the table named 'table'.
Join method	NestLoop(table table[ table...])	Forces a nested loop join for join operations between the specified tables.
	HashJoin(table table[ table...])	Forces a hash join for join operations between the specified tables.
	MergeJoin(table table[ table...])	Forces a merge join for join operations between the specified tables.
	NoNestLoop(table table[ table...])	Prevents a nested loop join for join operations between the specified tables.
	NoHashJoin(table table[ table...])	Prevents a hash join for join operations between the specified tables.
	NoMergeJoin(table table[ table...])	Prevents a merge join for join operations between the specified tables.
Join order	Leading(table table[ table...])	Specifies the join order between tables.
Join order	Leading(<join pair>)	Specifies the join order between two tables.
Row number correction	Rows(table table[ table...] correction)	Corrects the number of rows in the join result of the specified tables. Available correction methods include absolute value (#<n>), addition (+ <n>), subtraction (-<n>), and multiplication (* <n>). <n> represents the number of rows to specify.
Parallel query configuration	Parallel(table <# of workers> [soft\|hard])	Forces or prevents a parallel scan on the specified table. Note <# of workers> is the desired degree of parallelism (the number of parallel worker processes). A value of 0 prevents parallel execution. If the third parameter is soft (the default), only the value of the max_parallel_workers_per_gather parameter is modified. The optimizer determines the actual degree of parallelism. hard forces the specified degree of parallelism.
	PX(<# of workers>)	Specifies a parallel query during a cross-node parallel execution. Note <# of workers> specifies the degree of parallelism.
	NoPX()	Forces the query to not use the cross-node parallel execution feature.
GUC parameter configuration hints	Set(GUC-param value)	Sets a GUC parameter to the specified value at optimizer runtime.

Note

pg_hint_plan can also specify the query plan generated by a cross-node parallel execution. In cross-node parallel execution scenarios, row number correction hints are not supported. Join method hints can apply only to joins between two tables. Join order hints can specify only the overall order of all tables.