通過Hint幹預執行計畫

前提條件

• 執行個體核心小版本為V6.3.7.0及以上版本，如何查看和升級核心小版本，請參見版本升級。

• 已安裝pg_hint_plan外掛程式，具體資訊，請參見使用Hint。

指定SQL語句使用或不使用ORCA最佳化器

1. 開啟Hint幹預計劃並啟用Hint註冊規則，語句如下：

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

2. 建立用於測試的表t1和表t2，建表語句如下：

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

3. 分別往兩張表中插入測試資料，語句如下：

   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;

4. 建立索引，語句如下：

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

5. 強制關閉ORCA最佳化器，語句如下：

   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;$$);

   返回資訊如下：

   -[ 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 | {}

6. 輸出執行計畫，語句如下：

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

   強制關閉後，執行計畫使用了Postgres query optimizer最佳化器，返回資訊如下：

                                              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)

7. 強制啟用ORCA最佳化器，語句如下：

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

   返回資訊如下：

   -[ 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 | {}

8. 輸出執行計畫，語句如下：

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

   強制啟用後，執行計畫使用了ORCA最佳化器，返回資訊如下：

                                    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)

幹預JOIN順序和JOIN類型

1. 開啟Hint幹預計劃並啟用Hint註冊功能，語句如下：

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

2. 建立用於測試的表t1和表t2，建表語句如下：

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

3. 分別往兩張表中插入測試資料，語句如下：

   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;

4. 建立索引，語句如下：

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

5. 輸出執行計畫，語句如下：

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

   此處為預設執行計畫，JOIN類型和JOIN順序為HashJoin(t1 t2) Leading((t1 t2))，返回資訊如下：

                                       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

6. 以下幾種方法可以幹預JOIN順序和JOIN類型：

   • 在輸出執行計畫的語句前添加Hint，語句如下：

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

     JOIN類型由HashJoin轉變為MergeJoin，JOIN順序由(t1,t2)轉變為(t2,t1)，返回資訊如下：

                                         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)

   • 先註冊Hint再輸出執行計畫：

     1. 註冊SQL語句模板，語句如下：

        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;$$);

        返回資訊如下：

        -[ 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 | {}

     2. 輸出執行計畫，語句如下：

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

        Hint已生效，JOIN類型由HashJoin轉變為MergeJoin，JOIN順序由(t1,t2)轉變為(t2,t1)，返回資訊如下：

                                            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)

     3. 執行相同SQL語句模板的查詢計劃，語句如下：

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

        返回資訊如下，可以看到Hint也可以對同SQL語句模板的其它SQL語句生效：

                                            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)

     4. 可選：刪除所有該參數化語句的Hint，語句如下：

        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;$$);

   • 語句中部分常數固定，註冊Hint後執行計畫：

     1. 使用PREPARE語句註冊SQL語句模板，語句如下：

        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;$$);

        返回資訊如下：

        -[ 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}

     2. 執行變更了參數化常數的執行計畫，語句如下：

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

        參數化常數變化後，Hint依然可以生效，JOIN類型由HashJoin轉變為MergeJoin，JOIN順序由(t1,t2)轉變為(t2,t1)，返回資訊如下：

                                            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)

     3. 執行變更了固定常數的執行計畫，語句如下：

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

        固定常數變化後，Hint失效，不再幹預執行計畫，返回資訊如下：

                                            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)

     4. 可選：刪除所有該參數化語句的Hint，語句如下：

        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;$$);