postgresql12数据库分区表之list [数据库教程]

Z时代
2024-01-10
分类：综合

database

os: centos 7.4
db: postgresql 12.2

postgresql 12 的分区表已经比较完善。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - postgres
Last login: Thu Mar 19 14:47:45 CST 2020 on pts/0
$ 
$ psql
psql (12.2)
Type "help" for help.
postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=# show enable_partition_pruning;
 enable_partition_pruning 
--------------------------
 on
(1 row)
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
(3 rows)

single column list

for list partitioning, the partition key must consist of a single column or expression.

postgres=# CREATE TABLE cities (
    city_id      bigint not null,
    name         text   not null,
    population   bigint
) PARTITION BY LIST (name);
CREATE TABLE cities_1 PARTITION OF cities FOR VALUES IN (‘A‘);
CREATE TABLE cities_2 PARTITION OF cities FOR VALUES IN (‘B‘);
CREATE TABLE cities_3 PARTITION OF cities FOR VALUES IN (‘C‘);
CREATE TABLE cities_4 PARTITION OF cities FOR VALUES IN (‘D‘);
postgres=# d+
                                        List of relations
 Schema |             Name              |       Type        |  Owner   |    Size    | Description
--------+-------------------------------+-------------------+----------+------------+-------------
 public | cities                        | partitioned table | postgres | 0 bytes    | 
 public | cities_1                      | table             | postgres | 8192 bytes | 
 public | cities_2                      | table             | postgres | 8192 bytes | 
 public | cities_3                      | table             | postgres | 8192 bytes | 
 public | cities_4                      | table             | postgres | 8192 bytes |  
(5 rows)
postgres=# select * from pg_inherits;
 inhrelid | inhparent | inhseqno 
----------+-----------+----------
    16727 |     16724 |        1
    16733 |     16724 |        1
    16739 |     16724 |        1
    16745 |     16724 |        1
(4 rows)

postgres=# insert into cities
select 1,‘A‘,1 
union all
select 2,‘B‘,2
union all
select 3,‘C‘,3 
union all
select 4,‘D‘,4
;
postgres=# d+
                                        List of relations
 Schema |             Name              |       Type        |  Owner   |    Size    | Description 
--------+-------------------------------+-------------------+----------+------------+-------------
 public | cities                        | partitioned table | postgres | 0 bytes    | 
 public | cities_1                      | table             | postgres | 16 kB      | 
 public | cities_2                      | table             | postgres | 16 kB      | 
 public | cities_3                      | table             | postgres | 16 kB      | 
 public | cities_4                      | table             | postgres | 16 kB      | 
(5 rows)

postgres=# explain select * from cities where name = ‘B‘;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on cities_2  (cost=0.00..23.38 rows=5 width=48)
   Filter: (name = ‘B‘::text)
(2 rows)
postgres=# explain select * from cities where name in (‘B‘,‘C‘);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Append  (cost=0.00..46.86 rows=22 width=48)
   ->  Seq Scan on cities_2  (cost=0.00..23.38 rows=11 width=48)
         Filter: (name = ANY (‘{B,C}‘::text[]))
   ->  Seq Scan on cities_3  (cost=0.00..23.38 rows=11 width=48)
         Filter: (name = ANY (‘{B,C}‘::text[]))(5 rows)