PolarDB:pg_hint_plan

Step 1: Create the extension

CREATE EXTENSION pg_hint_plan;

Step 2: Load the extension

Choose one of the following loading methods based on your scope:

For a single user:

ALTER USER <username> SET session_preload_libraries = 'pg_hint_plan';

For a single database:

ALTER DATABASE <database_name> SET session_preload_libraries = 'pg_hint_plan';

If a misconfiguration prevents login, connect through another account or database and run:

ALTER USER <username> RESET session_preload_libraries;
ALTER DATABASE <database_name> RESET session_preload_libraries;

For a database cluster:

Go to the Quota Center, find the quota item named PolarDB PG pg_hint_plan use, and click Apply in the Actions column.

Step 3: Verify the extension is loaded

1. Enable debug output:

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

2. Run a test query with a hint:

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

   If the extension is loaded, the following output appears:

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

3. Reset debug settings:

   RESET pg_hint_plan.debug_print;
   RESET pg_hint_plan.message_level;

Hints for scan methods

Specify the scan method for a table. If the table has an alias, refer to it by the alias.

Effective on: ordinary tables, inherited tables, unlogged tables, temporary tables, system tables

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

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

Hints for join methods

Specify the join method for two or more tables.

Effective on: ordinary tables, inherited tables, unlogged tables, temporary tables, external tables, system tables, table functions, constant-value expressions, universal expressions

Not effective on: views and subqueries

Hints for join order

Specify the order in which tables are joined. Two approaches are available:

• Specify join order without constraining direction at each level.

• Specify join order and direction at each level.

/*+
    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) — join t1 and t2 using nested loop join

• MergeJoin(t1 t2 t3) — join t1, t2, and t3 using merge join

• Leading(t1 t2 t3) — set the join order to t1 → t2 → t3

Hints for row number correction

Correct row count estimates that the optimizer calculates incorrectly. Four operators are supported:

/*+ Rows(a b #10) */ SELECT ...;   -- Set join result row count to 10
/*+ Rows(a b +10) */ SELECT ...;   -- Increase row count by 10
/*+ Rows(a b -10) */ SELECT ...;   -- Decrease row count by 10
/*+ Rows(a b *10) */ SELECT ...;   -- Multiply row count by 10

Hints for parallel execution

Specify the parallel execution plan for a query.

Effective on: ordinary tables, inherited tables, unlogged tables, system tables

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

Use the table's real name or alias to specify internal tables within a view.

Example 1: Set the degree of parallelism (DOP) to 3 for c1 and 5 for c2:

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

Result:

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 the DOP for tl to 5:

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

Result:

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)

Hints for GUC parameter setting

Temporarily change a GUC parameter value for the duration of query planning. Unlike session-level GUC changes, these hints affect only the execution plan generation for the targeted query. If multiple hints set the same GUC parameter, the last one takes effect.

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