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

When PG 10 executes a query on a partition table, it checks the constraints of each partition one by one to see if it is necessary, and if there are many partitions, there will be a significant performance loss during the planning phase. PG 11 uses the "partition pruning" algorithm to quickly identify matching partitions to improve performance, but PG 11 still does some unnecessary processing, such as whether or not metadata for all partitions is still loaded.

PG 12 goes a step further by loading metadata after pruning, which can lead to significant performance improvements during the planning phase if most partitions are not involved.

Create a partition table

[local]: 5432 pg12@testdb=# drop table if exists tactile countermeasure note: table "t_counter" does not exist, skippingDROP TABLETime: 29.768 ms [local]: 5432 pg12@testdb=# create table t_counter (id int); CREATE TABLETime: 120.165 ms [local]: 5432 pg12@testdb=# insert into t_counter select generate_series (0ms 100000); INSERT 0 100001Time: 333.637 ms [local]: 5432 pg12@testdb=# drop table if exists t_hash_manypartitions NOTICE: table "t_hash_manypartitions" does not exist, skippingDROP TABLETime: 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 TABLETime: 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-# | | idpg12@testdb-# |' partition of t_hash_manypartitions for values with (modulus 8192 dint mainder'| | id | |'); 'pg12@testdb-# from t_counterpg12@testdb-# where id

< 8192pg12@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 TABLETime: 20.784 msCREATE TABLETime: 21.107 mspsql:/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;BEGINtestdb=# 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 time=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)

The planning time is more than 1.5s, which is a bad result.

Zhengzhou regular Infertility Hospital: http://www.xbzztj.com/

Query lock information

[xdb@localhost] $psql-d testdb- p 5433psql 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 | 4Lacer2 | 2695 | AccessShareLock | t | t t_hash_manypartitions_14 | relation | | | | 4x2 | 2695 | AccessShareLock | t | t t_hash_manypartitions_13 | relation | 4x2 | 2695 | AccessShareLock | t | t...testdb=# select count (*) from pg_locks where pid pg_backend_pid () | Count-8193 (1 row)

PG 12

Execute query

[local]: 5432 pg12@testdb=# begin;BEGINTime: 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=#*

The planning time 75ms is 2 orders of magnitude faster than the 1500ms of PG 11. Zhengzhou Infertility Hospital: http://www.zzchyy110.com/

Query lock information

[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 | 3can4 | 1591 | AccessShareLock | t | t t_hash_manypartitions | relation | 3 / 4 | 1591 | AccessShareLock | t | virtualxid | 3 ms 4 | | 3 rows 4 | 1591 | ExclusiveLock | t | t (3 ms) Time: 1.935 ms

Good, just lock the partition involved.

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.