Details in hinting

Syntax and placement

pg_hint_plan reads hints from only the first block comment and stops parsing from 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)

Using with PL/pgSQL

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

  • Hints affect only on the following kind of queries:
    • Queries that return one row (SELECT, INSERT, UPDATE and DELETE)
    • Queries that return multiple rows (RETURN QUERY)
    • Dynamic SQL statements (EXECUTE)
    • Cursor open (OPEN)
    • Loop over result of a query (FOR)
  • A hint comment has to be placed after the first word in a query as preceding comments are not sent as a part of this query.
=# 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;

Upper and lower case handling in object names

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 the database and does not match any unquoted names like TBL, tbl or Tbl.

Escaping special characters in object names

The objects defined in a hint’s parameter can use double quotes if they include parentheses, double quotes and white spaces. The escaping rules are the same as PostgreSQL.

Distinction between multiple occurences of a table

pg_hint_plan identifies the target object by using aliases if any. This behavior is useful to point to 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)

Underlying tables of views or rules

Hints are not applicable on views, but they can affect the queries within the view if the object names match the names in the expanded query on the view. Assigning aliases to the tables in a view enables them to be manipulated from outside 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)

Inheritance

Hints can only point to the parent of an inheritance tree and the hints affect all the tables in an inheritance tree. Hints pointing directly to inherited children have no effect.

Hints in multistatements

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

VALUES expressions

VALUES expressions in FROM clause are named as *VALUES* internally these can be hinted if it is the only VALUES of a query. Two or more VALUES expressions in a query cannot be distinguished by looking at an EXPLAIN result, resulting in ambiguous results:

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

Subqueries

Subqueries context can be occasionally hinted using the name ANY_subquery:

IN (SELECT ... {LIMIT | OFFSET ...} ...)
= ANY (SELECT ... {LIMIT | OFFSET ...} ...)
= SOME (SELECT ... {LIMIT | OFFSET ...} ...)

For these syntaxes, the planner internally assigns the name to the subquery when planning joins on tables including it, so join hints are applicable on such joins using the implicit name. For example:

=# /*+HashJoin(a1 ANY_subquery)*/
   EXPLAIN SELECT *
     FROM pgbench_accounts a1
   WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
                                         QUERY PLAN

---------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=0.49..2903.00 rows=1 width=97)
   Hash Cond: (a1.aid = a2.bid)
   ->  Seq Scan on pgbench_accounts a1  (cost=0.00..2640.00 rows=100000 width=97)
   ->  Hash  (cost=0.36..0.36 rows=10 width=4)
         ->  Limit  (cost=0.00..0.26 rows=10 width=4)
               ->  Seq Scan on pgbench_accounts a2  (cost=0.00..2640.00 rows=100000 width=4)

Using IndexOnlyScan hint

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

About NoIndexScan

A NoIndexScan hint implies NoIndexOnlyScan.

Parallel hints and UNION

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

Setting pg_hint_plan parameters by Set hints

pg_hint_plan parameters influence their own behavior so some parameters will not work as one could expect:

  • Hints to change enable_hint, enable_hint_table are ignored even though they are reported as “used hints” in debug logs.
  • Setting debug_print and message_level in the middle of query processing.