F.39. pg_hint_plan

F.39.1. Description

pg_hint_plan is a module that allows a user to control an execution plan with hinting phrases mentioned in comments of a special form.

F.39.2. Synopsis

Postgres Pro Enterprise uses a cost-based optimizer, which utilizes data statistics rather than static rules. The planner (optimizer) estimates costs of all possible execution plans for an SQL statement, then the execution plan with the lowest cost is executed. The planner makes every effort to select the best execution plan, but it is not always perfect, since it does not take into account some of the data properties or correlations between columns.

pg_hint_plan makes it possible to tweak execution plans using so-called hints, which are simple descriptions added in SQL comments of a special form.

F.39.3. Overview

F.39.3.1. Basic Usage

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)

F.39.4. Hint Table

The above section mentions that hints are inserted in a comment of a special form. This is inconvenient if a query cannot be edited. For such cases, hints can be placed in a special table called hint_plan.hints, which looks as follows:

ColumnDescription
idA unique number to identify a row for a hint. This column is filled automatically by sequence.
norm_query_string A pattern that matches the query to be hinted. Constants in the query should be replaced with ? as in the example below. Whitespaces are significant in the pattern.
application_name A value of application_name of sessions to apply the hint to. The hint in the example below applies to sessions connected from psql. An empty string means sessions with any application_name.
hint A hint phrase. This must be a series of hints excluding surrounding comment marks.

The following example shows how to work with the hint table.

 INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
     VALUES (
         'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
         '',
         'SeqScan(t1)');
 INSERT 0 1
 UPDATE hint_plan.hints
    SET hints = 'IndexScan(t1)'
  WHERE id = 1;
 UPDATE 1
 DELETE FROM hint_plan.hints
  WHERE id = 1;
 DELETE 1

The hint table is owned by the extension owner and has the same default privileges as at the time of its creation, i.e. during CREATE EXTENSION. Hints in the hint table take priority over the hints specified in comments.

F.39.4.1. Types of Hints

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 F.39.10.

F.39.4.1.1. Hints for Scan Methods

Scan method hints enforce a specific scanning method on the target table. pg_hint_plan recognizes the target table by alias names, if any. They are, for example, SeqScan or IndexScan.

Scan hints work with ordinary tables, inheritance tables, UNLOGGED tables, temporary tables, and system catalogs. External (foreign) tables, table functions, VALUES clause, CTEs, views, and subqueries are not affected.

 /*+
     SeqScan(t1)
     IndexScan(t2 t2_pkey)
  */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
F.39.4.1.2. Hints for Join Methods

Join method hints enforce join methods of the joins involving the specified tables.

This can affect joins only on ordinary tables. Inheritance tables, UNLOGGED tables, temporary tables, external (foreign) tables, system catalogs, table functions, VALUES command results, and CTEs are allowed to be in the parameter list. Joins on views and subqueries are not affected.

Join method hints are recommended to be used together with the Leading joining order hint because it guarantees that a joining order specified in the request will be executed.

The hint below prompts the NestLoop(MergeJoin(c b) a) joining structure:

/*+ 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)
F.39.4.1.3. Hint for Joining Order

The Leading hint enforces the joining order for two or more tables. There are two ways of enforcing. One is enforcing specific joining order but not restricting direction at each join level:

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

Another way also enforces join direction. When the above join order is specified, the join direction chosen by the planner (outer table — inner table) may be different from the expected one. If you want to change the join direction in such a situation, use the following format:

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

In this format, two elements enclosed in parentheses are nested, and within one parenthesis, the first element is the outer table, and the second element is the inner table.

Note that the Leading hint (just like join methods) does not operate together with GEQO if the number of tables specified in the request exceeds geqo_threshold.

Some extra examples of using the hint for joining order:

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

And an example below:

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

For details, see Section F.39.10.

F.39.4.1.4. Hints for Controlling Join Behavior

The Memoize hint allows the join to memoize the inner result, and the NoMemoize hint disallows it. In the example below, the NoMemoize hint prohibits the topmost hash join from memoizing the result of the table 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)

F.39.4.1.5. Hint for Row Number Correction

The Rows hint corrects the row number misestimation of joins that comes from restrictions in the planner. For example:

 /*+ 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.
F.39.4.1.6. Hint for Parallel Plans

The Parallel hint enforces parallel execution configuration on scans. The third parameter specifies the strength of enforcement. soft means that pg_hint_plan only changes max_parallel_workers_per_gather and leaves all others to the planner. hard changes other planner parameters so as to forcibly apply the number. This can affect ordinary tables, inheritance parents, UNLOGGED tables, and system catalog. External tables, table functions, VALUES clause, CTEs, views, and subqueries are not affected. Internal tables of a view can be specified by its real name or its alias as the target object. The following example shows that the query is enforced differently on each table.

 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)
F.39.4.1.7. Hints for Stored Procedures

Hints can be used with stored procedures. Be careful when using hints, since they can be inherited.

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
F.39.4.1.8. Hints for Prepared Statements

The pg_hint_plan extension allows using hints with prepared statements. Hints should be specified in the PREPARE statement and are ignored in the EXECUTE statement.

Below are a couple of sample queries. With the IndexOnlyScan(t1) hint:

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

And with the BitmapScan(t1) hint:

 /*+ 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)
F.39.4.1.9. Setting GUC Parameters During Planning

The Set hint changes GUC parameters during planning. The GUC parameter shown in Section 19.7.2 can have the expected effects on planning unless any other hint conflicts with the planner method configuration parameters. When multiple hints change the same GUC, the last hint takes effect. GUC parameters for pg_hint_plan are also settable by this hint, but it may not work as you expect.

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

F.39.4.2. GUC Parameters for pg_hint_plan

GUC parameters described below affect the behavior of pg_hint_plan.

Table F.31. GUC Parameters

Parameter NameDescriptionDefault Value
pg_hint_plan.enable_hintTrue enables pg_hint_plan.on
pg_hint_plan.enable_hint_tableTrue enables hinting by table.off
pg_hint_plan.parse_messagesSpecifies the log level of hint parse error. Valid values are error, warning, notice, info, log, debug.info
pg_hint_plan.debug_print Controls debug print and verbosity. Valid values are off, on, detailed, and verbose. off
pg_hint_plan.message_level Specifies message level of debug print. Valid values are error, warning, notice, info, log, debug. log
pg_hint_plan.hints_anywhere If it is on, pg_hint_plan reads hints ignoring SQL syntax. This allows placing hints anywhere in the query but may cause false reads. off

F.39.5. Installation

The pg_hint_plan extension is provided together with Postgres Pro Enterprise as a separate pre-built package. For detailed package installation instructions, see Chapter 17. Once you have Postgres Pro Enterprise installed, activate pg_hint_plan:

LOAD 'pg_hint_plan';
LOAD

You can also load it globally by adding pg_hint_plan to the shared_preload_libraries parameter in the postgresql.conf file.

shared_preload_libraries = 'pg_hint_plan'

For automatic loading for specific sessions, use ALTER USER SET/ALTER DATABASE SET.

pg_hint_plan does not require CREATE EXTENSION, but if you plan to use the hint table, create the extension and enable the enable_hint_table parameter:

CREATE EXTENSION pg_hint_plan;
SET pg_hint_plan.enable_hint_table TO on;

F.39.6. Details in Hinting

F.39.6.1. Syntax and Placement

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)

F.39.6.2. Using with PL/pgSQL

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

  • Hints affect only the following kinds 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 should be placed after the first word in a query as in the example below, since preceding comments are not sent as a part of the 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;

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

F.39.6.4. Escaping Special Characters in Object Names

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.

F.39.6.5. Distinction Between Multiple Occurrences of a Table

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)

F.39.6.6. Underlying Tables of Views or Rules

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

F.39.6.7. Inheritance

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.

F.39.6.8. Hinting in Multistatements

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

F.39.6.9. VALUES Expressions

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)

F.39.7. Subqueries

Subqueries in the following context can be hinted using the ANY_subquery name:

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

For these syntaxes, the planner internally assigns a 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)

F.39.7.1. Using the IndexOnlyScan Hint

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

F.39.7.2. The NoIndexScan Hint

The NoIndexScan hint implies NoIndexOnlyScan.

F.39.7.3. Parallel Hints and UNION

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.

F.39.7.4. Setting pg_hint_plan Parameters by Set Hints

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

  • 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 starts working in the middle of the query processing.

F.39.8. Errors

pg_hint_plan stops parsing on any error and uses the already parsed hints. Typical errors are listed below.

F.39.8.1. Syntax Errors

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.

F.39.8.2. Incorrect Object Definitions

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

F.39.8.3. Redundant or Conflicting Hints

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.

F.39.8.4. Nested Comments

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.

F.39.9. Functional Limitations

F.39.9.1. Influences of Planner GUC Parameters

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.

F.39.9.2. Hints Trying to Enforce Non-Executable Plans

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

  • FULL OUTER JOIN to use nested loop.

  • Use of indexes that do not have columns used in quals.

  • TID scans for queries without ctid conditions.

F.39.9.3. Queries in ECPG

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.

F.39.9.4. pg_stat_statements

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

F.39.10. Available Hints

The available hints are listed below.

Table F.32. Hints List

GroupFormatDescription
Scan methodSeqScan(table)Forces the sequential scan on a table.
 TidScan(table)Forces the TID scan on a table.
 IndexScan(table[ index...])Forces the index scan on a table. Restricts to specified indexes, if any.
 IndexOnlyScan(table[ index...]) Forces the index-only scan on a table. Restricts to specified indexes, if any. The index scan may be used if the index-only scan is not available.
 BitmapScan(table[ index...])Forces the bitmap scan on a table. Restricts to specified indexes, if any.
 IndexScanRegexp(table[ POSIX regexp...])Forces the index scan on a table. Restricts to indexes that match the specified POSIX regular expression.
 IndexOnlyScanRegexp(table[ POSIX regexp...])Forces the index-only scan on a table. Restricts to indexes that match the specified POSIX regular expression.
 BitmapScanRegexp(table[ POSIX regexp...])Forces the bitmap scan on a table. Restricts to indexes that match the specified POSIX regular expression.
 NoSeqScan(table)Forces the deactivation of the sequential scan on a table.
 NoTidScan(table)Forces the deactivation of the TID scan on a table.
 NoIndexScan(table)Forces the deactivation of the index scan and index-only scan on a table.
 NoIndexOnlyScan(table) Forces the deactivation of the index-only scan on a table.
 NoBitmapScan(table)Forces the deactivation of the bitmap scan on a table.
Join methodNestLoop(table table[ table...])Forces the nested loop for joins with specified tables.
 HashJoin(table table[ table...])Forces the hash join for joins with specified tables.
 MergeJoin(table table[ table...])Forces the merge join for joins with specified tables.
 NoNestLoop(table table[ table...])Forces the deactivation of the nested loop for joins with specified tables.
 NoHashJoin(table table[ table...])Forces the deactivation of the hash join for joins with specified tables.
 NoMergeJoin(table table[ table...])Forces the deactivation of the merge join for joins with specified tables.
Join orderLeading(table table[ table...])Forces the join order as specified.
 Leading(<join pair>) Forces the join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed in parentheses, which can make a nested structure.
Control join behaviorMemoize(table table[ table...]) Allows the topmost join of the joins involving the specified tables to memoize the inner result. Note that it does not enforce memoizing.
 NoMemoize(table table[ table...]) Disallows the topmost join of the joins involving the specified tables to memoize the inner result.
Row number correctionRows(table table[ table...] correction) Corrects row number of a result of the joins with the specified tables. The available correction methods are absolute (#<n>), addition (+<n>), subtraction (-<n>) and multiplication (*<n>). <n> should be a string that strtod() can read.
Parallel query configurationParallel(table <# of workers> [soft|hard]) Enforces or inhibits parallel execution of the specified table. <# of workers> is the desired number of parallel workers, where zero means inhibiting parallel execution. If the third parameter is soft (default), it only changes max_parallel_workers_per_gather and leaves everything else to the planner. The hard value enforces the specified number of workers.
GUCSet(GUC-parameter value)Sets the GUC parameter to the value while the planner is running.

F.39.11. See Also

EXPLAIN, SET, Chapter 19, Section 15.3