Optimize YSQL queries using pg_hint_plan

YugabyteDB leverages the PostgreSQL pg_hint_plan extension to control query execution plans with hinting phrases using comments.

YugabyteDB uses PostgreSQL's cost-based optimizer, which estimates the costs of each possible execution plan for an SQL statement. The execution plan with the lowest cost is executed. The planner does its best to select the best execution plan, but is not perfect. Additionally, the version of the planner used by YugabyteDB is sub-optimal. For instance, the cost-based optimizer is naive and assumes row counts for all tables to be 1000. Row counts however play a crucial role in calculating the cost estimates. To overcome these limitations, you can use pg_hint_plan.

pg_hint_plan makes it possible to tweak execution plans using "hints", which are descriptions in the form of SQL comments.

Revisit your hint plans

To use pg_hint_plan effectively, you need thorough knowledge of how your application will be deployed. Hint plans also need to be revisited when the database grows or the deployment changes to ensure that the plan is not limiting performance rather than optimizing it.

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

Configure pg_hint_plan

pg_hint_plan is pre-configured, and enabled by default. The following YSQL parameters control pg_hint_plan:

Option	Description	Default
`pg_hint_plan.enable_hint`	Turns pg_hint_plan on or off.	on
`pg_hint_plan.debug_print`	Controls debug output. Valid values are `off` (no debug output), `on`, `detailed`, and `verbose`.	off
`pg_hint_plan.message_level`	Specifies the minimum message level for debug output. In decreasing order of severity, the levels are: `error`, `warning`, `notice`, `info`, `log`, and `debug`. Messages at the `fatal` and `panic` levels are always included in the output.	info

Enable pg_hint_plan

To enable pg_hint_plan, run the following command:

yugabyte=# SET pg_hint_plan.enable_hint=ON;

Enable pg_hint_plan for all sessions

You can enable pg_hint_plan in different levels like all PostgreSQL options can.

Turn on debug output

To view the specific hints that pg_hint_plan uses and forwards to the query planner, turn on debug output. This is helpful for situations where syntactical errors or wrong hint names are present in hint phrases. To view these debug prints, run the following commands:

yugabyte=# SET pg_hint_plan.debug_print TO on;
yugabyte=# \set SHOW_CONTEXT always
yugabyte=# SET client_min_messages TO log;

Writing hint plans

pg_hint_plan parses hinting phrases of a special form present in SQL statements. This special form begins with the character sequence /*+ and ends with */. Hint phrases consist of hint names followed by hint parameters enclosed in parentheses and delimited by spaces.

In the following example, HashJoin is selected as the joining method for joining pg_bench_branches and pg_bench_accounts and a SeqScan is used for scanning the table pgbench_accounts.

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

The following table and index definitions are used in the examples that follow to illustrate the features of pg_hint_plan:

CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE t2 (id int PRIMARY KEY, val int);
CREATE TABLE t3 (id int PRIMARY KEY, val int);

INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;
INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;

CREATE INDEX t1_val ON t1 (val);
CREATE INDEX t2_val ON t2 (val);
CREATE INDEX t3_id1 ON t3 (id);
CREATE INDEX t3_id2 ON t3 (id);
CREATE INDEX t3_id3 ON t3 (id);
CREATE INDEX t3_val ON t3 (val);

The schema of the resulting tables is as follows:

                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 val    | integer |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, lsm (id HASH)
    "t1_val" lsm (val HASH)

                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 val    | integer |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, lsm (id HASH)
    "t2_val" lsm (val HASH)

                 Table "public.t3"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 val    | integer |           |          |
Indexes:
    "t3_pkey" PRIMARY KEY, lsm (id HASH)
    "t3_id1" lsm (id HASH)
    "t3_id2" lsm (id HASH)
    "t3_id3" lsm (id HASH)
    "t3_val" lsm (val HASH)

Hints for scan methods

Scan method hints enforce the scanning method on tables when specified along with appropriate hint phrases. You should specify your required scan method and respective target tables by alias names if any. YugabyteDB supports the following scan methods with hint phrases from pg_hint_plan:

Option	Value
SeqScan(table)	Enable SeqScan on the table.
NoSeqScan(table)	Do not enable SeqScan on the table.
IndexScan(table)	Enable IndexScan on the table.
IndexScan(table idx)	Enable IndexScan on the table using the index `idx`.
NoIndexScan(table)	Do not enable IndexScan on the table.
IndexOnlyScan(table)	Enable IndexOnlyScan on the table.
NoIndexOnlyScan(table)	Do not enable IndexOnlyScan on the table.
IndexScanRegexp(table regex)	Enable index scan on the table whose indices match with the regular expression defined by `regex`.
IndexOnlyScanRegexp(table regex)	Do not enable index scan on the table whose indices match with the regular expression defined by `regex`.

In the following example, the hint /*+SeqScan(t2)*/ allows table t2 to be scanned using SeqScan.

/*+SeqScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

LOG:  pg_hint_plan:
used hint:
SeqScan(t2)
not used hint:
duplication hint:
error hint:

              QUERY PLAN
--------------------------------------
 Nested Loop
   ->  Seq Scan on t2
   ->  Index Scan using t1_pkey on t1
         Index Cond: (id = t2.id)
(4 rows)

In the following example, due to the hint /*+SeqScan(t1)IndexScan(t2)*/, t2 is scanned using IndexScan, and t1 is scanned using SeqScan.

/*+SeqScan(t1)IndexScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

LOG:  pg_hint_plan:
used hint:
SeqScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

              QUERY PLAN
--------------------------------------
 Nested Loop
   ->  Seq Scan on t1
   ->  Index Scan using t2_pkey on t2
         Index Cond: (id = t1.id)
(4 rows)

You can also use hint phrases to instruct the query planner not to use a specific type of scan. As shown in the following example, the hint /*+NoIndexScan(t1)*/ restricts IndexScan on table t1.

/*+NoIndexScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

LOG:  pg_hint_plan:
used hint:
NoIndexScan(t1)
not used hint:
duplication hint:
error hint:

              QUERY PLAN
--------------------------------------
 Nested Loop
   ->  Seq Scan on t1
   ->  Index Scan using t2_pkey on t2
         Index Cond: (id = t1.id)
(4 rows)

Specifying indices in hint phrases

A single table can have many indices. Using pg_hint_plan, you can specify the exact index to use while performing scans on a table. Consider table t3 as an example. It contains multiple indices for the column id (t3_pkey, t3_id1, t3_id2, t3_id3). You can choose any of these indices to run scans by executing the SQL queries with the appropriate hint phrases.

Query without a hint phrase:

yugabyte=# EXPLAIN (COSTS false) SELECT * FROM t3 WHERE t3.id = 1;

           QUERY PLAN
--------------------------------
 Index Scan using t3_pkey on t3
   Index Cond: (id = 1)
(2 rows)

Query using a secondary index:

/*+IndexScan(t3 t3_id2)*/
EXPLAIN (COSTS false) SELECT * FROM t3 WHERE t3.id = 1;

LOG:  available indexes for IndexScan(t3): t3_id2
LOG:  pg_hint_plan:
used hint:
IndexScan(t3 t3_id2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN
-------------------------------
 Index Scan using t3_id2 on t3
   Index Cond: (id = 1)
(2 rows)

Query reverts to SeqScan as none of the indices can be used:

/*+IndexScan(t3 no_exist)*/
EXPLAIN (COSTS false) SELECT * FROM t3 WHERE t3.id = 1;

LOG:  available indexes for IndexScan(t3):
LOG:  pg_hint_plan:
used hint:
IndexScan(t3 no_exist)
not used hint:
duplication hint:
error hint:

     QUERY PLAN
--------------------
 Seq Scan on t3
   Filter: (id = 1)
(2 rows)

Query with a selective list of indexes:

/*+IndexScan(t3 t3_id1 t3_id2)*/
EXPLAIN (COSTS false) SELECT * FROM t3 WHERE t3.id = 1;

LOG:  available indexes for IndexScan(t3): t3_id2 t3_id1
LOG:  pg_hint_plan:
used hint:
IndexScan(t3 t3_id1 t3_id2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN
-------------------------------
 Index Scan using t3_id2 on t3
   Index Cond: (id = 1)
(2 rows)

In the preceding examples, the first query is executed with no hint phrase. Hence, the index scan uses the primary key index t3_pkey. However, the second query contains the hint /*+IndexScan(t3 t3_id2)*/, and hence it uses the secondary index t3_id2 to perform the index scan. When the hint phrase /*+IndexScan(t3 no_exist)*/ is provided, the planner reverts to SeqScan as none of the indices can be used. You can also provide a selective list of indices in the hint phrases, and the query planner chooses among them.

Hints for join methods

Join method hints enforce the join methods for SQL statements. Using pg_hint_plan, you can specify the methodology by which tables should be joined.

Option	Value
HashJoin(t1 t2 t3 ...)	Join t1, t2, and t3 using HashJoin.
NoHashJoin(t1 t2 t3 ...)	Do not join t1, t2, and t3 using HashJoin.
NestLoop(t1 t2 t3 ...)	Join t1, t2, and t3 using NestLoop join.
NoNestLoop(t1 t2 t3 ...)	Do not join t1, t2, and t3 using NestLoop join.

/*+HashJoin(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

LOG:  pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN
------------------------------
 Hash Join
   Hash Cond: (t1.id = t2.id)
   ->  Seq Scan on t1
   ->  Hash
         ->  Seq Scan on t2
(5 rows)

/*+NestLoop(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

LOG:  pg_hint_plan:
used hint:
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:

              QUERY PLAN
--------------------------------------
 Nested Loop
   ->  Seq Scan on t1
   ->  Index Scan using t2_pkey on t2
         Index Cond: (id = t1.id)
(4 rows)

In this example, the first query uses a HashJoin on tables t1 and t2 respectively while the second query uses a NestedLoop join for the same. The required join methods are specified in their respective hint phrases. You can use multiple hint phrases to combine join methods and scan methods.

/*+NestLoop(t2 t3 t1) SeqScan(t3) SeqScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3
WHERE t1.id = t2.id AND t1.id = t3.id;

LOG:  pg_hint_plan:
used hint:
SeqScan(t2)
SeqScan(t3)
NestLoop(t1 t2 t3)
not used hint:
duplication hint:
error hint:

              QUERY PLAN
--------------------------------------
 Nested Loop
   ->  Hash Join
         Hash Cond: (t2.id = t3.id)
         ->  Seq Scan on t2
         ->  Hash
               ->  Seq Scan on t3
   ->  Index Scan using t1_pkey on t1
         Index Cond: (id = t2.id)
(8 rows)

In the preceding example, the hint /*+NestLoop(t2 t3 t4 t1) SeqScan(t3) SeqScan(t4)*/ enables NestLoop join on tables t1, t2, t3, and t4. It also enables SeqScan on tables t3 and t4 due to the hint phrases. The rest of the scans are performed using IndexScan.

Hints for joining order

Joining order hints execute joins in a particular order, as enumerated in a hint phrase's parameter list. You can enforce joining in a specific order using the Leading hint.

/*+Leading(t1 t2 t3)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.id = t3.id;

                 QUERY PLAN
--------------------------------------------
 Nested Loop
   ->  Nested Loop
         ->  Seq Scan on t1
         ->  Index Scan using t2_pkey on t2
               Index Cond: (id = t1.id)
   ->  Index Scan using t3_pkey on t3
         Index Cond: (id = t1.id)
(7 rows)

/*+Leading(t2 t3 t1)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.id = t3.id;

                 QUERY PLAN
--------------------------------------------
 Nested Loop
   ->  Nested Loop
         ->  Seq Scan on t2
         ->  Index Scan using t3_pkey on t3
               Index Cond: (id = t2.id)
   ->  Index Scan using t1_pkey on t1
         Index Cond: (id = t2.id)
(7 rows)

The joining order in the first query is /*+Leading(t1 t2 t3)*/, whereas the joining order for the second query is /*+Leading(t2 t3 t1)*/. You can see that the query plan's order of execution follows the joining order specified in the parameter lists of the hint phrases.

Setting working memory

You can leverage the work_mem setting in PostgreSQL to improve the performance of slow queries that sort, join, or aggregate large sets of table rows. For a detailed description of the implications, refer to Tuning work_mem Setting in PostgreSQL to Speed Up Slow SQL Queries.

The following example shows how to enable work_mem as a part of a hint plan.

/*+Set(work_mem "1MB")*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

LOG:  pg_hint_plan:
used hint:
Set(work_mem 1MB)
not used hint:
duplication hint:
error hint:

              QUERY PLAN
--------------------------------------
 Nested Loop
   ->  Seq Scan on t1
   ->  Index Scan using t2_pkey on t2
         Index Cond: (id = t1.id)
(4 rows)

Configuring the planner method

Planner method configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the optimizer for a particular query is not optimal, a temporary solution is to use one of these configuration parameters to force the optimizer to choose a different plan. YugabyteDB supports the following configuration parameters:

enable_hashagg
enable_hashjoin
enable_indexscan
enable_indexonlyscan
enable_material
enable_nestloop
enable_partition_pruning
enable_partitionwise_join
enable_partitionwise_aggregate
enable_seqscan
enable_sort

pg_hint_plan leverages the planner method configuration by embedding these configuration parameters in each query's comment. Consider the following example:

yugabyte=# EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

              QUERY PLAN
--------------------------------------
 Nested Loop
   ->  Seq Scan on t1
   ->  Index Scan using t2_pkey on t2
         Index Cond: (id = t1.id)
(4 rows)

/*+Set(enable_indexscan off)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;

LOG:  pg_hint_plan:
used hint:
Set(enable_indexscan off)
not used hint:
duplication hint:
error hint:

          QUERY PLAN
------------------------------
 Hash Join
   Hash Cond: (t1.id = t2.id)
   ->  Seq Scan on t1
   ->  Hash
         ->  Seq Scan on t2
(5 rows)

The first query uses an IndexScan in table t2. However, when you add /*+Set(enable_indexscan off)*/, the second query uses SeqScan in table t2. You can combine any parameters in the hint phrases of your SQL queries.

For a more detailed explanation of the Planner Method Configuration and a complete list of available configuration parameters, refer to Planner Method Configuration in the PostgreSQL documentation.

Using the hint table

Embedding hint phrases in every query can be overwhelming. To help, pg_hint_plan can use a table called hint_plan.hints to store commonly-used hinting phrases. Using the hint table, you can group queries of similar type, and instruct pg_hint_plan to enable the same hinting phrases for all such queries.

To enable the hint table, run the following commands:

/* Create the hint_plan.hints table */
CREATE EXTENSION pg_hint_plan;

/*
 * Tell pg_hint_plan to check the hint table for
 * hint phrases to be embedded along with the query.
 */
SET pg_hint_plan.enable_hint_table = on;

The following example illustrates this in detail.

yugabyte=# 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

yugabyte=# INSERT INTO hint_plan.hints
(norm_query_string,
 application_name,
 hints)
VALUES
('EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?;',
 '',
 'IndexScan(t1)');

INSERT 0 1

yugabyte=# select * from hint_plan.hints;

-[ RECORD 1 ]-----+--------------------------------------------------------
id                | 1
norm_query_string | EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;
application_name  |
hints             | SeqScan(t1)
-[ RECORD 2 ]-----+--------------------------------------------------------
id                | 2
norm_query_string | EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?;
application_name  |
hints             | IndexScan(t1)

This example inserts queries into the hint_plan.hints table, with placeholders for positional parameters using a question mark (?) and their required hint phrases respectively. During runtime, when these queries are executed, pg_hint_plan automatically executes these queries with their respective hinting phrases.

Learn more

Refer to Get query statistics using pg_stat_statements to track planning and execution of all the SQL statements.
Refer to View live queries with pg_stat_activity to analyze live queries.
Refer to View COPY progress with pg_stat_progress_copy to track the COPY operation status.
Refer to Analyze queries with EXPLAIN to optimize YSQL's EXPLAIN and EXPLAIN ANALYZE queries.