Use pg_hint_plan hints to adjust query execution plans - PolarDB

The pg_hint_plan extension lets you override the query optimizer's execution plan choices by embedding hints directly in SQL comments.

PolarDB for PostgreSQL (Compatible with Oracle) uses a cost-based optimizer that selects execution plans based on statistical data. While the optimizer generally makes good choices, it cannot account for every data relationship. Setting Grand Unified Configuration (GUC) variables adjusts behavior across an entire session; pg_hint_plan targets a single query without affecting anything else.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL (Compatible with Oracle) cluster
A database user with sufficient privileges to create extensions and alter user or database settings

Usage notes

Data Management Service (DMS) does not support hint comments. Connect to the database using a different client.
pg_hint_plan reads only the first comment block in a query. Hints in subsequent comment blocks are ignored.
During hint parsing, the scanner stops at any character other than a letter, digit, space, underscore (_), comma (,), or parenthesis (()).
pg_hint_plan compares object names case-sensitively. A hint referencing TBL applies only to TBL, not tbl or Tbl. When a query uses a table alias, use the alias in the hint—not the original table name.

Limits

The following limits apply when using pg_hint_plan in PL/pgSQL stored procedures:

Hints take effect only for these statement types:
- SELECT, INSERT, UPDATE, DELETE (single-row queries)
- RETURN QUERY (multi-row queries)
- EXECUTE QUERY (SQL statement execution)
- OPEN (cursor opening)
- FOR (traversal of query results)
Place each hint immediately after the first word of a query. Hints placed before that position are not associated with the query.

Install the extension

Create the extension.
```
CREATE EXTENSION pg_hint_plan;
```
Load the extension using one of the following methods:
For a single user:
```
ALTER USER <username> SET session_preload_libraries = 'pg_hint_plan';
```
Replace <username> with the login username.
For a specific database:
```
ALTER DATABASE <database_name> SET session_preload_libraries = 'pg_hint_plan';
```
Note
If a configuration error prevents you from logging in, connect as a different user or to a different database and reset the parameter:
```
ALTER USER <username> RESET session_preload_libraries;
ALTER DATABASE <database_name> RESET session_preload_libraries;
```
For a database cluster: Go to Quota Center. In the row for PolarDB PG pg_hint_plan use, click Apply in the Actions column.

Verify that the extension loaded successfully.

Enable debug output.

SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;

Run a test query with a hint.

/*+Set(enable_seqscan 1)*/SELECT 1;

The following output confirms the extension is loaded:

NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)

Disable debug output.

RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;

Write hint comments

A pg_hint_plan comment starts with /*+ and ends with */. Each hint consists of a hint name followed by its parameters in parentheses, separated by spaces. Place multiple hints on separate lines for readability.

/*+
   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 query plan produced:

                                  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

Scan method hints

Scan method hints force a specific scan method on a target table, identified by its alias if one exists.

Effective for: standard tables, inherited tables, unlogged tables, temporary tables, system tables

Not effective for: foreign tables, table functions, constant value statements, common table expressions (CTEs), views, subqueries

/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
SELECT * FROM table1 t1 JOIN table2 t2 ON (t1.key = t2.key);

Join method hints

Join method hints force a specific join algorithm for a set of tables.

Effective for: standard tables, inherited tables, unlogged tables, temporary tables, foreign tables, system tables, table functions, constant value commands, CTEs

Not effective for: views, subqueries

Join order hints

Join order hints specify the order in which tables are joined. Two syntax forms are supported:

Leading(t1 t2 t3) — specifies join order only, without restricting direction at each level
Leading((t1 t2) t3) — specifies both order and direction (nested parentheses indicate join pairs)

/*+
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    Leading(t1 t2 t3)
  */
SELECT * FROM table1 t1
    JOIN table2 t2 ON (t1.key = t2.key)
    JOIN table3 t3 ON (t2.key = t3.key);

In this example:

NestLoop(t1 t2) — uses a nested loop join for t1 and t2
MergeJoin(t1 t2 t3) — uses a merge join for the result of (t1, t2) joined with t3
Leading(t1 t2 t3) — joins the three tables in the order t1 → t2 → t3

Row number correction hints

Row number correction hints override the row count estimate the optimizer uses for a join result. This is useful when optimizer statistics are inaccurate.

/*+ Rows(a b #10) */ SELECT ...; -- Set join result to exactly 10 rows
/*+ Rows(a b +10) */ SELECT ...; -- Add 10 to the estimated row count
/*+ Rows(a b -10) */ SELECT ...; -- Subtract 10 from the estimated row count
/*+ Rows(a b *10) */ SELECT ...; -- Multiply the estimated row count by 10

Parallel execution hints

Parallel execution hints control the degree of parallelism for a query.

Effective for: standard tables, inherited tables, unlogged tables, system tables. Internal tables of a view can be targeted by their real names or aliases.

Not effective for: foreign tables, constant clauses, CTEs, views, subqueries

Example 1: Set parallelism for two tables in a join.

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

Output:

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 parallelism for a single-table aggregation.

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

Output:

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 hints

GUC parameter hints change the value of a GUC parameter for the duration of query plan generation only. The change does not affect other statements. If the same GUC parameter appears multiple times, the last value takes effect.

/*+ Set(random_page_cost 2.0) */
SELECT * FROM table1 t1 WHERE key = 'value';

Hint reference

The following table lists all supported hint formats. Brackets [ ] indicate optional parameters.

Type	Format	Description
Scan method	`SeqScan(table)`	Forces a sequential scan on the table.
	`TidScan(table)`	Forces a TID scan on the table.
	`IndexScan(table [index...])`	Forces an index scan. Optionally specify which index to use.
	`IndexOnlyScan(table [index...])`	Forces an index-only scan. Optionally specify which index to use.
	`BitmapScan(table [index...])`	Forces a bitmap index scan. Optionally specify which index to use.
	`NoSeqScan(table)`	Prevents a sequential scan.
	`NoTidScan(table)`	Prevents a TID scan.
	`NoIndexScan(table)`	Prevents an index scan.
	`NoIndexOnlyScan(table)`	Prevents an index-only scan.
	`NoBitmapScan(table)`	Prevents a bitmap index scan.
Join method	`NestLoop(table table [table...])`	Forces a nested loop join.
	`HashJoin(table table [table...])`	Forces a hash join.
	`MergeJoin(table table [table...])`	Forces a merge join.
	`NoNestLoop(table table [table...])`	Prevents a nested loop join.
	`NoHashJoin(table table [table...])`	Prevents a hash join.
	`NoMergeJoin(table table [table...])`	Prevents a merge join.
Join order	`Leading(table table [table...])`	Specifies join order (order only, no direction constraint).
	`Leading(<join pair>)`	Specifies join order and direction using nested parentheses.
Row number correction	`Rows(table table [table...] correction)`	Corrects the row count estimate for a join. Correction methods: `#<n>` (set), `+<n>` (add), `-<n>` (subtract), `*<n>` (multiply).
Parallel query	`Parallel(table <# of workers> [soft\|hard])`	Controls parallel scan for the specified table. Set `<# of workers>` to `0` to disable parallel execution. `soft` (default) adjusts `max_parallel_workers_per_gather` and lets the optimizer determine the final degree. `hard` forces the specified degree.
	`PX(<# of workers>)`	Specifies the degree of parallelism for cross-node parallel execution.
	`NoPX()`	Disables cross-node parallel execution for the query.
GUC parameter	`Set(GUC-param value)`	Sets a GUC parameter to the specified value during query plan generation.

Note

In cross-node parallel execution scenarios:

Row number correction hints are not supported.
Join method hints apply only to joins between two tables.
Join order hints can specify only the overall join order of all tables.