PostgreSQL11preview-Fasterpartitionpruning
標籤
PostgreSQL , 分割槽 , 繼承 , constraint_exclusion
背景
目前PG社群版本內建分割槽表的效能受制於一些BIND、LOCK所有子表等問題，當分割槽過多時，效能會受到極大的影響。
《分割槽表鎖粒度差異 – pg_pathman VS native partition table》
《PostgreSQL 查詢涉及分割槽表過多導致的效能問題 – 效能診斷與優化(大量BIND, spin lock, SLEEP程式)》
不過還好pg_pathman這個外掛彌補了以上問題。同時EDB發行的商用PG版本，在分割槽上面也有一些改進。
社群中，有一些committer關注到了這樣的問題，同時提出了一些解法，已經將partition的選擇程式碼進行了改造，採用的方法可能與EDB商用髮型版本類似。
第一個相關patch如下
Faster partition pruning
Add a new module backend/partitioning/partprune.c, implementing a more
sophisticated algorithm for partition pruning. The new module uses each
partition`s "boundinfo" for pruning instead of constraint exclusion,
based on an idea proposed by Robert Haas of a "pruning program": a list
of steps generated from the query quals which are run iteratively to
obtain a list of partitions that must be scanned in order to satisfy
those quals.
At present, this targets planner-time partition pruning, but there exist
further patches to apply partition pruning at execution time as well.
This commit also moves some definitions from include/catalog/partition.h
to a new file include/partitioning/partbounds.h, in an attempt to
rationalize partitioning related code.
Authors: Amit Langote, David Rowley, Dilip Kumar
Reviewers: Robert Haas, Kyotaro Horiguchi, Ashutosh Bapat, Jesper Pedersen.
Discussion: https://postgr.es/m/098b9c71-1915-1a2a-8d52-1a7a50ce79e8@lab.ntt.co.jp
這個patch還有一些連帶的patch，未來社群版本的分割槽表應該不會再有bind, lock所有分割槽的問題了。
https://commitfest.postgresql.org/17/1330/
參考
《分割槽表鎖粒度差異 – pg_pathman VS native partition table》
《PostgreSQL 查詢涉及分割槽表過多導致的效能問題 – 效能診斷與優化(大量BIND, spin lock, SLEEP程式)》
《PostgreSQL 商用版本EPAS(阿里雲ppas) – 分割槽表效能優化 (堪比pg_pathman)》
《PostgreSQL 10 內建分割槽 vs pg_pathman perf profiling》