在PostgreSQL 10中引入了声明式分区时，这是向前迈出的一大步。 但是，新的重要功能在PostgreSQL 10中不起作用，而现在在PostgreSQL 11中已解决。其中之一是本地分区索引。 为了更容易理解，我们从PostgreSQL 10中的示例开始。
postgresql 10：
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中，如果在分区表上创建索引，会发生什么呢？
postgres=# create index idx_test on part (a);
ERROR: cannot create index on partitioned table "part"
postgres=#
无法创建索引。但是可以在分区上创建索引：
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中是什么样子的。
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=#
再次尝试在分区表上创建索引：
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中，如果想对分区表添加主键约束，会失败：
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=#
不过，却可以在分区上添加主键约束：
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中，可以直接在分区表上添加主键约束，添加成功后，会自动添加到分区上：
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那样，直接在分区上添加主键约束。
不过，在分区表上添加的主键约束，必须包含分区列：
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=#
原文链接：https://www.cnblogs.com/abclife/p/13849338.html
