PostgreSQL 11中分区表的本地索引

在PostgreSQL 10中引入了声明式分区时，这是向前迈出的一大步。 但是，新的重要功能在PostgreSQL 10中不起作用，而现在在PostgreSQL 11中已解决。其中之一是本地分区索引。 为了更容易理解，我们从PostgreSQL 10中的示例开始。

postgresql 10：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

postgres=# select version();                                                  version                                                  ----------------------------------------------------------------------------------------------------------  PostgreSQL 10.14 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=# create table part ( a int, list varchar(5) ) partition by list (list); CREATE TABLE postgres=# create table part_1 partition of part for values in ('beer'); CREATE TABLE postgres=# create table part_2 partition of part for values in ('wine'); CREATE TABLE postgres=# \d+ part                                           Table "public.part"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           |          |         | plain    |              |  list   | character varying(5) |           |          |         | extended |              | Partition key: LIST (list) Partitions: part_1 FOR VALUES IN ('beer'),             part_2 FOR VALUES IN ('wine')   postgres=#

在ppstgresql 10中，如果在分区表上创建索引，会发生什么呢？

?

1 2 3

postgres=# create index idx_test on part (a); ERROR:  cannot create index on partitioned table "part" postgres=#

无法创建索引。但是可以在分区上创建索引：

?

1 2 3 4 5

postgres=# create index idx_test_1 on part_1 (a); CREATE INDEX postgres=# create index idx_test_2 on part_2 (a); CREATE INDEX postgres=#

 

 

再来看看postgresql 11中是什么样子的。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

postgres=# create table part ( a int, list varchar(5) ) partition by list (list); CREATE TABLE postgres=# create table part_1 partition of part for values in ('beer'); CREATE TABLE postgres=# create table part_2 partition of part for values in ('wine'); CREATE TABLE postgres=# \d+ part                                           Table "public.part"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           |          |         | plain    |              |  list   | character varying(5) |           |          |         | extended |              | Partition key: LIST (list) Partitions: part_1 FOR VALUES IN ('beer'),             part_2 FOR VALUES IN ('wine')   postgres=#

再次尝试在分区表上创建索引：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

postgres=# create index idx_test on part (a); CREATE INDEX postgres=# \d+ part                                           Table "public.part"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           |          |         | plain    |              |  list   | character varying(5) |           |          |         | extended |              | Partition key: LIST (list) Indexes:     "idx_test" btree (a) Partitions: part_1 FOR VALUES IN ('beer'),             part_2 FOR VALUES IN ('wine')   postgres=# \d+ part_1                                          Table "public.part_1"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           |          |         | plain    |              |  list   | character varying(5) |           |          |         | extended |              | Partition of: part FOR VALUES IN ('beer') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5))) Indexes:     "part_1_a_idx" btree (a)   postgres=# \d+ part_2                                          Table "public.part_2"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           |          |         | plain    |              |  list   | character varying(5) |           |          |         | extended |              | Partition of: part FOR VALUES IN ('wine') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5))) Indexes:     "part_2_a_idx" btree (a)   postgres=#

在postgresql 11中，分区表上创建的索引被级联到了分区上。

 

此外，在postgresql10中，如果想对分区表添加主键约束，会失败：

?

1 2 3 4 5

postgres=# alter table part add constraint part_pk primary key(a,list); ERROR:  primary key constraints are not supported on partitioned tables LINE 1: alter table part add constraint part_pk primary key(a,list);                              ^ postgres=#

不过，却可以在分区上添加主键约束：

?

1 2 3 4 5

postgres=# alter table part_1 add constraint part1_pk primary key(a,list); ALTER TABLE postgres=# alter table part_2 add constraint part2_pk primary key(a,list); ALTER TABLE postgres=#

　　

 

在postgresql11中，可以直接在分区表上添加主键约束，添加成功后，会自动添加到分区上：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

postgres=# alter table part add constraint part_pk primary key(a,list); ALTER TABLE postgres=# \d+ part_1                                          Table "public.part_1"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           | not null |         | plain    |              |  list   | character varying(5) |           | not null |         | extended |              | Partition of: part FOR VALUES IN ('beer') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5))) Indexes:     "part_1_pkey" PRIMARY KEY, btree (a, list)     "part_1_a_idx" btree (a)   postgres=# \d+ part_2                                          Table "public.part_2"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           | not null |         | plain    |              |  list   | character varying(5) |           | not null |         | extended |              | Partition of: part FOR VALUES IN ('wine') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5))) Indexes:     "part_2_pkey" PRIMARY KEY, btree (a, list)     "part_2_a_idx" btree (a)   postgres=# \d+ part                                           Table "public.part"  Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+-------------  a      | integer              |           | not null |         | plain    |              |  list   | character varying(5) |           | not null |         | extended |              | Partition key: LIST (list) Indexes:     "part_pk" PRIMARY KEY, btree (a, list)     "idx_test" btree (a) Partitions: part_1 FOR VALUES IN ('beer'),             part_2 FOR VALUES IN ('wine')   postgres=#

当然也可以像10那样，直接在分区上添加主键约束。

不过，在分区表上添加的主键约束，必须包含分区列：

?

1 2 3 4

postgres=# alter table part add constraint part_pk primary key(a); ERROR:  insufficient columns in PRIMARY KEY constraint definition DETAIL:  PRIMARY KEY constraint on table "part" lacks column "list" which is part of the partition key. postgres=#