Use pg_hint_plan Hints to Optimize Execution Plans - AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL supports the pg_hint_plan extension, which allows you to influence execution plans by embedding hints in your SQL statements. You can use hints to enable or disable the ORCA optimizer for a specific query statement, or to change the join type and join order used by the planner.

Hints are a targeted optimization technique. In most cases, the query optimizer selects an efficient execution plan automatically. Use hints only when you have identified a specific query where the optimizer's default plan is suboptimal and you need to override its decisions.

Prerequisites

An AnalyticDB for PostgreSQL instance of V6.3.7.0 or later is created. For more information about how to view and update the minor version of an instance, see Update the minor engine version.
The pg_hint_plan extension is installed. For more information, see Use the hint feature.

Enable or disable the ORCA optimizer for a query statement

This example demonstrates how to register hints that switch between the ORCA optimizer and the Postgres query optimizer for a specific query statement.

Prepare the environment

Enable hints to modify execution plans and enable the hint table feature.

   SET pg_hint_plan.enable_hint to on;
   SET pg_hint_plan.enable_hint_table to on;

Create tables t1 and t2.

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

Insert data to tables t1 and t2.

   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;

Create indexes for tables t1 and t2.

   CREATE INDEX t1_val ON t1 (val);
   CREATE INDEX t2_val ON t2 (val);

Disable the ORCA optimizer

Disable the ORCA optimizer.

   SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 1;$$);

The following information is returned:

   -[ RECORD 1 ]---------+-----------------------------------------------------
   id                    | 1
   norm_query_string     | explain select * from t1 join t2 on t1.val = t2.val;
   application_name      |
   hints                 | set(optimizer off)
   query_hash            | -2169095602568752481
   enable                | t
   prepare_param_strings | {}

Display an execution plan.

   EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 3;

The Postgres query optimizer is used in the execution plan. The following information is returned:

   QUERY PLAN
   -------------------------------------------------------------------------------------------------
    Gather Motion 3:1  (slice2; segments: 3)  (cost=8.25..27.61 rows=11 width=16)
      ->  Hash Join  (cost=8.25..27.61 rows=4 width=16)
            Hash Cond: (t2.val = t1.val)
            ->  Seq Scan on t2  (cost=0.00..13.00 rows=334 width=8)
            ->  Hash  (cost=8.22..8.22 rows=1 width=8)
                  ->  Broadcast Motion 1:3  (slice1; segments: 1)  (cost=0.16..8.22 rows=3 width=8)
                        ->  Index Scan using t1_pkey on t1  (cost=0.16..8.18 rows=1 width=8)
                              Index Cond: (id = 3)
    Optimizer: Postgres query optimizer
   (9 rows)

Enable the ORCA optimizer

Enable the ORCA optimizer.

   SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer on) SET(rds_optimizer_options 0) */SELECT * FROM t1 WHERE t1.id = 1;$$);

The following information is returned:

   -[ RECORD 1 ]---------+------------------------------------------------
   id                    | 2
   norm_query_string     | select * from t1 where t1.id = $1;
   application_name      |
   hints                 | set(optimizer on) set(rds_optimizer_options 0)
   query_hash            | -8281826471521807124
   enable                | t
   prepare_param_strings | {}

Display an execution plan.

   EXPLAIN SELECT * FROM t1 WHERE t1.id = 2;

The ORCA optimizer is used in the execution plan. The following information is returned:

   QUERY PLAN
   ----------------------------------------------------------------------------
    Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..6.00 rows=1 width=8)
      ->  Index Scan using t1_pkey on t1  (cost=0.00..6.00 rows=1 width=8)
            Index Cond: (id = 1)
    Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
   (4 rows)

Modify the join type and join order

This example demonstrates how to change the join type and join order in an execution plan by using hints. You can apply hints directly in the SQL statement or register them in the hint table so they take effect automatically for matching queries.

Prepare the environment

Disable the ORCA optimizer, enable hints to modify execution plans, and enable the hint table feature.

   SET optimizer to off;
   SET pg_hint_plan.enable_hint to on;
   SET pg_hint_plan.enable_hint_table to on;

Create tables t1 and t2.

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

Insert data to tables t1 and t2.

   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;

Create indexes for tables t1 and t2.

   CREATE INDEX t1_val ON t1 (val);
   CREATE INDEX t2_val ON t2 (val);

View the default execution plan

Display an execution plan.

   EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;

The default execution plan is displayed, with the join type being hash join and the join order being (t1 t2). The following information is returned:

   QUERY PLAN
   ----------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=25.50..208.00 rows=800 width=16)
      ->  Hash Join  (cost=25.50..208.00 rows=267 width=16)
            Hash Cond: (t1.id = t2.id)
            ->  Seq Scan on t1  (cost=0.00..137.00 rows=3334 width=8)
                  Filter: (val < 100)
            ->  Hash  (cost=15.50..15.50 rows=267 width=8)
                  ->  Seq Scan on t2  (cost=0.00..15.50 rows=267 width=8)
                        Filter: (val > 1)
    Optimizer: Postgres query optimizer

Apply hints to modify the execution plan

Use one of the following methods to modify the join type and join order:

Method 1: Add hints directly to the query statement

Add hints to the beginning of the statement to change the join type and join order.

   /*+ MergeJoin(t1 t2) Leading((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;

The join type changes from hash join to merge join, and the join order changes from (t1 t2) to (t2 t1). The following information is returned:

   QUERY PLAN
   -----------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.31..93.54 rows=800 width=16)
      ->  Merge Join  (cost=0.31..93.54 rows=267 width=16)
            Merge Cond: (t2.id = t1.id)
            ->  Index Scan using t2_pkey on t2  (cost=0.15..52.90 rows=267 width=8)
                  Filter: (val > 1)
            ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=3334 width=8)
                  Filter: (val < 100)
    Optimizer: Postgres query optimizer
   (8 rows)

Method 2: Register hints and display an execution plan

   SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);

The following information is returned:

   -[ RECORD 1 ]---------+--------------------------------------------------------------------------
   id                    | 1
   norm_query_string     | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;
   application_name      |
   hints                 | MergeJoin(t1 t2) Leading((t2 t1))
   query_hash            | -4733464863014584191
   enable                | t
   prepare_param_strings | {}

Display an execution plan.

   EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;

The hints take effect. The join type changes from hash join to merge join, and the join order changes from (t1 t2) to (t2 t1). The following information is returned:

   QUERY PLAN
   -----------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.31..93.54 rows=800 width=16)
      ->  Merge Join  (cost=0.31..93.54 rows=267 width=16)
            Merge Cond: (t2.id = t1.id)
            ->  Index Scan using t2_pkey on t2  (cost=0.15..52.90 rows=267 width=8)
                  Filter: (val > 1)
            ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=3334 width=8)
                  Filter: (val < 100)
    Optimizer: Postgres query optimizer
   (8 rows)

Display the execution plan of a query statement that uses the same SQL pattern.

   EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;

The following information is returned. The hints take effect and modify the execution plan of the query statement that uses the same SQL pattern.

   QUERY PLAN
   -----------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=8.25..38.24 rows=13 width=16)
      ->  Merge Join  (cost=8.25..38.24 rows=5 width=16)
            Merge Cond: (t1.id = t2.id)
            ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=2667 width=8)
                  Filter: (val < 80)
            ->  Sort  (cost=8.09..8.09 rows=1 width=8)
                  Sort Key: t2.id
                  ->  Index Scan using t2_val on t2  (cost=0.15..8.08 rows=1 width=8)
                        Index Cond: (val > 20)
    Optimizer: Postgres query optimizer
   (10 rows)

(Optional) Remove hints from the SQL pattern.

   SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);

Method 3: Register hints and display an execution plan when a fixed constant exists in the query statement

Use a PREPARE statement to register hints with an SQL pattern.

   SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > $1;$$);

The following information is returned:

   -[ RECORD 1 ]---------+--------------------------------------------------------------------------
   id                    | 1
   norm_query_string     | PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;
   application_name      |
   hints                 | MergeJoin(t1 t2) Leading((t2 t1))
   query_hash            | -4733464863014584191
   enable                | t
   prepare_param_strings | {100,$1}

Display the execution plan of a query statement in which a parameterized constant is changed.

   EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 2;

After the parameterized constant is changed, the hints still take effect. The join type changes from hash join to merge join, and the join order changes from (t1 t2) to (t2 t1). The following information is returned:

   QUERY PLAN
   -----------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.31..93.54 rows=800 width=16)
      ->  Merge Join  (cost=0.31..93.54 rows=267 width=16)
            Merge Cond: (t1.id = t2.id)
            ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=3334 width=8)
                  Filter: (val < 100)
            ->  Index Scan using t2_pkey on t2  (cost=0.15..52.90 rows=267 width=8)
                  Filter: (val > 2)
    Optimizer: Postgres query optimizer
   (8 rows)

Display the execution plan of a query statement in which a fixed constant is changed.

   EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;

After the fixed constant is changed, the hints do not take effect. The following information is returned:

   QUERY PLAN
   -----------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=8.09..175.22 rows=13 width=16)
      ->  Hash Join  (cost=8.09..175.22 rows=5 width=16)
            Hash Cond: (t1.id = t2.id)
            ->  Seq Scan on t1  (cost=0.00..137.00 rows=2667 width=8)
                  Filter: (val < 80)
            ->  Hash  (cost=8.08..8.08 rows=1 width=8)
                  ->  Index Scan using t2_val on t2  (cost=0.15..8.08 rows=1 width=8)
                        Index Cond: (val > 20)
    Optimizer: Postgres query optimizer
   (9 rows)

(Optional) Remove hints from the SQL pattern.

   SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);