postgresql 13 数据库分区表性能之一 pg_pathman 在分区数为 64、128、256、512、1024 的简单测试

缺乏、安全感 2022-12-29 09:27 98阅读 0赞

os: centos 7.8  
db: postgresql 13.1

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.8.2003 (Core)
    # 
    # yum list installed |grep -i postgresql
    postgresql13.x86_64                     13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-contrib.x86_64             13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-devel.x86_64               13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-docs.x86_64                13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-libs.x86_64                13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-llvmjit.x86_64             13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-plperl.x86_64              13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-plpython3.x86_64           13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-pltcl.x86_64               13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-server.x86_64              13.1-1PGDG.rhel7               @pgdg13  
    postgresql13-test.x86_64                13.1-1PGDG.rhel7               @pgdg13 
    
    # yum list installed |grep -i pathman
    pg_pathman_13.x86_64                    1.5.12-1.rhel7                 @pgdg13
    
    # su - postgres
    Last login: Fri Dec 18 15:39:51 CST 2020 on pts/0
    
    $ psql
    psql (13.1)
    Type "help" for help.
    
    postgres=# select name,setting from pg_settings where name like '%partition%';
                   name                | setting 
    -----------------------------------+---------
     enable_partition_pruning          | on
     enable_partitionwise_aggregate    | off
     enable_partitionwise_join         | off
     pg_pathman.enable_auto_partition  | on
     pg_pathman.enable_partitionfilter | on
     pg_pathman.enable_partitionrouter | off
    (6 rows)
    
    postgres=# select name,setting from pg_settings where name like 'pg_pathman%' order by name;
                     name                 | setting  
    --------------------------------------+----------
     pg_pathman.enable                    | on
     pg_pathman.enable_auto_partition     | on
     pg_pathman.enable_bounds_cache       | on
     pg_pathman.enable_partitionfilter    | on
     pg_pathman.enable_partitionrouter    | off
     pg_pathman.enable_runtimeappend      | on
     pg_pathman.enable_runtimemergeappend | on
     pg_pathman.insert_into_fdw           | postgres
     pg_pathman.override_copy             | on
    (9 rows)

postgres=# create database yewudb;
    
    yewudb=# \c yewudb
    
    yewudb=# create extension pg_pathman;
    
    yewudb=# \dx
                         List of installed extensions
        Name    | Version |   Schema   |           Description            
    ------------+---------+------------+----------------------------------
     pg_pathman | 1.5     | public     | Partitioning tool for PostgreSQL
     plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
    (2 rows)
    
    yewudb=# \timing

# 64分区 #

drop table if exists tmp_hash64 cascade;
    
    create table tmp_hash64(
    id   bigint not null, 
    name text   not null, 
    insert_timestamp timestamp with time zone not null
    );
    
    create index idx_tmp_hash64_id on tmp_hash64(id)
    ;
    
    select create_hash_partitions(
    'tmp_hash64'::regclass,             -- 主表oid
    'id',                               -- 分区字段，一定要not null约束
    64,                                 -- 分区表数量
    false                               -- 不立即将数据从主表迁移到分区
    );
    
    
    select set_enable_parent('tmp_hash64'::regclass, false);

插入数据

insert into tmp_hash64 
    select id,
           id::text,
           current_date - mod(id,1000)
      from generate_series(1,1000000) as id;
      
    INSERT 0 1000000
    Time: 10710.247 ms (00:10.710)

有分区键

explain
    select id,
           name,
    	   insert_timestamp
      from tmp_hash64
     where id=99999
    ;
    
                                            QUERY PLAN                                         
    -------------------------------------------------------------------------------------------
     Index Scan using tmp_hash64_12_id_idx on tmp_hash64_12  (cost=0.29..8.30 rows=1 width=22)
       Index Cond: (id = 99999)
    (2 rows)
    
    Time: 4.078 ms
    
    select id,
           name,
    	   insert_timestamp
      from tmp_hash64
     where id=99999
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 0.674 ms

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash64 a,
           tmp_hash64 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
                                               QUERY PLAN                                           
    ------------------------------------------------------------------------------------------------
     Nested Loop  (cost=9.41..222.16 rows=2916 width=48)
       ->  Bitmap Heap Scan on tmp_hash64_12 a  (cost=4.70..92.79 rows=54 width=48)
             Recheck Cond: (id = 99999)
             ->  Bitmap Index Scan on tmp_hash64_12_id_idx  (cost=0.00..4.69 rows=54 width=0)
                   Index Cond: (id = 99999)
       ->  Materialize  (cost=4.70..93.06 rows=54 width=8)
             ->  Bitmap Heap Scan on tmp_hash64_12 b  (cost=4.70..92.79 rows=54 width=8)
                   Recheck Cond: (id = 99999)
                   ->  Bitmap Index Scan on tmp_hash64_12_id_idx  (cost=0.00..4.69 rows=54 width=0)
                         Index Cond: (id = 99999)
    (10 rows)
    
    Time: 1.838 ms
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash64 a,
           tmp_hash64 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 0.798 ms

无分区键

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash64 a,
           tmp_hash64 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
    (264 rows)
    
    Time: 17.679 ms
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash64 a,
           tmp_hash64 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 887.742 ms

# 128分区 #

drop table if exists tmp_hash128 cascade;
    
    create table tmp_hash128(
    id   bigint not null, 
    name text   not null, 
    insert_timestamp timestamp with time zone not null
    );
    
    create index idx_tmp_hash128_id on tmp_hash128(id)
    ;
    
    select create_hash_partitions(
    'tmp_hash128'::regclass,             -- 主表oid
    'id',                               -- 分区字段，一定要not null约束
    128,                                 -- 分区表数量
    false                               -- 不立即将数据从主表迁移到分区
    );
    
    
    select set_enable_parent('tmp_hash128'::regclass, false);

插入数据

insert into tmp_hash128 
    select id,
           id::text,
           current_date - mod(id,1000)
      from generate_series(1,1000000) as id;
      
    INSERT 0 1000000
    Time: 11580.045 ms (00:11.580)

有分区键

explain
    select id,
           name,
    	   insert_timestamp
      from tmp_hash128
     where id=99999
    ;
    
                                            QUERY PLAN                                          
    ---------------------------------------------------------------------------------------------
     Index Scan using tmp_hash128_12_id_idx on tmp_hash128_12  (cost=0.28..8.30 rows=1 width=22)
       Index Cond: (id = 99999)
    (2 rows)
    
    Time: 7.222 ms
    
    select id,
           name,
    	   insert_timestamp
      from tmp_hash128
     where id=99999
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 5.419 ms

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash128 a,
           tmp_hash128 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
                                               QUERY PLAN                                            
    -------------------------------------------------------------------------------------------------
     Nested Loop  (cost=8.98..108.14 rows=729 width=48)
       ->  Bitmap Heap Scan on tmp_hash128_12 a  (cost=4.49..49.48 rows=27 width=48)
             Recheck Cond: (id = 99999)
             ->  Bitmap Index Scan on tmp_hash128_12_id_idx  (cost=0.00..4.48 rows=27 width=0)
                   Index Cond: (id = 99999)
       ->  Materialize  (cost=4.49..49.62 rows=27 width=8)
             ->  Bitmap Heap Scan on tmp_hash128_12 b  (cost=4.49..49.48 rows=27 width=8)
                   Recheck Cond: (id = 99999)
                   ->  Bitmap Index Scan on tmp_hash128_12_id_idx  (cost=0.00..4.48 rows=27 width=0)
                         Index Cond: (id = 99999)
    (10 rows)
    
    Time: 2.618 ms
    
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash128 a,
           tmp_hash128 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 0.708 ms

无分区键

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash128 a,
           tmp_hash128 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
    (520 rows)
    
    Time: 49.063 ms
    
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash128 a,
           tmp_hash128 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 225.191 ms

# 256分区 #

drop table if exists tmp_hash256 cascade;
    
    create table tmp_hash256(
    id   bigint not null, 
    name text   not null, 
    insert_timestamp timestamp with time zone not null
    );
    
    create index idx_tmp_hash256_id on tmp_hash256(id)
    ;
    
    select create_hash_partitions(
    'tmp_hash256'::regclass,             -- 主表oid
    'id',                               -- 分区字段，一定要not null约束
    256,                                 -- 分区表数量
    false                               -- 不立即将数据从主表迁移到分区
    );
    
    
    select set_enable_parent('tmp_hash256'::regclass, false);

插入数据

insert into tmp_hash256
    select id,
           id::text,
           current_date - mod(id,1000)
      from generate_series(1,1000000) as id;
      
    INSERT 0 1000000
    Time: 10985.800 ms (00:10.986)

有分区键

explain
    select id,
           name,
    	   insert_timestamp
      from tmp_hash256
     where id=99999
    ;
    
                                         QUERY PLAN                                      
    -------------------------------------------------------------------------------------
     Bitmap Heap Scan on tmp_hash256_12  (cost=4.39..28.11 rows=14 width=48)
       Recheck Cond: (id = 99999)
       ->  Bitmap Index Scan on tmp_hash256_12_id_idx  (cost=0.00..4.39 rows=14 width=0)
             Index Cond: (id = 99999)
    (4 rows)
    
    Time: 2.453 ms
    
    
    select id,
           name,
    	   insert_timestamp
      from tmp_hash256
     where id=99999
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 8.704 ms

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash256 a,
           tmp_hash256 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
                                                   QUERY PLAN                                                
    ---------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=0.56..12.61 rows=1 width=48)
       ->  Index Scan using tmp_hash256_12_id_idx on tmp_hash256_12 a  (cost=0.28..8.30 rows=1 width=22)
             Index Cond: (id = 99999)
       ->  Index Only Scan using tmp_hash256_12_id_idx on tmp_hash256_12 b  (cost=0.28..4.30 rows=1 width=8)
             Index Cond: (id = 99999)
    (5 rows)
    
    Time: 0.800 ms
    
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash256 a,
           tmp_hash256 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 0.839 ms

无分区键

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash256 a,
           tmp_hash256 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
    (1032 rows)
    
    Time: 76.555 ms
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash256 a,
           tmp_hash256 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 268.585 ms

# 512分区 #

drop table if exists tmp_hash512 cascade;
    
    create table tmp_hash512(
    id   bigint not null, 
    name text   not null, 
    insert_timestamp timestamp with time zone not null
    );
    
    create index idx_tmp_hash512_id on tmp_hash512(id)
    ;
    
    select create_hash_partitions(
    'tmp_hash512'::regclass,             -- 主表oid
    'id',                               -- 分区字段，一定要not null约束
    512,                                 -- 分区表数量
    false                               -- 不立即将数据从主表迁移到分区
    );
    
    
    select set_enable_parent('tmp_hash512'::regclass, false);

插入数据

insert into tmp_hash512 
    select id,
           id::text,
           current_date - mod(id,1000)
      from generate_series(1,1000000) as id;
      
    INSERT 0 1000000
    Time: 12022.231 ms (00:12.022)

有分区键

explain
    select id,
           name,
    	   insert_timestamp
      from tmp_hash512
     where id=99999
    ;
    
                                         QUERY PLAN                                      
    -------------------------------------------------------------------------------------
     Bitmap Heap Scan on tmp_hash512_268  (cost=4.33..16.19 rows=7 width=48)
       Recheck Cond: (id = 99999)
       ->  Bitmap Index Scan on tmp_hash512_268_id_idx  (cost=0.00..4.33 rows=7 width=0)
             Index Cond: (id = 99999)
    (4 rows)
    
    Time: 7.290 ms
    
    select id,
           name,
    	   insert_timestamp
      from tmp_hash512
     where id=99999
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 22.381 ms

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash512 a,
           tmp_hash512 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
                                                    QUERY PLAN                                                 
    -----------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=0.56..12.60 rows=1 width=48)
       ->  Index Scan using tmp_hash512_268_id_idx on tmp_hash512_268 a  (cost=0.28..8.29 rows=1 width=22)
             Index Cond: (id = 99999)
       ->  Index Only Scan using tmp_hash512_268_id_idx on tmp_hash512_268 b  (cost=0.28..4.29 rows=1 width=8)
             Index Cond: (id = 99999)
    (5 rows)
    
    Time: 21.018 ms
    
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash512 a,
           tmp_hash512 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 1.117 ms

无分区键

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash512 a,
           tmp_hash512 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
     JIT:
       Functions: 3073
       Options: Inlining false, Optimization false, Expressions true, Deforming true
    (2059 rows)
    
    Time: 959.745 ms
    
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash512 a,
           tmp_hash512 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 434.730 ms

# 1024分区 #

drop table if exists tmp_hash1024 cascade;
    
    create table tmp_hash1024(
    id   bigint not null, 
    name text   not null, 
    insert_timestamp timestamp with time zone not null
    );
    
    create index idx_tmp_hash1024_id on tmp_hash1024(id)
    ;
    
    select create_hash_partitions(
    'tmp_hash1024'::regclass,             -- 主表oid
    'id',                               -- 分区字段，一定要not null约束
    1024,                                 -- 分区表数量
    false                               -- 不立即将数据从主表迁移到分区
    );
    
    
    select set_enable_parent('tmp_hash1024'::regclass, false);

插入数据

insert into tmp_hash1024
    select id,
           id::text,
           current_date - mod(id,1000)
      from generate_series(1,1000000) as id;
      
    INSERT 0 1000000
    Time: 12743.607 ms (00:12.744)

有分区键

explain
    select id,
           name,
    	   insert_timestamp
      from tmp_hash1024
     where id=99999
    ;
    
                                          QUERY PLAN                                      
    --------------------------------------------------------------------------------------
     Bitmap Heap Scan on tmp_hash1024_780  (cost=4.32..12.79 rows=5 width=48)
       Recheck Cond: (id = 99999)
       ->  Bitmap Index Scan on tmp_hash1024_780_id_idx  (cost=0.00..4.31 rows=5 width=0)
             Index Cond: (id = 99999)
    (4 rows)
    
    Time: 1.513 ms
    
    select id,
           name,
    	   insert_timestamp
      from tmp_hash1024
     where id=99999
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 0.922 ms

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash1024 a,
           tmp_hash1024 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
    
                                                QUERY PLAN                                            
    --------------------------------------------------------------------------------------------------
     Nested Loop  (cost=8.63..25.90 rows=25 width=48)
       ->  Bitmap Heap Scan on tmp_hash1024_780 a  (cost=4.32..12.79 rows=5 width=48)
             Recheck Cond: (id = 99999)
             ->  Bitmap Index Scan on tmp_hash1024_780_id_idx  (cost=0.00..4.31 rows=5 width=0)
                   Index Cond: (id = 99999)
       ->  Materialize  (cost=4.32..12.81 rows=5 width=8)
             ->  Bitmap Heap Scan on tmp_hash1024_780 b  (cost=4.32..12.79 rows=5 width=8)
                   Recheck Cond: (id = 99999)
                   ->  Bitmap Index Scan on tmp_hash1024_780_id_idx  (cost=0.00..4.31 rows=5 width=0)
                         Index Cond: (id = 99999)
    (10 rows)
    
    Time: 15.729 ms
    
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash1024 a,
           tmp_hash1024 b
     where 1=1
       and a.id=99999
       and a.id=b.id
    ;
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 0.539 ms

无分区键

explain
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash1024 a,
           tmp_hash1024 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
     JIT:
       Functions: 6145
       Options: Inlining false, Optimization false, Expressions true, Deforming true
    (4107 rows)
    
    Time: 1911.705 ms (00:01.912)
    
    
    select a.id,
           a.name,
    	   a.insert_timestamp
      from tmp_hash1024 a,
           tmp_hash1024 b
     where 1=1
       and a.name='99999'
       and a.name=b.name
    ;
    
      id   | name  |    insert_timestamp    
    -------+-------+------------------------
     99999 | 99999 | 2018-03-25 00:00:00+08
    (1 row)
    
    Time: 578.505 ms

参考：