PostgreSQL 12 Beta3，建立包含8192個子分割槽的分割槽表，執行查詢語句，在分割槽鍵上排序，出錯。
資料庫版本：
```
[local]:5432 pg12@testdb=# select version();
                                                  version                                   
                
--------------------------------------------------------------------------------------------
 PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.
8.5-16), 64-bit
(1 row)

Time: 9.511 ms
```
資料表結構
```
[local]:5432 pg12@testdb=# \d t_hash_manypartitions
        Partitioned table "public.t_hash_manypartitions"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           |          | 
 c2     | character varying(40) |           |          | 
 c3     | character varying(40) |           |          | 
Partition key: HASH (c2)
Number of partitions: 8191 (Use \d+ to list them.)
```
只有1行資料
```
[local]:5432 pg12@testdb=# insert into t_hash_manypartitions(c1,c2,c3) values(0,'c2-0','c3-0');
INSERT 0 1
Time: 14.038 ms
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions;
 c1 |  c2  |  c3  
----+------+------
  0 | c2-0 | c3-0
(1 row)

Time: 917.996 ms
[local]:5432 pg12@testdb=# 
```
雖然只有1行資料，但全表掃描仍然很慢，接近1s，而普通表僅幾毫秒。
```
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions;
 c1 |  c2  |  c3  
----+------+------
  0 | c2-0 | c3-0
(1 row)

Time: 898.615 ms
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions;
 c1 |  c2  |  c3  
----+------+------
  0 | c2-0 | c3-0
(1 row)

Time: 898.783 ms
[local]:5432 pg12@testdb=# 
```
執行查詢，在分割槽鍵c2上排序
```
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  parallel worker
Time: 2420.971 ms (00:02.421)
[local]:5432 pg12@testdb=# 
```
提示out of shared memory，記憶體溢位
```
[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=128;
ALTER SYSTEM
Time: 7.705 ms
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  parallel worker
Time: 1988.893 ms (00:01.989)
[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=512;
ALTER SYSTEM
Time: 13.137 ms
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  parallel worker
Time: 1968.974 ms (00:01.969)
[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=8192;
ALTER SYSTEM
Time: 4.060 ms
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  parallel worker
Time: 1985.106 ms (00:01.985)
[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=16384;
ALTER SYSTEM
Time: 7.791 ms
[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  parallel worker
Time: 1953.134 ms (00:01.953)
[local]:5432 pg12@testdb=# 
```
可以看到，增大該引數值至16384，仍然報錯。**修改此引數需重啟資料庫，重啟資料庫後重新執行即可**
檢視執行計劃，PG在每個分割槽上執行並行掃描，然後使用Parallel Append合併結果集，然後再執行排序。
```
[local]:5432 pg12@testdb=# explain select * from t_hash_manypartitions order by c2;
                                                QUERY PLAN                                  
              
--------------------------------------------------------------------------------------------
 Gather Merge  (cost=455382.87..734442.42 rows=2391772 width=200)
   Workers Planned: 2
   ->  Sort  (cost=454382.84..457372.56 rows=1195886 width=200)
         Sort Key: t_hash_manypartitions_1.c2
         ->  Parallel Append  (cost=0.00..104753.25 rows=1195886 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_1  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_2  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_3  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_4  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_5  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_6  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_7  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_8  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_9  (cost=0.00..12.06 rows=206 
width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_10  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_11  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_12  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_13  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_14  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_15  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_16  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_17  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_18  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_19  (cost=0.00..12.06 rows=206
 width=200)
               ->  Parallel Seq Scan on t_hash_manypartitions_20  (cost=0.00..12.06 rows=206
--More--
```
在PG 11.2上則沒有問題
```
testdb=# select version();
                                                 version                                    
             
--------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5
-16), 64-bit
(1 row)

testdb=# select * from t_hash_manypartitions order by c2;
 c1 |  c2  |  c3  
----+------+------
  0 | c2-0 | c3-0
(1 row)

testdb=# 
```

PostgreSQL DBA(95) - PG 12 Partition(out of shared memory)

相關文章