pg_hint_plan reads hinting phrases in a comment of special form given with the target SQL statement. The special form is beginning by the character sequence “/*+” and ends with “*/”. Hint phrases consist of hint name and following parameters enclosed in parentheses and delimited by spaces. Each hinting phrases can be delimited by new lines for readability.

In the example below, hash join is selected as the joining method and scanning pgbench_accounts by sequential scan method.

postgres=# /*+
postgres*#    HashJoin(a b)
postgres*#    SeqScan(a)
postgres*#  */
postgres-# EXPLAIN SELECT *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   ORDER BY a.aid;
                                      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)

postgres=# 

Hinting phrases are classified into six types based on the kind of object and how they can affect planning: scanning methods, join methods, joining order, row number correction, parallel query, and GUC setting. You will see the lists of hint phrases of each type in Hint list (See Section F.37.10).

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

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

postgres=# / * + Leading ((t1 (t2 t3))) * / SELECT ...

postgres=# /*+ Rows(a b #10) */ SELECT... ; Sets rows of join result to 10
postgres=# /*+ Rows(a b +10) */ SELECT... ; Increments row number by 10
postgres=# /*+ Rows(a b -10) */ SELECT... ; Subtracts 10 from the row number.
postgres=# /*+ Rows(a b *10) */ SELECT... ; Makes the number 10 times larger.

postgres=# 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)

postgres=# 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)

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

GUC parameters described below affect the behavior of pg_hint_plan.

pg_hint_plan reads hints from only the first block comment and any characters except alphabets, digits, spaces, underscores, commas and parentheses stop parsing immediately. In the following example HashJoin(a b) and SeqScan(a) are parsed as hints, but IndexScan(a) and MergeJoin(a b) are not.

postgres=# /*+
postgres*#    HashJoin(a b)
postgres*#    SeqScan(a)
postgres*#  */
postgres-# /*+ IndexScan(a) */
postgres-# EXPLAIN SELECT /*+ MergeJoin(a b) */ *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   ORDER BY a.aid;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
@@ -237,17 +285,56 @@ postgres-#   ORDER BY a.aid;
 (7 rows)

postgres=#

pg_hint_plan works for queries in PL/pgSQL scripts with some restrictions.

postgres=# CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
postgres$# DECLARE
postgres$#     id  integer;
postgres$#     cnt integer;
postgres$# BEGIN
postgres$#     SELECT /*+ NoIndexScan(a) */ aid
postgres$#         INTO id FROM pgbench_accounts a WHERE aid = $1;
postgres$#     SELECT /*+ SeqScan(a) */ count(*)
postgres$#         INTO cnt FROM pgbench_accounts a;
postgres$#     RETURN id + cnt;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;

Unlike the way PostgreSQL handles object names, pg_hint_plan compares bare object names in hints against the database internal object names in a case-sensitive way. Therefore, an object name TBL in a hint matches only “TBL” in database and does not match any unquoted names like TBL, tbl, or Tbl.

The objects as the hint parameter should be enclosed in double quotes if they includes parentheses, double quotes, and white spaces. The escaping rule is the same as in PostgreSQL.

pg_hint_plan identifies the target object by using aliases if exists. This behavior is usable to point a specific occurrence among multiple occurrences of one table.

postgres=# /*+ HashJoin(t1 t1) */
postgres-# EXPLAIN SELECT * FROM s1.t1
postgres-# JOIN public.t1 ON (s1.t1.id=public.t1.id);
INFO:  hint syntax error at or near "HashJoin(t1 t1)"
DETAIL:  Relation name "t1" is ambiguous.
...
postgres=# /*+ HashJoin(pt st) */
postgres-# EXPLAIN SELECT * FROM s1.t1 st
postgres-# JOIN public.t1 pt ON (st.id=pt.id);
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=64.00..1112.00 rows=28800 width=8)
   Hash Cond: (st.id = pt.id)
   ->  Seq Scan on t1 st  (cost=0.00..34.00 rows=2400 width=4)
   ->  Hash  (cost=34.00..34.00 rows=2400 width=4)
         ->  Seq Scan on t1 pt  (cost=0.00..34.00 rows=2400 width=4)

Hints are not applicable on views itself, but they can affect the queries within if the object names match the object names in the expanded query on the view. Assigning aliases to the tables in a view enables them to be manipulated from outside of the view.

postgres=# CREATE VIEW v1 AS SELECT * FROM t2;
postgres=# EXPLAIN /*+ HashJoin(t1 v1) */
          SELECT * FROM t1 JOIN v1 ON (c1.a = v1.a);
                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Join  (cost=3.27..18181.67 rows=101 width=8)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1  (cost=0.00..14427.01 rows=1000101 width=4)
   ->  Hash  (cost=2.01..2.01 rows=101 width=4)
         ->  Seq Scan on t2  (cost=0.00..2.01 rows=101 width=4)

Hints can point only the parent of inheritance tables and the hint affect all the inheritance. Hints simultaneously point directly to children are not in effect.

One multistatement can have exactly one hint comment and the hint affects all of the individual statements in the multistatement. Notice that the seemingly multistatement on the interactive interface of psql is internally a sequence of single statements so hints affect only the statement just following.

VALUES expressions in the FROM clause are named as “*VALUES*” internally so it is hintable if it is the only VALUES in a query. Two or more VALUES expressions in a query seems distinguishable looking its explain result. But in reality it is mere a cosmetic and they are not distinguishable.

postgres=# /*+ MergeJoin(*VALUES*_1 *VALUES*) */
      EXPLAIN SELECT * FROM (VALUES (1, 1), (2, 2)) v (a, b)
      JOIN (VALUES (1, 5), (2, 8), (3, 4)) w (a, c) ON v.a = w.a;
INFO:  pg_hint_plan: hint syntax error at or near "MergeJoin(*VALUES*_1 *VALUES*) "
DETAIL:  Relation name "*VALUES*" is ambiguous.
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Hash Join  (cost=0.05..0.12 rows=2 width=16)
   Hash Cond: ("*VALUES*_1".column1 = "*VALUES*".column1)
   ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.04 rows=3 width=8)
   ->  Hash  (cost=0.03..0.03 rows=2 width=8)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8)

Index scan may be unexpectedly performed on another index when the index specified in IndexOnlyScan hint cannot perform index-only scan.

NoIndexScan hint involves NoIndexOnlyScan.

A UNION can run in parallel only when all underlying subqueries are parallel-safe. Conversely, enforcing parallel on any of the subqueries lets a parallel-executable UNION run in parallel. Meanwhile, a parallel hint with zero workers inhibits a scan from being executed in parallel.

pg_hint_plan parameters change the behavior of itself so some parameters do not work as expected.

Any syntactical errors or wrong hint names are reported as an syntax error. These errors are reported in the server log with the message level which specified by pg_hint_plan.message_level if pg_hint_plan.debug_print is on or above.

Object misspecifications results silent ignorance of the hints. This kind of error is reported as “not used hints” in the server log by the same condition to syntax errors.

The last hint will be active when redundant hints or hints conflicting with each other. This kind of error is reported as “duplication hints” in the server log by the same condition to syntax errors.

Hint comment cannot include another block comment within. If pg_hint_plan finds it, differently from other errors, it stops parsing and abandons all hints already parsed. This kind of error is reported in the same manner as other errors.

The planner does not try to consider joining order for FROM clause entries more than from_collapse_limit. pg_hint_plan cannot affect joining order as expected for the case.

Planner chooses any executable plans when the enforced plan cannot be executed.

ECPG removes comments in queries written as embedded SQL so hints cannot be passed from those queries. The only exception is that EXECUTE command passes given string unmodified. Please consider hint tables for this case.

pg_stat_statements generates a query ID ignoring comments. As the result, the identical queries with different hints are summarized as the same query.