Use the pg_hint_plan extension to customize query plans for PostgreSQL - ApsaraDB RDS

The pg_hint_plan extension lets you override PostgreSQL's cost-based planner using hints embedded in SQL comments, like /*+ SeqScan(orders) */. Use it when the planner selects a suboptimal query plan that you cannot fix through statistics updates or index changes alone.

Prerequisites

Before you begin, make sure you have:

An RDS PostgreSQL 10 or later instance
A privileged account on the instance. To create one, see Create an account

Important

For RDS PostgreSQL 17, the minor engine version must be 20241030 or later. If you cannot create the extension, upgrade the minor engine version first.

How it works

PostgreSQL uses a cost-based optimizer that estimates the cost of every possible query plan and selects the lowest-cost one. Because the optimizer works from statistics rather than your knowledge of the data, it sometimes misses inherent data patterns and picks a suboptimal plan.

pg_hint_plan reads special hints from SQL comments and passes them to the planner before optimization. This lets you specify the scan method, join method, join order, and other planner behaviors for a specific query—without modifying the data or statistics.

Install the extension

Install using the console

Go to the RDS instance list, select a region, and click the instance ID.
In the left navigation pane, click Plug-ins.
On the Extension Management page, click the Uninstalled Extensions tab, search for pg_hint_plan, and click Install in the Actions column.
In the dialog box, select the target database and a privileged account, then click OK.

The extension is installed when the instance status changes from Maintaining Instance to Running.

Install using SQL

Set the instance parameters to add pg_hint_plan to the Running Value of shared_preload_libraries. For example:
```
'pg_stat_statements,auto_explain,pg_hint_plan'
```
Using a privileged account, connect to the database where you want to install the extension, then run:
```
CREATE EXTENSION pg_hint_plan;
```

Upgrade or uninstall the extension

On the Extension Management page, click the Installed Extensions tab:

To upgrade: click Upgrade in the Actions column. If no Upgrade button appears, the extension is already at the latest version.
To uninstall: click Uninstall in the Actions column.

To uninstall using SQL:

DROP EXTENSION pg_hint_plan;

Write comment hints

Hints are placed in a block comment that starts with /*+ and ends with */, immediately before the SQL statement. Each hint consists of a hint name and its parameters in parentheses, with parameters separated by spaces.

Example

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

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 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)

Use table aliases in hints

If a query uses a table alias, the hint must reference the alias—not the original table name. Using the original name causes the hint to be silently ignored.

Hint with alias (effective):

Example

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

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

Hint without alias (ignored):

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

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

Use the hint table for non-editable SQL

When you cannot modify the SQL statement directly, store hints in the hint_plan.hints table. Hints in this table take precedence over inline comment hints.

Note

By default, the user who creates the pg_hint_plan extension has permissions on the hint_plan.hints table.

The table has the following columns:

Column	Description
`id`	Unique row identifier, generated automatically.
`norm_query_string`	Pattern matching the target SQL statement. Replace all constants with `?`. Spaces are significant.
`application_name`	Application name to scope the hint. Leave blank to apply to all applications.
`hints`	Hint content, without the surrounding `/+ /` comment marks.

Enable the hint table

SET pg_hint_plan.enable_hint_table = on;

Manage hints in the table

Insert a hint:

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

Update a hint:

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

Delete a hint:

DELETE FROM hint_plan.hints
WHERE id = 1;

Hint types

pg_hint_plan supports six categories of hints.

Scan method hints

Force or prohibit a specific scan method on a table. If the table has an alias in the query, use the alias in the hint.

Valid on: ordinary tables, inherited tables, unlogged tables, temporary tables, system tables.

Not valid on: external tables, table functions, constant-value expressions, universal expressions, views, subqueries.

Example—force a sequential scan on t1 and an index scan on t2:

Example

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

Join method hints

Force or prohibit a specific join method between tables.

Valid on: ordinary tables, inherited tables, unlogged tables, temporary tables, foreign tables, system tables, table functions, constant-value expressions, universal expressions.

Not valid on: views, subqueries.

Join order hints

Control the order in which tables are joined. Two forms are available:

Specify join order only (direction chosen by the planner):

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

Specify join order and direction at each level:

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

Example

Row number correction hints

Correct row count estimates that the optimizer gets wrong. Four correction methods are available:

Example

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

Parallel execution hints

Set the number of parallel workers for a table. Set the worker count to 0 to disable parallel execution.

The third parameter controls which settings are changed:

soft (default): changes only max_parallel_workers_per_gather; the planner controls other settings.
hard: changes all related planner settings.

Valid on: ordinary tables, inherited tables, unlogged tables, system tables. Internal tables of a view can be referenced by real name or alias.

Not valid on: external tables, constant-value clauses, universal expressions, views, subqueries.

Example—run a join query with 3 workers on c1 and 5 workers on c2:

Example

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

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—aggregate with 5 workers:

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

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

Temporarily change a GUC parameter value for the duration of query planning. If the same GUC parameter is set more than once, the last value takes effect.

Example

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

Supported hint formats

Category	Format	Description
Scan methods	`SeqScan(table)`	Forces a sequential scan.
	`TidScan(table)`	Forces a TID (tuple identifier) scan.
	`IndexScan(table [index...])`	Forces an index scan. Optionally specify the index.
	`IndexOnlyScan(table [index...])`	Forces an index-only scan. Optionally specify the index.
	`BitmapScan(table [index...])`	Forces 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.
Join methods	`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...])`	Prohibits a nested loop join.
	`NoHashJoin(table table [table...])`	Prohibits a hash join.
	`NoMergeJoin(table table [table...])`	Prohibits a merge join.
Join order	`Leading(table table [table...])`	Specifies the join order.
	`Leading(<join pair>)`	Specifies the join order and direction at each level.
Row number correction	`Rows(table table [table...] correction)`	Corrects the estimated row count for a join. Correction methods: `#<n>` (set to n), `+<n>` (add n), `-<n>` (subtract n), `*<n>` (multiply by n). `<n>` must be readable by the `strtod` function.
Parallel execution	`Parallel(table <workers> [soft\|hard])`	Sets or prohibits parallel execution. Set `<workers>` to `0` to disable. Default: `soft`.
GUC parameters	`Set(GUC-param value)`	Sets a GUC parameter value during query planning.

For more information, see pg_hint_plan.