Hints PostgreSQL to change its intended execution plan via special comment statements using pg_hint_plan extension.

Background information

Unlike adjusting GUC variables to affect execution plans, which impacts all SQL in the current session, pg_hint_plan only modifies the specified single SQL execution plan to optimize a specific SQL execution plan.

Prerequisites

Before using this extension, include pg_stat_statements and pg_hint_plan in the shared_preload_libraries parameter. For more information about how to modify parameters, refer to Modifying parameters.

Create and delete extension

Create extension

CREATE EXTENSION pg_hint_plan;

Delete extension

DROP EXTENSION pg_hint_plan;

Usage

Usage prompt phrases

pg_hint_plan reads special format prompt phrases in the target sql statement. Prompt phrases start with characters /*+ and end with */. Prompt phrases consist of prompt names and parameters, enclosed in parentheses, with space separating two parameters. To improve readability, each prompt phrase occupies one line.

The default plan in the following example is:

Specifying HashJoin as the chosen join method starts with /*+ and ends with */, plan is:

Usage prompt table

Using prompt phrases causes many inconveniences when the query statement cannot be edited. At this time, the prompt can be placed in the special table hint_plan.hints, with the table structure as follows:

Column name	Description
id	Prompt row unique identifier. This column is auto-filled in order.
norm_query_string	The pattern matching the query to prompt. Constants in the query must be replaced with ?. Spaces also require special attention.
application_name	Name the session applying this prompt. An empty string indicates any session with application_name.
hints	Prompt phrase. Exclude comment tags.

Example

Prompt type

According to how the prompt phrases affect the execution plan, they can be divided into the following six categories: scan method, join method, join order, row number correction, parallel query, and GUC settings. The list of prompt phrases for each type can be viewed in the prompt list.

Scan method prompt

The scan method prompt forces the target table to use a specific scan method. pg_hint_plan identifies the target table by its table alias (if any). Scan methods include SeqScan, IndexScan, etc.

Scan prompts are effective for ordinary tables, inherited tables, UNLOGGED tables, temporary tables, and system tables, but ineffective for external tables, table functions, VALUES clauses, common expressions, views, and subqueries.

Example is as follows.

postgres=# /*+
postgres*# IndexScan(a a_pk)
postgres*# SeqScan(b)
postgres*# */
postgres-# explain select * from a join b on a.id=b.id;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=14.89..48.70 rows=635 width=45)
Hash Cond: (b.id = a.id)
-> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36)
-> Hash (cost=13.64..13.64 rows=100 width=9)
-> Index Scan using a_pk on a (cost=0.14..13.64 rows=100 width=9)
(5 rows)

Connection method prompt

Connection method prompt forces specifying related tables to aggregate together.

Connection method prompt is valid for ordinary tables, inherited tables, UNLOGGED tables, temporary tables, external tables, system tables, table functions, VALUES clauses, and common expressions, but invalid for views and subqueries.

postgres=# /*+
postgres*# MergeJoin(a b)
postgres*# IndexScan(a a_pk)
postgres*# SeqScan(b)
postgres*# */
postgres-# explain select * from a join b on a.id=b.id;
QUERY PLAN
-----------------------------------------------------------------------
Merge Join (cost=88.31..114.76 rows=635 width=45)
Merge Cond: (a.id = b.id)
-> Index Scan using a_pk on a (cost=0.14..13.64 rows=100 width=9)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: b.id
-> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36)
(6 rows)

Join order prompt

This prompt "Leading" indicates the join order of two or more tables. There are two methods to force specify here:

Force a specific join order without restricting the direction at each join level.

Force join direction.

Example is as follows:

postgres=# /*+
postgres*# NestLoop(a b)
postgres*# MergeJoin(a b c)
postgres*# Leading(a b c)
postgres*# */
postgres-# EXPLAIN SELECT * FROM a
postgres-# JOIN b ON (a.id = b.id)
postgres-# JOIN c ON (b.id = c.id);
QUERY PLAN
--------------------------------------------------------------------------------
Merge Join (cost=368.33..431.99 rows=4032 width=81)
Merge Cond: (a.id = c.id)
-> Sort (cost=280.16..281.75 rows=635 width=45)
Sort Key: a.id
-> Nested Loop (cost=0.14..250.60 rows=635 width=45)
-> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36)
-> Index Scan using a_pk on a (cost=0.14..0.17 rows=1 width=9)
Index Cond: (id = b.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: c.id
-> Seq Scan on c (cost=0.00..22.70 rows=1270 width=36)
(11 rows)

Line number correction prompt

Line number correction prompt corrects line number errors caused by scheduler limitations.

Example is as follows:

/*+ Rows(a b #10) */ SELECT... ; //设置连接结果的行数量为 10。
/*+ Rows(a b +10) */ SELECT... ; //行数量增加 10。
/*+ Rows(a b -10) */ SELECT... ; //行数量减去 10。
/*+ Rows(a b *10) */ SELECT... ; //将行数量扩大至原来的 10 倍。

Parallel execution prompt

Parallel query prompt forces parallel execution configuration during scanning.

The third parameter represents intensity. soft means pg_hint_plan only changes max_parallel_worker_per_gather; hard changes all parameters to make prompt numbers effective.

Parallel query prompts apply to ordinary tables, inheritance tables, UNLOGGED tables, and system tables; they do not apply to external tables, table functions, VALUES clauses, common table expressions (CTEs), views, or subqueries. The target object can be specified by the real name or alias of the internal table.

The following two examples illustrate different ways to execute queries on each table:

postgres=# explain /*+ Parallel(a 2 hard) Parallel(b 3 hard) */
postgres-# select * from a join b on a.id=b.id;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=1.25..12.36 rows=635 width=45)
Hash Cond: (b.id = a.id)
-> Gather (cost=0.00..0.00 rows=1270 width=36)
Workers Planned: 3
-> Parallel Seq Scan on b (cost=0.00..0.00 rows=410 width=36)
-> Hash (cost=0.00..0.00 rows=100 width=9)
-> Gather (cost=0.00..0.00 rows=100 width=9)
Workers Planned: 2
-> Parallel Seq Scan on a (cost=0.00..0.00 rows=59 width=9)
(9 rows)

postgres=# EXPLAIN /*+ Parallel(a 5 hard) */ SELECT sum(id) FROM a;
QUERY PLAN
----------------------------------------------------------------------------------
Finalize Aggregate (cost=3703.27..3703.28 rows=1 width=8)
-> Gather (cost=3702.75..3703.26 rows=5 width=8)
Workers Planned: 5
-> Partial Aggregate (cost=2702.75..2702.76 rows=1 width=8)
-> Parallel Seq Scan on a (cost=0.00..0.00 rows=1081100 width=4)
(5 rows)

Setting temporary GUC Parameter

Temporarily modify the GUC Parameter during planning. The set GUC is effective if the planner's configuration Parameter does not conflict with any other Prompt. The same GUC Parameter setting is subject to the latest one.

Example is as follows:

postgres=# /*+ Set(random_page_cost 1.2) */
SELECT * FROM a ta WHERE name = '10abc';
id | name
----+-------
10 | 10abc
(1 row)

Prompt list

All supported formats for Prompt are shown in the following table:

Scope	File format	Note
Scan method prompt	SeqScan(table)	Force sequential scan.
		TidScan(table)	Force TID scan.
		IndexScan(table[ index...])	Force index scan, a specific index can be specified.
		IndexOnlyScan(table[ index...])	Force using only index scan, a specific index can be specified.
		BitmapScan(table[ index...])	Force use bitmap scan.
		NoSeqScan(table)	Force not to use sequential scanning.
		NoTidScan(table)	Force no TID scan.
		NoIndexScan(table)	Force not to use index scan.
		NoIndexOnlyScan(table)	Force not to use index scan, only scan the table.
		NoBitmapScan(table)	Force no Bitmap scan.
Join method prompt	NestLoop(table table[ table...])	Force nested loop join.
		HashJoin(table table[ table...])	Force hash join.
		MergeJoin(table table[ table...])	Force using merge join.
		NoNestLoop(table table[ table...])	Force disable using nested loop join.
		NoHashJoin(table table[ table...])	Force not to use hash join.
		NoMergeJoin(table table[ table...])	Force not to use merge join.
Join order prompt	Leading(table table[ table...])	Enforce join order.
Join order prompt		Leading()	Force the order and direction of join.
line number correction prompt	Rows(table table[ table...] correction)	Correct the row number of the join result composed of the specified tables. Available correction methods are absolute value #<n>, addition +<n>, subtraction -<n>, and multiplication *<n>. <n> is a number that the strtod function can read.
Parallel execution prompt	Parallel(table <# of workers> [soft\|hard])	Force or prohibit parallel execution for the specified table. <worker#> is the required quantity of parallel worker process, 0 means disabling parallel execution. The third parameter, if soft (default), changes only max_parallel_workers_per_gather and leaves all other settings to the planner; if hard, all related parameters will be forced to be set.
Set temporary GUC parameter	Set(GUC-param value)	set the GUC parameter to this value during planner runtime.

Others

For more information, refer to PostgreSQL official documentation.

RDS for PostgreSQL