You can use the pg_hint_plan extension to add hints to SQL statements. The hints specify how you want the SQL statements to be executed. This way, you can optimize the execution plans of the SQL statements.

Background information

PostgreSQL uses a cost-based optimizer that utilizes data statistics rather than static rules. The optimizer evaluates the costs of all possible execution plans for an SQL statement, and executes the execution plan with the lowest cost. The optimizer makes the best effort, but the selected execution plan may not be the best plan, because the optimizer does not consider the underlying relationships among data.

You can specify Grand Unified Scheme (GUC) variables to adjust the execution plan, but this would affect the entire session. If you do not want to affect the entire session, you can use the pg_hint_plan to optimize a single execution plan.

Precautions

  • Data Management (DMS) does not support hints. You must use other methods to access your database.
  • pg_hint_plan reads hints from only the first comment block.
  • pg_hint_plan accepts only letters, digits, spaces, and the following special characters: _ , ( ) Other characters stop the parsing immediately.
  • pg_hint_plan compares object names in a case-sensitive way, which is different from how PostgreSQL compares object names. For example, an object named TBL in a hint matches only TBL in a database. Objects named tbl or Tbl are ignored.

Limitations

The use of pg_hint_plan for storage procedure defined by using PL/pgSQL is subject to the following limitations:
  • Hints take effect only for the following types of statements:
    • SELECT, INSERT, UPDATE, and DELETE statements.
    • RETURN QUERY statements.
    • EXECUTE QUERY statements.
    • OPEN statements.
    • FOR statements.
  • A hint must be placed immediately after the first word of an SQL statement. If the hint is placed before the first word, the hint is not considered part of the query.

Create and load the pg_hint_plan extension

  1. Create the extension. 
    CREATE EXTENSION pg_hint_plan;
  2. Load the extension.
    • Automatically load the extension for a single user.
      • Execute the following statement to load the extension. 
        ALTER USER xxx set session_preload_libraries='pg_hint_plan';
        Note Replace xxx in the statement with the actual username.
      • Execute the following statement to load the extension for a single database. 
        ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
      Note If the account cannot log on to the database due to improper configurations, execute one of the following statements to log on to PolarDB through another account or database and reset the configurations.
      ALTER USER xxx reset session_preload_libraries;
ALTER DATABASE xxx reset session_preload_libraries;
    • Automatically load the extension for a database cluster.

      To use the pg_hint_plan extension, go to the Quota Center. Click Apply in the Actions column corresponding to polardb_pg_pg_hint_plan.

    • Check whether the extension is loaded.
      1. Execute the following statements to allow debugging information to be sent to the client. 
        SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
      2. Execute the following statement to check whether the extension is loaded. 
        /*+Set(enable_seqscan 1)*/select 1;
        If the extension is loaded, the following information is returned. 
        NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
      3. Execute the following statements to stop sending debugging information to the client. 
        RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;

Usage notes

  • Basic usage

    A hint starts with the combination of a forward slash, an asterisk, and a plus sign (/*+) and ends with the combination of an asterisk and a forward slash (*/). A hint consists of the hint name and the parameters. The parameters are enclosed in a pair of parentheses () and are separated by spaces. For readability purposes, you can start each hint in a new line.

    Example

    In this example, HashJoin is used as the joining method, and the pgbench_accounts table is scanned by using the SeqScan 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;
    The following result is returned:
                                          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)
  • Hint table
    Hints can be used to optimize the execution plans of SQL statements. However, this is convenient only when SQL statements are editable. If SQL statements are not editable, you can place hints in a table named hint_plan.hints. The table consists of the following columns.
    ColumnDescription
    idThe ID of the hint. The ID is unique and is automatically generated.
    norm_query_stringThe pattern that matches the SQL statement to which you want to add the hint. The constants in the SQL statement must be replaced by a question mark (?) that serves as a wildcard. Space characters are necessary parts of the pattern.
    application_nameThe name of the application to which the hint is applied. If this parameter is left empty, the hint is applied to all applications.
    hintsThe comment that contains the hint. You do not need to include comment marks.
    The following statement shows an example of a hint table. By default, the user who creates the pg_hint_plan extension has the permissions on the hint table. When hints are added to both a statement comment and the hint table, the hints in the hint table take precedence over the hints in the comment.
    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

Hint types

  • Hint types

    Hints are classified into the following six types based on how they affect execution plans:

    • Hints for scan methods

      This type of hint specifies the method that is used to scan the specified table. If the specified table has an alias, the pg_hint_plan extension identifies the table based on the alias. Supported scan methods include SeqScan, IndexScan, and so on.

      The hints for scan methods are effective on ordinary tables, inherited tables, unlogged tables, temporary tables, and system tables. However, the hints for scan methods are not effective on external tables, table functions, statements in which the values of constants are specified, universal expressions, views. and subqueries.

      Example:
      /*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
    • Hints for join methods

      This type of hint specifies the method that is used to join the specified tables. The hints for join methods are effective on ordinary tables, inherited tables, unlogged tables, temporary tables, external tables, system tables, table functions, statements in which the values of constants are specified, and universal expressions. The hints for join methods are not effective on views and subqueries.

    • Hints for join order
      This type of hint specifies the order in which two or more tables are joined. You can use one of the following methods to specify a hint for join order:
      • Specify the order in which you want to join the specified tables without restricting the direction at each join level.
      • Specify the order in which you want to join the specified tables and the direction at each join level.
      Example:
       /*+
    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);
      Note Components:
      • NestLoop(t1 t2): specifies the method for joining t1 and t2 tables.
      • MergeJoin(t1 t2 t3): specifies the method for joining t1, t2, and t3 tables.
      • Leading(t1 t2 t3): specifies the order in which the three tables are joined.
    • Hints for row number correction

      This type of hint corrects row number errors that are caused by the restrictions of the optimizer. 

       /*+ Rows(a b #10) */ SELECT... ; # Sets the row number of join results to 10.
 /*+ Rows(a b +10) */ SELECT... ; # Increases the row number by 10.
 /*+ Rows(a b -10) */ SELECT... ; # Decreases the row number by 10.
 /*+ Rows(a b *10) */ SELECT... ; # Increases the row number by 10 times.
    • Hints for parallel execution

      This type of hint specifies the plan that is used to execute SQL statements in parallel.

      The hints for parallel execution are effective on ordinary tables, inherited tables, unlogged tables, and system tables. However, the hints for parallel execution are not affective on external tables, clauses in which the values of constants are specified, universal expressions, views, and subqueries. You can specify the internal tables of a view by their real names or aliases.

      The following examples show how an SQL statement is executed in a different way on each table:
      • Example 1: Set the degree of parallelism (DOP) for the c1 table to 3, and that for the c2 table to 5. 
        EXPLAIN /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
        The following result is returned:
                                          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 the t1 table to 5. 
        EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
        The following result is returned:
                                            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

      This type of hint temporarily changes the value of a GUC parameter. The values of GUC parameters take effect only when the executor generates execution plans. The values help you improve the query performance without affecting the entire session. If you set more than one hint on a GUC parameter, the latest hint takes effect.

      Example:
       /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
  • List of hint syntaxes
    The following table lists all supported hint syntaxes. You can add the hints as comments of your queries to server specific purposes. Optional parameters are enclosed in pairs of brackets ([ ]) in the syntax.
    TypeHint syntaxDescription
    Hints for scan methodsSeqScan(table)Specifies a sequence scan.
    TidScan(table)Specifies a TID scan.
    IndexScan(table[ index...])Specifies an index scan. You can specify an index.
    IndexOnlyScan(table[ index...])Specifies an index-only scan. You can specify an index.
    BitmapScan(table[ index...])Specifies a bitmap scan. You can specify an index.
    NoSeqScan(table)Prohibits a sequence scan.
    NoTidScan(table)Prohibits a TID scan.
    NoIndexScan(table)Prohibits an index scan.
    NoIndexOnlyScan(table)Prohibits an index scan. Only tables are scanned.
    NoBitmapScan(table)Prohibits a bitmap scan.
    Hints for join methodsNestLoop(table table[ table...])Specifies a nested loop join.
    HashJoin(table table[ table...])Specifies a hash join.
    MergeJoin(table table[ table...])Specifies a merge join.
    NoNestLoop(table table[ table...])Prohibits a nested loop join.
    NoHashJoin(table table[ table...])Prohibits a hash join.
    NoMergeJoin(table table[ table...])Prohibits a merge join.
    Hints for join orderLeading(table table[ table...])Specifies the join order.
    Leading(<join pair>)Specifies the join order and direction.
    Hints for row number correctionRows(table table[ table...] correction)Corrects the row number of the join result that is obtained from the specified tables. The following operators are supported: #<n>, + <n>, -<n>, and * <n>. The <n> operator is supported by the strtod function.
    Hints for parallel executionParallel(table <# of workers> [soft|hard])Specifies or prohibits the parallel execution of the specified tables.
    Note
    • The <worker#> parameter specifies the number of working programs that are required. The value 0 specifies to prohibit parallel execution.
    • If the third parameter is set to soft, only the value of the max_parallel_workers_per_gather parameter is changed and the other parameters are specified by the optimizer.
    • If the third parameter is set to hard, the values of all related parameters are changed. The third parameter is set to soft by default.
    PX(<# of workers>)Specifies a cross-node parallel execution.
    Note <# of workers> specifies the DOP.
    NoPX()Prohibits a cross-node parallel execution.
    Hints for GUC parameter settingSet(GUC-param value)Specifies the value of a GUC parameter when the optimizer runs.
    Note pg_hint_plan can also specify the execution plan generated during a cross-node parallel execution. During a cross-node parallel execution, the Rows(table table[ table...] correction) hint is not supported. The hints for join methods can be used only to join two tables, and the hints for join order can be used only for all tables.