RDS PostgreSQL提供pg_hint_plan外掛程式,可以通過特殊的備註陳述式提示,使PostgreSQL改變其既定的執行計畫。
前提條件
背景資訊
PostgreSQL使用基於代價的最佳化器,最佳化路線使用統計資料而非固定的規則。對於一條SQL語句,最佳化器會評估每一種可能的代價並最終選擇代價最低的去執行,最佳化器會儘力選擇最好的執行計畫,但是由於其並不瞭解資料中可能存在的一些內在串連關係,這些執行計畫可能並不完美。使用pg_hint_plan外掛程式以特殊的注釋形式來提示SQL語句應該如何執行,可以最佳化執行計畫。
注釋提示
pg_hint_plan的注釋以/*+
開頭,以*/
結束。提示句包括提示名和接下來括弧包裹的參數,以空格作為分界。為了可讀性,每一個提示句都可以重新換行。
樣本
HashJoin作為串連方法,並且使用序列掃描SeqScan來掃描表pgbench_accounts。
/*+
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)
提示表
雖然可以使用注釋提示的方式對SQL語句進行提示,但是當SQL語句不可編輯時,這種提示方式就很不方便。對於這種情況,可以將提示放在一張特殊的表hint_plan.hints中。這個表包含了下欄欄位。
建立pg_hint_plan外掛程式的使用者預設擁有提示表的許可權,提示表的優先權高於注釋提示。
欄位 | 描述 |
id | 提示ID號,唯一且自動填滿。 |
norm_query_string | 與要提示的查詢匹配的模式。查詢中的常量必須替換為 |
application_name | 應用會話的名稱,置空表示任意應用。 |
hints | 提示句,不需要注釋標記。 |
樣本命令如下:
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
postgres=# UPDATE hint_plan.hints
postgres-# SET hints = 'IndexScan(t1)'
postgres-# WHERE id = 1;
UPDATE 1
postgres=# DELETE FROM hint_plan.hints
postgres-# WHERE id = 1;
DELETE 1
提示類型
根據提示短語影響執行計畫的方式,可以分為如下六類:
掃描方法提示
掃描方法提示對目標表強制執行特定的掃描方法。pg_hint_plan通過表格的別名(如果存在的話)來識別目標表。掃描方法是
SeqScan
、IndexScan
、NoSeqScan
等。掃描提示對普通表、繼承表、無日誌表、暫存資料表和系統資料表有效,對外部表格、表函數、常量值語句、通用運算式、視圖和子查詢無效。
樣本命令如下:
/*+ SeqScan(t1) IndexScan(t2 t2_pkey) */ SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
串連方法提示
串連方法提示強制指定相關表格彙總在一起的方法。
串連方法提示對普通表 、繼承表、無日誌表、暫存資料表、外部表格、系統資料表、表函數、常量值命令和通用運算式有效,對視圖和子查詢無效。
串連順序提示
串連順序提示指定兩個或多個表的串連順序。這裡有兩種強制指定方法:
強制執行特定的串連順序,但不限制每個串連層級的方向。
強制串連方向。
樣本命令如下:
/*+ 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);
行號糾正提示
行號糾正提示會糾正由於計劃器限制而導致的行號錯誤。
樣本命令如下:
/*+ Rows(a b #10) */ SELECT... ; //設定串連結果的行號為10。 /*+ Rows(a b +10) */ SELECT... ; //行號增加10。 /*+ Rows(a b -10) */ SELECT... ; //行號減去10。 /*+ Rows(a b *10) */ SELECT... ; //將行號擴大至原來的10倍。
並存執行提示
並行提示會指定並行的執行計畫。
並存執行提示對普通表、繼承表、無日誌表和系統資料表有效,對外部表格、常量從句、通用運算式、視圖和子查詢無效。視圖的內部表可以通過其真實名稱或別名指定目標對象。
下面兩個樣本說明在每個表上執行查詢的方式不同:
方式一
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)
設定臨時GUC參數
在計劃的時候臨時改變GUC參數。執行計畫中的GUC參數會有預期的效果,除非提示句與其他計劃衝突。同樣的GUC參數設定以最後一次為準。
樣本命令如下:
/*+ Set(random_page_cost 2.0) */ SELECT * FROM table1 t1 WHERE key = 'value';
所有提示支援的格式如下表。
類別 | 格式 | 說明 |
掃描方法提示 | SeqScan(table) | 強制序列掃描。 |
TidScan(table) | 強制TID掃描。 | |
IndexScan(table[ index...]) | 強制索引掃描,可以指定某個索引。 | |
IndexOnlyScan(table[ index...]) | 強制僅使用索引掃描,可以指定某個索引。 | |
BitmapScan(table[ index...]) | 強制使用Bitmap掃描。 | |
NoSeqScan(table) | 強制不使用序列掃描。 | |
NoTidScan(table) | 強制不使用TID掃描。 | |
NoIndexScan(table) | 強制不使用索引掃描。 | |
NoIndexOnlyScan(table) | 強制不使用索引掃描,僅掃描表。 | |
NoBitmapScan(table) | 強制不使用Bitmap掃描。 | |
串連方法提示 | NestLoop(table table[ table...]) | 強制使用嵌套迴圈串連。 |
HashJoin(table table[ table...]) | 強制使用散列串連。 | |
MergeJoin(table table[ table...]) | 強勢使用合并串連。 | |
NoNestLoop(table table[ table...]) | 強制不使用嵌套迴圈串連。 | |
NoHashJoin(table table[ table...]) | 強制不使用散列串連。 | |
NoMergeJoin(table table[ table...]) | 強制不使用合并串連。 | |
串連順序提示 | Leading(table table[ table...]) | 強制串連的順序。 |
Leading(<join pair>) | 強制串連的順序和方向。 | |
行號糾正提示 | Rows(table table[ table...] correction) | 糾正由指定表組成的聯結結果的行號。可用的校正方法為絕對值 |
並存執行提示 | Parallel(table <# of workers> [soft|hard]) | 強制或禁止並存執行指定表。 第三個參數如果是soft(預設),表示僅更改max_parallel_workers_per_gather並將其他所有內容留給計劃器選擇; 如果是hard,表示所有相關參數都會被強制指定。 |
設定臨時GUC參數 | Set(GUC-param value) | 規劃器運行時,將GUC參數設定為該值。 |
更多pg_hint_plan的介紹請參見PostgreSQL官方文檔。