Shulou(Shulou.com)06/01 Report--

PostgreSQL 12 Beta3, creating a partition table with 8192 subpartitions, executing the query, sorting on the partition key, error.

Database version:

[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

Data table structure

[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.)

Only 1 row of data

[local]: 5432 pg12@testdb=# insert into t_hash_manypartitions (c1 row c2) values; INSERT 0 1Time: 14.038 ms [local]: 5432 pg12@testdb=# select * from tweets hashmatching manypartitions; C1 | c2 | c3-+-0 | c2-0 | c3-0 (1 row) Time: 917.996 ms [local]: 5432 pg12@testdb=#

Although there is only one row of data, the full table scan is still slow, close to 1 second, while the normal table is only a few milliseconds.

[local]: 5432 pg12@testdb=# select * from tweets hashworthy partitions; 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=#

Execute the query and sort on the partition key c2

[local]: 5432 pg12@testdb=# select * from t_hash_manypartitions order by c2bot error: out of shared memoryHINT: You might need to increase max_locks_per_transaction.CONTEXT: parallel workerTime: 2420.971 ms (00from t_hash_manypartitions order by 02.421) [local]: 5432 pg12@testdb=#

Prompt out of shared memory, memory overflow

[local]: 5432 pg12@testdb=# alter system set max_locks_per_transaction=128;ALTER SYSTEMTime: 7.705 ms [local]: 5432 pg12@testdb=# select * from t_hash_manypartitions order by c2 political error: out of shared memoryHINT: You might need to increase max_locks_per_transaction.CONTEXT: parallel workerTime: 1988.893 ms [local]: 5432 pg12@testdb=# alter system set max_locks_per_transaction=512 ALTER SYSTEMTime: 13.137 ms [local]: 5432 pg12@testdb=# select * from t_hash_manypartitions order by c2scape error: out of shared memoryHINT: You might need to increase max_locks_per_transaction.CONTEXT: parallel workerTime: 1968.974 ms [local]: 5432 pg12@testdb=# alter system set max_locks_per_transaction=8192;ALTER SYSTEMTime: 4.060 ms [local]: 5432 pg12@testdb=# select * from t_hash_manypartitions order by c2 ERROR: out of shared memoryHINT: You might need to increase max_locks_per_transaction.CONTEXT: parallel workerTime: 1985.106 ms [local]: 5432 pg12@testdb=# alter system set max_locks_per_transaction=16384;ALTER SYSTEMTime: 7.791 ms [local]: 5432 pg12@testdb=# select * from t_hash_manypartitions order by c2 ERROR: out of shared memoryHINT: You might need to increase max_locks_per_transaction.CONTEXT: parallel workerTime: 1953.134 ms (01.953) [local]: 5432 pg12@testdb=#

As you can see, increasing the value of this parameter to 16384 still reports an error. To modify this parameter, you need to restart the database. Restart the database and execute it again.

Looking at the execution plan, PG performs a parallel scan on each partition, then merges the result sets using Parallel Append, and then performs the sort.

[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--

There is no problem on 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=#

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.