pg_hint_plan

1. Overview

pg_hint_plan is an extension that allows controlling PostgreSQL/IvorySQL execution plans using SQL comment hints. It optimizes query performance without modifying SQL logic and works in both PostgreSQL and Oracle compatibility modes.

2. Installation

The source installation environment is Ubuntu 24.04(x86_64), with IvorySQL 5 or above installed at /usr/ivory-5

2.1. Install from Source

# Clone PG18 branch source code

git clone --branch PG18 https://github.com/ossc-db/pg_hint_plan.git
cd pg_hint_plan

# Compile and install the extension
make PG_CONFIG=/usr/ivory-5/bin/pg_config
make PG_CONFIG=/usr/ivory-5/bin/pg_config install

2.2. Modify Database Configuration File

Modify the ivorysql.conf file to add pg_hint_plan to shared_preload_libraries:

shared_preload_libraries = 'gb18030_2022, liboracle_parser, ivorysql_ora, pg_hint_plan'

Restart the database for the configuration to take effect.

2.3. Load Extension

postgres=# LOAD 'pg_hint_plan';
LOAD

3. Hint Types

pg_hint_plan provides various types of hints to control different aspects of query execution plans.

3.1. Scan Method Hints

Hints specifying table scan methods:

SeqScan(table) - Sequential scan
IndexScan(table[ index]) - Index scan, optionally specifying index name
BitmapScan(table[ index]) - Bitmap scan, optionally specifying index name
TidScan(table) - TID scan

Negative form hints (prohibit using a specific scan method):

NoSeqScan(table)
NoIndexScan(table)
NoBitmapScan(table)
NoTidScan(table)

3.2. Join Method Hints

Hints specifying table join methods:

HashJoin(table table[ table…]) - Hash join
NestedLoop(table table[ table…]) - Nested loop join
MergeJoin(table table[ table…]) - Merge join

Negative form hints:

NoHashJoin(table table)
NoNestedLoop(table table)
NoMergeJoin(table table)

3.3. Join Order Hints

Leading(table table[ table…]) - Specify join order, tables are joined in sequence

3.4. Parallel Execution Hints

Parallel(table count[ workers]) - Set number of parallel worker processes
count - Whether to use parallel (0 means no, 1 means yes)
workers - Number of parallel worker processes (optional, defaults to planner-calculated value)

3.5. Other Hints

Set(enable_*) - Set GUC parameters
Rows(table table[ table…] correction) - Correct rows estimate

4. Usage Examples

4.1. Basic Usage Example

Using HashJoin and SeqScan hints:

postgres=# /*+
   HashJoin(a b)
   SeqScan(a)
*/
EXPLAIN SELECT *
FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid
ORDER BY a.aid;

4.2. Complex Multi-Hint Example

Combining multiple hints to control execution plan for complex queries:

postgres=# /*+
   NestedLoop(t1 t2)
   IndexScan(t2 t2_pkey)
   MergeJoin(t1 t2 t3)
   Leading(t1 t2 t3)
*/
EXPLAIN SELECT * FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id;

4.3. Parallel Execution Example

Setting parallel degree and join methods for multiple tables:

postgres=# /*+
   Parallel(t1 3)
   Parallel(t2 5)
   HashJoin(t1 t2)
*/
EXPLAIN SELECT * FROM large_table1 t1
JOIN large_table2 t2 ON t1.id = t2.id;

4.4. Specify Index Scan Example

Force scanning using a specific index:

postgres=# /*+
   IndexScan(employees emp_name_idx)
*/
EXPLAIN SELECT * FROM employees
WHERE last_name = 'SMITH';

5. Hint Table (Optional Feature)

pg_hint_plan provides an optional hint table feature for persistently storing hints.

5.1. Create Hint Table

postgres=# CREATE TABLE hint_plan.hints (
    id serial PRIMARY KEY,
    norm_query_string text NOT NULL,
    application_name text,
    hints text NOT NULL,
    CONSTRAINT hint_plan_hints_norm_query_string_key UNIQUE (norm_query_string, application_name)
);

5.2. Insert Hints

postgres=# INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
    'SELECT * FROM table1 WHERE id = ?;',
    'psql',
    'SeqScan(table1)'
);

Through the hint table, hints can be automatically applied to specific queries without modifying SQL statements.

6. IvorySQL Compatibility

6.1. Oracle Compatibility Mode

pg_hint_plan works in both PostgreSQL and Oracle compatibility modes. The hint syntax remains consistent and is compatible with IvorySQL-specific data types (VARCHAR2, NUMBER, etc.).

6.2. Oracle Mode Example

-- Connect to port 1521 (Oracle mode)
postgres=# /*+
   IndexScan(employees emp_name_idx)
*/
SELECT * FROM employees WHERE last_name = 'SMITH';

In Oracle compatibility mode, hint usage is identical to PostgreSQL mode and can normally control query execution plans containing IvorySQL-specific data types and syntax.