PG在10分区表上执行查询时,会逐个检查每个分区的约束来看是否需要,如果分区很多在计划阶段会有较大的性能损失.PG 11通过“分区修剪”算法来快速的标识匹配的分区来改进性能,但PG 11仍然做了一些不必要的处理比如不管是否涉及仍然加载了所有分区的元数据。
PG 12更进一步,那就是在修剪后才加载元数据,如果不涉及大多数的分区那么在计划阶段可以带来明显的性能提升。
(本地):5432 pg12@testdb=#删除表如果存在t_counter;注意:表“t_counter”不存在,skippingDROP TABLETime: 29.768(本地)女士:5432 pg12@testdb=#创建表t_counter (int id);创建TABLETime: 120.165(本地)女士:5432 pg12@testdb=#插入t_counter选择generate_series(0, 100000);插入0 100001时间:333.637(本地)女士:5432 pg12@testdb=#删除表如果存在t_hash_manypartitions;注意:表“t_hash_manypartitions”不存在,skippingDROP TABLETime: 1.536(本地)女士:5432 pg12@testdb=#创建表t_hash_manypartitions (int c1, c2 varchar (40), c3 varchar(40)被散列分区(c2);创建TABLETime: 45.986(本地)女士:5432 pg12@testdb=#(本地):5432 pg12@testdb=# \ o/tmp/脚本。sql(本地):5432 pg12@testdb=#选择“创建表t_hash_manypartitions_”pg12@testdb - # | | idpg12@testdb - # | |”分区的t_hash_manypartitions值和(模量8192,剩余的| | id | |”);“从t_counterpg12@testdb pg12@testdb - # - # id & lt;8192 pg12@testdb - #订单的id;时间:78.499(本地)女士:5432 pg12@testdb=# \ o(本地):5432 pg12@testdb=10 #【root@localhost ~】#尾- n/tmp/脚本。sql创建表t_hash_manypartitions_8184分区t_hash_manypartitions值和(模量8192,剩余8184);创建表的t_hash_manypartitions t_hash_manypartitions_8185分区值(模量8192,剩余8185);创建表的t_hash_manypartitions t_hash_manypartitions_8186分区值(模量8192,剩余8186);创建表的t_hash_manypartitions t_hash_manypartitions_8187分区值(模量8192,剩余8187);创建表的t_hash_manypartitions t_hash_manypartitions_8188分区值(模量8192,剩余8188);创建表的t_hash_manypartitions t_hash_manypartitions_8189分区值(模量8192,剩余8189);创建表的t_hash_manypartitions t_hash_manypartitions_8190分区值(模量8192,剩余8190);创建表t_hash_manypartitions t_hash_manypartitions_8191分区的值(模量8192,剩余8191);(8192行)(本地):5432 pg12@testdb=# \我/tmp/script.sql……创建TABLETime: 20.784 msCREATE TABLETime: 21.107 mspsql:/tmp/脚本。sql: 8196:错误:语法错误达到或接近“8192”1号线:(8192行)^时间:0.198(本地)女士:5432 pg12@testdb=# 插入数据
插入t_hash_manypartitions (c1, c2, c3)值(1 ' c2-1 c3-1);
执行查询,条件为c2=癱2-1”
=#开始;testdb BEGINtestdb=#解释分析select *从t_hash_manypartitions c2=癱2-1”;查询计划- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Append(成本=0.00=2 . . 14.38行宽度=200)(实际时间=1.516=0 . . 1.516行循环=1)→Seq扫描> [xdb@localhost ~] psql psql testdb - d - p 5433美元(11.2)式“帮助”寻求帮助。testdb=#选择关系::regclass locktype, virtualxid, transactionid, virtualtransaction, pid,模式,当然,从testdb pg_locks - # - # testdb fastpath pid & lt;比;pg_backend_pid ();关系| locktype | virtualxid | transactionid | virtualtransaction pid | | |模式授予| fastpath - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - t_hash_manypartitions_15关系| | | | 4/2 | 2695 | AccessShareLock | | t t_hash_manypartitions_14 |关系| | | 4/2 | 2695 | AccessShareLock | | t t_hash_manypartitions_13 |关系| | | 4/2 | 2695 | AccessShareLock | | t…testdb=# select count(*)从pg_locks pid & lt;比;pg_backend_pid ();数- - - - - - - 8193(1行)
执行查询
(本地):5432 pg12@testdb=#开始;BEGINTime: 0.639(本地)女士:5432 pg12@testdb=# *解释分析select *从t_hash_manypartitions c2=癱2-1”;查询计划- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Seq扫描>(本地):5432 pg12@testdb=#选择关系::regclass, locktype, virtualxid, transactionid, virtualtransaction, pid,模式,当然,从pg_locks fastpath pid & lt;比;pg_backend_pid ();null
