個人中心
退出
PostgreSQL 11 新特性之分區(qū)裁剪增強(qiáng)
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
在之前的版本中，只在查詢的計劃階段執(zhí)行分區(qū)排除操作（通過 constraint_exclusion 變量控制），意味著許多連接查詢和預(yù)編譯查詢無法使用分區(qū)排除。另外，這種方法占用的時間會隨著分區(qū)的數(shù)量線性增長。
PostgreSQL 11 通過兩個方面的改進(jìn)提供了更加強(qiáng)大且快速的分區(qū)裁剪功能：
查詢計劃階段更快的分區(qū)排除，可以提高分區(qū)表（尤其是包含許多分區(qū)的分區(qū)表）的訪問性能。
支持執(zhí)行階段的分區(qū)排除。
分區(qū)裁剪使用選項(xiàng) enable_partition_pruning 進(jìn)行控制。該參數(shù)默認(rèn)值為 on。
show enable_partition_pruning;
enable_partition_pruning
--------------------------
on
(1 row)
CREATE TABLE rtable(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
CREATE TABLE rtable100 PARTITION OF rtable FOR VALUES FROM (1) TO (100);
CREATE TABLE rtable200 PARTITION OF rtable FOR VALUES FROM (101) TO (200);
CREATE TABLE rtable300 PARTITION OF rtable FOR VALUES FROM (201) TO (300);
CREATE TABLE rtable400 PARTITION OF rtable FOR VALUES FROM (301) TO (400);
explain analyze select * from rtable where c1=256;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..24.53 rows=6 width=42) (actual time=0.009..0.009 rows=0 loops=1)
-> Seq Scan on rtable300 (cost=0.00..24.50 rows=6 width=42) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (c1 = 256)
Planning Time: 0.397 ms
Execution Time: 0.042 ms
(5 rows)
如果將該參數(shù)設(shè)置為 off，將會禁用分區(qū)裁剪功能：
set enable_partition_pruning=off;
explain analyze select * from rtable where c1=256;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..98.12 rows=24 width=42) (actual time=0.015..0.015 rows=0 loops=1)
-> Seq Scan on rtable100 (cost=0.00..24.50 rows=6 width=42) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (c1 = 256)
-> Seq Scan on rtable200 (cost=0.00..24.50 rows=6 width=42) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (c1 = 256)
-> Seq Scan on rtable300 (cost=0.00..24.50 rows=6 width=42) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (c1 = 256)
-> Seq Scan on rtable400 (cost=0.00..24.50 rows=6 width=42) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (c1 = 256)
Planning Time: 0.246 ms
Execution Time: 0.167 ms
(11 rows)
注意 Currently, pruning of partitions during the planning of an UPDATE
目前，對于 UPDATE 和 DELETE 語句，計劃階段的分區(qū)裁剪基于之前的約束排除方法實(shí)現(xiàn)（但是，該功能使用 enable_partition_pruning 選項(xiàng)控制，而不是 constraint_exclusion 選項(xiàng)）。
另外，執(zhí)行階段的分區(qū)裁剪目前支持 Append 節(jié)點(diǎn)類型，而不支持 MergeAppend 或者 ModifyTable（UPDATE 或者 DELETE）。
這些行為很可能在將來的 PostgreSQL 版本中進(jìn)行修改。
在 PostgreSQL 11 中，查詢計劃階段的分區(qū)排除使用二分查找法搜索匹配的分區(qū)（LIST 分區(qū)表和 RANGE 分區(qū)表）；對于哈希分區(qū)表，使用哈希函數(shù)查找匹配的分區(qū)。但是，對于 UPDATE/DELETE 語句，仍然使用約束排除的方法。
PostgreSQL 11 另一個重大的改進(jìn)就是支持查詢執(zhí)行時的動態(tài)分區(qū)裁剪。先看一個 PostgreSQL 10 中的示例。
-- PostgreSQL 10
PREPARE ps1 (INT) AS SELECT * FROM rtable WHERE c1 = $1;
explain analyze execute ps1(256);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..98.00 rows=24 width=42) (actual time=0.051..0.051 rows=0 loops=1)
-> Seq Scan on rtable100 (cost=0.00..24.50 rows=6 width=42) (actual time=0.011..0.011 rows=0 loops=1)
Filter: (c1 = 256)
-> Seq Scan on rtable200 (cost=0.00..24.50 rows=6 width=42) (actual time=0.006..0.006 rows=0 loops=1)
Filter: (c1 = 256)
-> Seq Scan on rtable300 (cost=0.00..24.50 rows=6 width=42) (actual time=0.006..0.006 rows=0 loops=1)
Filter: (c1 = 256)
-> Seq Scan on rtable400 (cost=0.00..24.50 rows=6 width=42) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (c1 = 256)
Planning time: 0.373 ms
Execution time: 0.215 ms
(11 rows)
查詢計劃顯示需要掃描所有的分區(qū)，因?yàn)椴樵冇媱澠鳠o法確定帶參數(shù)的查詢語句的執(zhí)行計劃。
以下是相同的操作在 PostgreSQL 11 中的結(jié)果：
-- PostgreSQL 11
PREPARE ps1 (INT) AS SELECT * FROM rtable WHERE c1 = $1;
explain analyze execute ps1(256);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..24.53 rows=6 width=42) (actual time=0.017..0.019 rows=1 loops=1)
-> Seq Scan on rtable300 (cost=0.00..24.50 rows=6 width=42) (actual time=0.016..0.017 rows=1 loops=1)
Filter: (c1 = 256)
Planning Time: 0.382 ms
Execution Time: 0.049 ms
(5 rows)
結(jié)果顯示，PostgreSQL 11 可以針對帶參數(shù)的查詢語句執(zhí)行分區(qū)排除。
動態(tài)分區(qū)排除可以利用查詢的計劃階段不能確定的值執(zhí)行分區(qū)裁剪；例如 PREPARE 語句中的參數(shù)，通過子查詢獲取的值，或者嵌套循環(huán)連接的內(nèi)層參數(shù)值。運(yùn)行時分區(qū)裁剪發(fā)生在以下兩個時間點(diǎn)：
查詢計劃初始化階段。使用執(zhí)行初始化階段能夠確定的參數(shù)值執(zhí)行分區(qū)裁剪。這個階段排除的分區(qū)不會顯示在 EXPLAIN 或 EXPLAIN ANALYZE 的結(jié)果中?？梢酝ㄟ^ EXPLAIN 結(jié)果的 “Subplans Removed” 屬性查看這個階段排除的分區(qū)數(shù)量。
查詢計劃的實(shí)際執(zhí)行階段。在查詢的實(shí)際執(zhí)行階段，仍然可能使用運(yùn)行時才能確定的值完成分區(qū)裁剪的操作。例如來自子查詢的值和運(yùn)行時參數(shù)（例如參數(shù)化的嵌套循環(huán)連接）的值。由于這些參數(shù)的值在運(yùn)行時可能會發(fā)生變化，每次參數(shù)變化時都會執(zhí)行一次分區(qū)裁剪操作。判斷是否在該階段產(chǎn)生分區(qū)排除可以查詢 EXPLAIN ANALYZE 輸出中的 nloops 屬性。
以下是一個使用子查詢的示例：
explain analyze select * from rtable where c1=(select 256);
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Append (cost=0.01..74.61 rows=19 width=42) (actual time=0.027..0.027 rows=0 loops=1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
-> Seq Scan on rtable100 (cost=0.00..24.50 rows=6 width=42) (never executed)
Filter: (c1 = $0)
-> Seq Scan on rtable200 (cost=0.00..24.50 rows=6 width=42) (never executed)
Filter: (c1 = $0)
-> Seq Scan on rtable300 (cost=0.00..1.00 rows=1 width=42) (actual time=0.012..0.012 rows=0 loops=1)
Filter: (c1 = $0)
-> Seq Scan on rtable400 (cost=0.00..24.50 rows=6 width=42) (never executed)
Filter: (c1 = $0)
Planning Time: 0.301 ms
Execution Time: 0.103 ms
(13 rows)
查詢計劃中的 (never executed) 就是運(yùn)行時動態(tài)執(zhí)行的分區(qū)裁剪。
官方文檔：Table Partitioning
分類導(dǎo)航