PG 10在分割槽表上執行查詢時，會逐個檢查每個分割槽的約束來看是否需要，如果分割槽很多在計劃階段會有較大的效能損失。PG 11透過”partition pruning“演算法來快速的標識匹配的分割槽來改進效能，但PG 11仍然做了一些不必要的處理比如不管是否涉及仍然載入了所有分割槽的後設資料。
PG 12更進一步，那就是在pruning後才載入後設資料，如果不涉及大多數的分割槽那麼在計劃階段可以帶來明顯的效能提升。

**建立分割槽表**
```

[local]:5432 pg12@testdb=# drop table if exists t_counter;
NOTICE:  table "t_counter" does not exist, skipping
DROP TABLE
Time: 29.768 ms
[local]:5432 pg12@testdb=# create table t_counter(id int);
CREATE TABLE
Time: 120.165 ms
[local]:5432 pg12@testdb=# insert into t_counter select generate_series(0,100000);
INSERT 0 100001
Time: 333.637 ms

[local]:5432 pg12@testdb=# drop table if exists t_hash_manypartitions;
NOTICE:  table "t_hash_manypartitions" does not exist, skipping
DROP TABLE
Time: 1.536 ms
[local]:5432 pg12@testdb=# create table t_hash_manypartitions (c1 int,c2  varchar(40),c3 varchar(40)) partition by hash(c2);
CREATE TABLE
Time: 45.986 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# \o /tmp/script.sql
[local]:5432 pg12@testdb=# select 'create table t_hash_manypartitions_'
pg12@testdb-#       ||id
pg12@testdb-#       ||' partition of t_hash_manypartitions for values with (modulus 8192,remainder '||id||');'
pg12@testdb-# from t_counter
pg12@testdb-# where id < 8192
pg12@testdb-# order by id ;
Time: 78.499 ms
[local]:5432 pg12@testdb=# \o
[local]:5432 pg12@testdb=#

[root@localhost ~]# tail -n 10 /tmp/script.sql 
 create table t_hash_manypartitions_8184 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8184);
 create table t_hash_manypartitions_8185 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8185);
 create table t_hash_manypartitions_8186 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8186);
 create table t_hash_manypartitions_8187 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8187);
 create table t_hash_manypartitions_8188 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8188);
 create table t_hash_manypartitions_8189 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8189);
 create table t_hash_manypartitions_8190 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8190);
 create table t_hash_manypartitions_8191 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8191);
(8192 rows)

[local]:5432 pg12@testdb=# \i /tmp/script.sql
...
CREATE TABLE
Time: 20.784 ms
CREATE TABLE
Time: 21.107 ms
psql:/tmp/script.sql:8196: ERROR:  syntax error at or near "8192"
LINE 1: (8192 rows)
         ^
Time: 0.198 ms
[local]:5432 pg12@testdb=# 
```
插入資料
```
insert into t_hash_manypartitions(c1,c2,c3) values(1,'c2-1','c3-1');

```

**PG 11**
執行查詢，條件為c2 = 'c2-1'
```
testdb=# begin;
BEGIN
testdb=# explain analyze select * from t_hash_manypartitions where c2 = 'c2-1';
                                                         QUERY PLAN                         
                                 
--------------------------------------------------------------------------------------------
---------------------------------
 Append  (cost=0.00..14.38 rows=2 width=200) (actual time=1.516..1.516 rows=0 loops=1)
   ->  Seq Scan on t_hash_manypartitions_4956  (cost=0.00..14.38 rows=2 width=200) (actual t
ime=1.491..1.491 rows=0 loops=1)
         Filter: ((c2)::text = 'c2-1'::text)
 Planning Time: 1585.294 ms
 Execution Time: 2.502 ms
(5 rows)
```
計劃時間超過1.5s，比較糟糕的結果。
查詢鎖資訊
```
[xdb@localhost ~]$ psql -d testdb -p 5433
psql (11.2)
Type "help" for help.

testdb=# select relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath 
testdb-# from pg_locks 
testdb-# where pid <> pg_backend_pid();
          relation          |  locktype  | virtualxid | transactionid | virtualtransaction |
 pid  |      mode       | granted | fastpath 
----------------------------+------------+------------+---------------+--------------------+
------+-----------------+---------+----------
 t_hash_manypartitions_15   | relation   |            |               | 4/2                |
 2695 | AccessShareLock | t       | t
 t_hash_manypartitions_14   | relation   |            |               | 4/2                |
 2695 | AccessShareLock | t       | t
 t_hash_manypartitions_13   | relation   |            |               | 4/2                |
 2695 | AccessShareLock | t       | t
...

testdb=# select count(*) from pg_locks 
where pid <> pg_backend_pid();
 count 
-------
  8193
(1 row)
```

**PG 12**
執行查詢
```
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.639 ms
[local]:5432 pg12@testdb=#* explain analyze select * from t_hash_manypartitions where c2 = 'c2-1';
                                                       QUERY PLAN                           
                             
--------------------------------------------------------------------------------------------
-----------------------------
 Seq Scan on t_hash_manypartitions_4956  (cost=0.00..14.38 rows=2 width=200) (actual time=22
.356..22.356 rows=0 loops=1)
   Filter: ((c2)::text = 'c2-1'::text)
 Planning Time: 75.491 ms
 Execution Time: 22.603 ms
(4 rows)

Time: 519.835 ms
[local]:5432 pg12@testdb=#* 
```
計劃時間75ms，比起PG 11的1500ms快了2個數量級。
查詢鎖資訊
```
[local]:5432 pg12@testdb=# select relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath 
from pg_locks 
where pid <> pg_backend_pid();
          relation          |  locktype  | virtualxid | transactionid | virtualtransaction |
 pid  |      mode       | granted | fastpath 
----------------------------+------------+------------+---------------+--------------------+
------+-----------------+---------+----------
 t_hash_manypartitions_4956 | relation   |            |               | 3/4                |
 1591 | AccessShareLock | t       | t
 t_hash_manypartitions      | relation   |            |               | 3/4                |
 1591 | AccessShareLock | t       | t
                            | virtualxid | 3/4        |               | 3/4                |
 1591 | ExclusiveLock   | t       | t
(3 rows)

Time: 1.935 ms
```
很好，只是給涉及的分割槽上鎖而已。

**參考資料**
[PostgreSQL 12: Partitioning is now faster ](https://www.2ndquadrant.com/en/blog/postgresql-12-partitioning/)

PostgreSQL DBA(94) - PG 12 Improving Partition(Select)

相關文章