pg_hint_plan reads hinting phrases in comments of a special form given with the target SQL statement. The special comment begins with the character sequence /*+ and ends with */. Hinting phrases consist of a hint name and following parameters enclosed in parentheses and delimited by whitespaces. Each hinting phrase can be delimited by new lines for readability.

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

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

Hinting phrases are classified into several types depending on the kind of object and how they can affect the planner. For more details, see Section H.2.10.

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

/*+ Leading(((c b) a)) MergeJoin(c b) NestLoop(a b c) */ EXPLAIN(COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM t1 a, t1 b, t1 c WHERE a.f1 = b.f1 AND b.f1 = c.f1;

                          QUERY PLAN
---------------------------------------------------------------
 Aggregate
   ->  Nested Loop
         ->  Merge Join
               Merge Cond: (c.f1 = b.f1)
               ->  Index Only Scan using t1_idx1 on t1 c
               ->  Materialize
                     ->  Index Only Scan using t1_idx1 on t1 b
         ->  Memoize
               Cache Key: b.f1
               Cache Mode: logical
               ->  Index Only Scan using t1_idx1 on t1 a
                     Index Cond: (f1 = b.f1)

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

 /*+ Leading ((t1 (t2 t3))) * / SELECT ...

/*+ Leading(((c b) a)) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM t1 a, t1 b, t1 c WHERE a.f1 = b.f1 AND b.f1 = c.f1;
                          QUERY PLAN
---------------------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (b.f1 = a.f1)
         ->  Nested Loop
               ->  Index Only Scan using t1_idx1 on t1 c
               ->  Memoize
                     Cache Key: c.f1
                     Cache Mode: logical
                     ->  Index Only Scan using t1_idx1 on t1 b
                           Index Cond: (f1 = c.f1)
         ->  Hash
               ->  Seq Scan on t1 a

/*+ Leading(((d c) (b a))) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM t1 a, t1 b, t1 c, t1 d WHERE a.f1 = b.f1 AND b.f1 = c.f1 AND c.f1 = d.f1;

                          QUERY PLAN
---------------------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (c.f1 = a.f1)
         ->  Nested Loop
               ->  Index Only Scan using t1_idx1 on t1 d
               ->  Memoize
                     Cache Key: d.f1
                     Cache Mode: logical
                     ->  Index Only Scan using t1_idx1 on t1 c
                           Index Cond: (f1 = d.f1)
         ->  Hash
               ->  Hash Join
                     Hash Cond: (b.f1 = a.f1)
                     ->  Seq Scan on t1 b
                     ->  Hash
                           ->  Seq Scan on t1 a

/*+ NoMemoize(a b) */
EXPLAIN SELECT * FROM a, b WHERE a.val = b.val;
                             QUERY PLAN
--------------------------------------------------------------------
 Hash Join  (cost=270.00..1412.50 rows=100000 width=16)
   Hash Cond: (b.val = a.val)
   ->  Seq Scan on b  (cost=0.00..15.00 rows=1000 width=8)
   ->  Hash  (cost=145.00..145.00 rows=10000 width=8)
         ->  Seq Scan on a  (cost=0.00..145.00 rows=10000 width=8)

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

 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)

 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)

CREATE OR REPLACE FUNCTION test_1() RETURNS bool AS $$
BEGIN
EXECUTE 'SELECT count(*) FROM t1 WHERE f1 < 2' ;
RETURN true;
END; $$ language plpgsql;

CREATE OR REPLACE FUNCTION test_2() RETURNS void AS $$
BEGIN
EXECUTE 'SELECT /*+ SET(enable_bitmapscan off)*/ test_1()' ;
END;
$$ language plpgsql;

 SELECT test_2();

    Query Text: SELECT count(*) FROM t1 WHERE f1 < 2
    Aggregate  (cost=18.00..18.01 rows=1 width=8) (actual time=0.511..0.512 rows=1 loops=1)
      ->  Seq Scan on t1  (cost=0.00..17.50 rows=200 width=0) (actual time=0.105..0.457 rows=200 loops=1)
            Filter: (f1 < 2)
            Rows Removed by Filter: 800

/*+ IndexOnlyScan(t1) */ PREPARE stmt AS SELECT count(*) FROM t1 WHERE f1 < 2;

EXPLAIN EXECUTE stmt;
EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE stmt;
                QUERY PLAN
-------------------------------------------
 Aggregate
   ->  Index Only Scan using t1_idx1 on t1
         Index Cond: (f1 < 2)

 /*+ BitmapScan(t1) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE stmt;
                QUERY PLAN
-------------------------------------------
 Aggregate
   ->  Index Only Scan using t1_idx1 on t1
         Index Cond: (f1 < 2)

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

GUC parameters described below affect the behavior of pg_hint_plan.

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

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 /*+ IndexScan(a) */
 EXPLAIN SELECT /*+ MergeJoin(a b) */ *
    FROM pgbench_branches b
    JOIN pgbench_accounts a ON b.bid = a.bid
    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)

However, when the hints_anywhere parameter is on, pg_hint_plan reads hints from anywhere in the query so the following hint uses would be equivalent:

EXPLAIN /*+ SeqScan(t1)*/
SELECT * FROM table1 t1 WHERE a < 10;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on table1 t1  (cost=0.00..17.50 rows=9 width=8)
   Filter: (a < 10)
(2 rows)


EXPLAIN
SELECT * FROM table1 t1 WHERE a < 10 AND '/*+SeqScan(t1)*/' <> '';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on table1 t1  (cost=0.00..17.50 rows=9 width=8)
   Filter: (a < 10)
(2 rows)


EXPLAIN
SELECT * FROM table1 t1 WHERE a < 10 /*+SeqScan(t1)*/;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on table1 t1  (cost=0.00..17.50 rows=9 width=8)
   Filter: (a < 10)
(2 rows)

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

 CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
 DECLARE
     id  integer;
     cnt integer;
 BEGIN
     SELECT /*+ NoIndexScan(a) */ aid
         INTO id FROM pgbench_accounts a WHERE aid = $1;
     SELECT /*+ SeqScan(a) */ count(*)
         INTO cnt FROM pgbench_accounts a;
     RETURN id + cnt;
 END;
 $$ 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 manner. 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 defined as the hint parameter should be enclosed in double quotes if they include parentheses, double quotes, and whitespaces. The escaping rules are the same as in PostgreSQL.

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

 /*+ HashJoin(t1 t1) */
 EXPLAIN SELECT * FROM s1.t1
  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.
...
 /*+ HashJoin(pt st) */
 EXPLAIN SELECT * FROM s1.t1 st
  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 themselves, but they can affect the queries within the view if the object names match the object names in the expanded query on the view. Assigning aliases to the tables in the view enables them to be manipulated from outside of the view.

 CREATE VIEW v1 AS SELECT * FROM t2;
 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)

And one more example:

CREATE VIEW v1 AS SELECT count(*) FROM t1 WHERE f1 < 2;
/*+ IndexOnlyScan(t1) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM v1;
                QUERY PLAN
-------------------------------------------
 Aggregate
   ->  Index Only Scan using t1_idx1 on t1
         Index Cond: (f1 < 2)

Be careful not to select tables with identical names in different views because these tables may become affected. To avoid this, try using unique aliases, for example, a combination of a view name and a table name:

/*+ SeqScan(t1) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM v2;

             QUERY PLAN
-------------------------------------
 Nested Loop Semi Join
   Join Filter: ((count(*)) = t1.f1)
   ->  Aggregate
         ->  Seq Scan on t1 t1_1
               Filter: (f1 < 2)
   ->  Seq Scan on t1

Hints can point only at the parent of an inheritance tree, yet they affect all the tables in the inheritance tree. Hints pointing directly at children will not take effect.

One multistatement can have exactly one hint comment and this hint affects all individual statements in the multistatement.

VALUES expressions in the FROM clause are named as *VALUES* internally, so they are hintable if it is the only VALUES expression in a query. Two or more VALUES expressions in a query cannot be distinguished by looking at EXPLAIN, which makes the results ambiguous:

/*+ 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)

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

The NoIndexScan hint implies NoIndexOnlyScan.

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

pg_hint_plan parameters influence the behavior of the module, so some parameters do not work as expected:

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

Hints with incorrect object definitions are simply ignored. This kind of error is reported as “Not Used Hint” in the server log.

The last hint will take effect in case of redundant or conflicting hints. This kind of error is reported as “Duplication Hint” in the server log under the same conditions as syntax errors.

A hint comment cannot be recursive. If pg_hint_plan finds it, hint parsing is immediately stopped and all the hints already parsed are ignored.

The planner does not try to consider the joining order for the FROM clause entries more than from_collapse_limit. In such cases pg_hint_plan cannot affect the joining order.

The planner selects any executable plan when the enforced plan cannot be executed.

ECPG removes comments from queries written as embedded SQL, so hints cannot be passed to it. The only exception is the EXECUTE command that passes the query string to the server as is. The hint table can be used in this case.

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