Unlike Oracle, PostgreSQL does not provide SQL hints out of the box, so you need to install the separate pg_hint_plan extension. SQL hints tell the optimizer to change the SQL plan it generated into the form the user wants when the SQL statement runs. Without the pg_hint_plan extension, hints in SQL statements are not applied, so you must install it to use hints.
📢 This article covers how to install the pg_hint_plan extension and how to use hints.
Preparing the pg_hint_plan Extension Installation Files
The pg_hint_plan extension files differ by PostgreSQL version and OS version, so prepare the installation files that match your environment. You can download the pg_hint_plan extension installation files from GitHub.
📢 pg_hint_plan Extension Github URL: https://github.com/ossc-db/pg_hint_plan
On the pg_hint_plan GitHub page, click Release in the middle on the right (red box) to find the installation files for your PostgreSQL and OS versions. Files are grouped as pg_hint_plan{
PostgreSQL_Version}, so scroll down to the heading that matches your PostgreSQL version and get the file for your OS version from Assets.
Installing the pg_hint_plan Extension
📢 This article tests on Rocky Linux 8.9 / PostgreSQL 16.1. You can install the pg_hint_plan extension with an RPM or from source code.
Installing with RPM
Download the RPM
Copy the link to the RPM installation file from GitHub and download it to the server.
Install the RPM
Installing from Source Code
Download the source code
Extract and install the source code (make && make install)
Verifying the pg_hint_plan Extension Installation
Verify the installation
Check that the pg_hint_plan extension is installed in the path where the PostgreSQL engine is installed.
📢 The paths shown below may differ depending on your PostgreSQL installation.
- Check for the pg_hint_plan.so library file in
{PostgreSQL engine path}/lib
- Check for the pg_hint_plan control and sql files in
{PostgreSQL engine path}/share/extension
Enable the pg_hint_plan extension in PostgreSQL
To use the pg_hint_plan extension, change the
shared_preload_libraries parameter in the PostgreSQL configuration file and restart PostgreSQL.Install the extension
Using the pg_hint_plan Extension
Check whether the extension is installed
Check index information
How to write hints
📢 Without the pg_hint_plan extension, hints written in SQL are not applied
Using the pg_hint_plan Extension: Controlling the SQL Plan
Scan Method : Index Scan
Without hints
The SQL plan for this query uses a bitmap index scan.
With hints
We applied the
indexscan Hint to make the query use a specific index.Join Method : Nested Loop Join + Leading
Without hints
The SQL plan hash-joins the movie and language tables.
With hints
We used the
leading and nestloop hints to adjust the SQL plan so that the language table is the driving table and is joined with the movie table using a nested loop join.Combining Hints: Parallel + Rows + Set
Without hints
To make the hints easier to understand, we dropped all indexes and ran the query so that we could apply hints other than join or scan methods.
With hints
We applied hints (Rows, Parallel, Set) that help with query execution and SQL plan control.
The
Rows hint changes the final row count shown in the SQL plan to a specific value. For the Parallel hint, soft (the default) allows parallelism but leaves it to the optimizer to decide, while hard forces parallel execution.The
Set hint applies some of the SQL plan parameters in the PostgreSQL configuration file (postgresql.conf) to the SQL plan.📢 random_page_cost: parameter that sets the planner's estimate of the cost of a non-sequentially fetched disk page (default 4.0)
Hints Provided by the pg_hint_plan Extension
📢 Items in [ ] in the Format column are optional, so the hint still applies if you omit them.
Scan Method
Hint List | Description | Format |
SeqScan | Forces a sequential scan on the table | SeqScan(Table) |
TidScan | Forces a TID scan on the table | TidScan(Table) |
IndexScan | Forces an index scan using the specified index on the table. If the table has only one index or you don't need to specify one, you can write just the table | IndexScan(Table [index]) |
IndexOnlyScan | Forces an index-only scan using the specified index on the table. If the table has only one index or you don't need to specify one, you can write just the table | IndexOnlyScan(Table [index]) |
BitmapScan | Forces a bitmap scan using the specified index on the table. If the table has only one index or you don't need to specify one, you can write just the table | BitmapScan(Table [index]) |
IndexScanRegexp | Forces an index scan using indexes that match the specified POSIX regular expression pattern. If there is only one pattern or you don't need to specify one, you can write just the table | IndexScanRegexp(Table [POSIX Regrexp..]) |
IndexOnlyScanRegexp | Forces an index-only scan using indexes that match the specified POSIX regular expression pattern (9.2v or later). If there is only one pattern or you don't need to specify one, you can write just the table | IndexOnlyScanRegexp(Table [POSIX Regrexp..]) |
BitmapScanRegexp | Forces a bitmap scan using indexes that match the specified POSIX regular expression pattern. If there is only one pattern or you don't need to specify one, you can write just the table | BitmapScanRegexp(Table [POSIX Regrexp..]) |
NoSeqScan | Prevents a sequential scan on the table | NoSeqScan(Table) |
NoTidScan | Prevents a TID scan on the table | NoTidScan(Table) |
NoIndexScan | Prevents an index scan or index-only scan on the table | NoIndexScan(Table) |
NoIndexOnlyScan | Prevents an index-only scan on the table | NoIndexOnlyScan(Table) |
NoBitmapScan | Prevents a bitmap scan on the table | NoBitmapScan(Table) |
Join Method
Hint List | Description | Format |
Leading | Specifies the join order. To also control join direction, write the parentheses as in form (2) on the right in Format; Table1 is then the driving (outer) table | (1) Leading(Table Table)(2) Leading((Table1 Table2)) |
NestLoop | Forces a nested loop join for the specified tables | NestLoop(TableTable [Table..]) |
HashJoin | Forces a hash join for the specified tables | HashJoin(Table Table [Table..]) |
MergeJoin | Forces a merge join for the specified tables | MergeJoin(Table Table [Table..]) |
NoNestLoop | Prevents a nested loop join for the specified tables | NoNestLoop(Table Table [Table..]) |
NoHashJoin | Prevents a hash join for the specified tables | NoHashJoin(Table Table [Table..]) |
NoMergeJoin | Prevents a merge join for the specified tables | NoMergeJoin(Table Table [Table..]) |
Behavior Control on Join
Hint List | Description | Format |
Memoize | When Memoize is enabled, caches the result of the top-level join among the specified tables | Memoize(Table Table [ Table...]) |
NoMemoize | Prevents caching (memoizing) inner results in joins between the specified tables | NoMemoize(Table Table [ Table...]) |
Row Number Correction
Hint List | Description | Format |
Rows | Corrects the row count of the join result for the specified tables using absolute (#), add (+), subtract (-), and multiply (*) | Rows(Table Table [ Table...] correction) |
Parallel Query Configuration
Hint List | Description | Format |
Parallel | Applies parallel execution to the table scan ( default : soft ) | Parallel(Table <# of workers> [soft|hard]) |
GUC(Grand Unified Configuration) : postgresql.conf Parameter
Hint List | Description | Format |
Set | Sets the SQL plan-related GUC parameters to the values defined in the hint while the optimizer runs. Not applied if it conflicts with another hint | Set(GUC-param value) |
Related Articles
