分区是一种将表拆分为物理不同对象的选项，而表仍然是逻辑上的单个实体。分区是 PostgreSQL 的一项功能，我们借助 YugabyteDB 来实现功能，例如能够将位置专用于特定分区。
最近我们遇到了一种情况，我们发现分区表上的索引仅在过滤器独占使用一个分区时使用。如果访问了多个分区，该计划将恢复为分区的顺序扫描，这意味着这些表分区已被扫描。在大多数情况下，索引扫描需要的工作更少，因此速度更快。
让我们设置一个综合测试用例。此处描述的问题适用于 PostgreSQL 和 YugabyteDB。创建代码指示 YugabyteDB 特定的任何内容，并说明如何更改它以在 PostgreSQL 上工作：
设置对象
创建表空间
-- The WITH clause is YugabyteDB specific -- This can be skipped when testing in PostgreSQL create tablespace tablespace1 WITH (replica_placement='{ "num_replicas": 1, "placement_blocks": [{"cloud":"local","region":"local","zone":"local1","min_num_replicas":1}] }'); create tablespace tablespace2 WITH (replica_placement='{ "num_replicas": 1, "placement_blocks":[{"cloud":"local","region":"local","zone":"local2","min_num_replicas":1}] }'); create tablespace tablespace3 WITH (replica_placement='{ "num_replicas": 1, "placement_blocks":[{"cloud":"local","region":"local","zone":"local3","min_num_replicas":1}] }');复制
创建表空间允许在 PostgreSQL 中放置数据库文件，在 YugabyteDB 中它遵循该原则。对于 PostgreSQL，这意味着您可以将表存储移动到特定的挂载点。
对于 YugabyteDB，它允许专用 tablet 服务器并使用 为存储设置复制因子num_replicas，它允许从一般设置的复制因子更改它。
'cloud'、'region' 和 'zone' 属性是平板电脑服务器的属性（超出了本文的范围）。我的测试设置为“local1”、“local2”和“local3”的每个区域都有一个平板电脑服务器。
如果你想在 PostgreSQL 上重放这个，你可以跳过表空间创建。
创建指定分区的表
-- Change the comment to YugabyteDB for PostgreSQL create table partition_test( id int, locality varchar, f1 varchar, f2 varchar, primary key(id hash, locality asc) -- YugabyteDB --primary key(id asc, locality asc) -- PostgreSQL ) partition by list( locality );复制
使用分区将分区添加到表中
-- This works with PostgreSQL -- Comment out the tablespace clause if you didn't create tablespaces. create table partition_test_local1 partition of partition_test( id, locality, f1, f2 ) for values in ('local1') tablespace tablespace1 ; create table partition_test_local2 partition of partition_test( id, locality, f1, f2 ) for values in ('local2') tablespace tablespace2 ; create table partition_test_local3 partition of partition_test( id, locality, f1, f2 ) for values in ('local3') tablespace tablespace3 ;复制
插入测试数据
insert into partition_test values (1,'local1','aaa','bbb'), (2,'local2','aaa','ccc'), (3,'local3','bbb','ddd'), (4,'local1','bbb','eee'), (5,'local2','ccc','fff'), (6,'local3','ccc','ggg');复制
测试
无过滤：所有分区seq扫描
让我们看看对分区表进行扫描的解释计划是怎样的：
yugabyte=# explain select * from partition_test; QUERY PLAN --------------------------------------------------------------------------------- Append (cost=0.00..299.00 rows=3000 width=100) -> Seq Scan on partition_test_local1 (cost=0.00..94.00 rows=1000 width=100) -> Seq Scan on partition_test_local2 (cost=0.00..95.00 rows=1000 width=100) -> Seq Scan on partition_test_local3 (cost=0.00..95.00 rows=1000 width=100)复制
首先要注意的是，Append行源/计划节点将不同的结果聚集到一个结果中，并且可以从一个或两个以上的结果中获取结果。
过滤主键的子集
现在让我们在列上应用过滤器id：
yugabyte=# explain select id from partition_test where id = 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Append (cost=0.00..45.65 rows=300 width=4) -> Index Scan using partition_test_local1_pkey on partition_test_local1 (cost=0.00..14.65 rows=100 width=4) Index Cond: (id = 1) -> Index Scan using partition_test_local2_pkey on partition_test_local2 (cost=0.00..14.75 rows=100 width=4) Index Cond: (id = 1) -> Index Scan using partition_test_local3_pkey on partition_test_local3 (cost=0.00..14.75 rows=100 width=4) Index Cond: (id = 1)复制
id扫描所有分区以在列中查找值 1 似乎很奇怪。但是，请记住，主键是在id列和locality列上设置的，因此索引不能id单独保证列的唯一性，因此必须扫描整个（分区）表。
提到这一点的原因是分区表上的主键必须包括所有分区键列（请参阅PostgreSQL 文档第 5.10.2.3 章限制 https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE）。
这意味着可能很容易想到过滤通常是主键（id此处）的内容将使用主键查找。
过滤主键的子集，包括分区键
如果我们将过滤器应用于分区键（位置字段）：
yugabyte=# explain select id from partition_test where locality = 'local2'; QUERY PLAN ------------------------------------------------------------------------------- Append (cost=0.00..102.50 rows=1000 width=4) -> Seq Scan on partition_test_local2 (cost=0.00..97.50 rows=1000 width=4) Filter: ((locality)::text = 'local2'::text)复制
是的，尽管没有选择整个主键，但对分区键进行过滤足以进行分区修剪，并排除可以安全排除的分区。
问题
现在我们正在了解我们遇到的情况。我们有一个分区表，我们有多个分区。有时，无法使用定义的主键列来查询数据，但我们可以使用分区键。一个解决方案是为分区表创建另一个索引，该索引确实指定了分区键。但是，这可以通过多种方式完成。
这是我们遇到的情况：
create index partition_test_locality_f1_1 on partition_test_local1 (f1, locality) tablespace tablespace1 where locality = 'local1'; create index partition_test_locality_f1_2 on partition_test_local2 (f1, locality) tablespace tablespace1 where locality = 'local2'; create index partition_test_locality_f1_3 on partition_test_local3 (f1, locality) tablespace tablespace1 where locality = 'local3';复制
现在让我们看看我们是否可以使用索引来过滤具有单个值的两个字段：
yugabyte=# explain select * from partition_test where locality = 'local1' and f1 = 'aaa'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..14.50 rows=100 width=100) -> Index Scan using partition_test_locality_f1_1 on partition_test_local1 (cost=0.00..14.00 rows=100 width=100) Index Cond: ((f1)::text = 'aaa'::text)复制
这行得通，并使您认为这不是问题。
现在让我们尝试两个位置值：
yugabyte=# explain select * from partition_test where locality in ('local1','local2') and f1 = 'aaa'; QUERY PLAN ------------------------------------------------------------------------------------------------------- Append (cost=0.00..209.00 rows=2000 width=100) -> Seq Scan on partition_test_local1 (cost=0.00..99.00 rows=1000 width=100) Filter: (((locality)::text = ANY ('{local1,local2}'::text[])) AND ((f1)::text = 'aaa'::text)) -> Seq Scan on partition_test_local2 (cost=0.00..100.00 rows=1000 width=100) Filter: (((locality)::text = ANY ('{local1,local2}'::text[])) AND ((f1)::text = 'aaa'::text))复制
它确实执行了分区修剪，因为它没有访问“local3”的分区。但发生这种情况是因为我们指定了分区键，因此可以删除“local3”分区。
但是......索引访问更改为seq扫描：扫描的所有行，而不是使用已创建的索引。为什么会这样？
原因是创建的索引不是分区索引，而是部分索引。部分索引涵盖的部分是分区。
这里的实际问题是规划器不知道部分索引之间的关系，并且必须检查索引的每一行是否适用于查询（PostgreSQL 文档第 11.8 部分索引，底部部分 https://www.postgresql.org/docs/11/indexes-partial.html）。尽管文档说部分索引在每个分区定义时需要大量工作，对我来说可以使用它们，但我找不到强制使用每个分区部分索引的方法。
解决方案
解决方案是不使用部分索引，而是在作为表分区的表的字段上为每个分区定义一个正则索引。
首先删除部分索引：
drop index partition_test_locality_f1_1; drop index partition_test_locality_f1_2; drop index partition_test_locality_f1_3;复制
并创建索引
create index partition_test_locality_f1_1 on partition_test_local1 (f1, locality) tablespace tablespace1; create index partition_test_locality_f1_2 on partition_test_local2 (f1, locality) tablespace tablespace2; create index partition_test_locality_f1_3 on partition_test_local3 (f1, locality) tablespace tablespace3;复制
如果我们现在再次解释上面的 f1 和 locality 查询：
yugabyte=# explain select * from partition_test where locality in ('local1','local2') and f1 = 'aaa'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..10.60 rows=20 width=100) -> Index Scan using partition_test_locality_f1_1 on partition_test_local1 (cost=0.00..5.25 rows=10 width=100) Index Cond: (((f1)::text = 'aaa'::text) AND ((locality)::text = ANY ('{local1,local2}'::text[]))) -> Index Scan using partition_test_locality_f1_2 on partition_test_local2 (cost=0.00..5.25 rows=10 width=100) Index Cond: (((f1)::text = 'aaa'::text) AND ((locality)::text = ANY ('{local1,local2}'::text[])))复制
现在规划器可以在分区修剪后使用索引，因此可以使用索引的多个分区，就像我们在主键列上看到的过滤器一样。
如果在主分区表上定义索引，数据库将为每个表分区创建一个索引分区。遗憾的是，目前表空间不是从表分区继承的，因此索引分区最终是在默认表空间中创建的。
这意味着，如果您关心表和索引分区的位置/表空间，则不应在主表上为分区表定义索引，而是稍后将任何非 PK 索引添加到表分区，就像上面所做的那样。
警告
修剪分区后，规划器决定最佳扫描路径。这意味着可以删除表分区的其中一个索引，并保留其他索引：
drop index partition_test_locality_f1_2;复制
然后，当我们执行涉及“local2”分区和另一个分区的 SQL 时，我们可以为每个分区设置不同的访问路径：
yugabyte=# explain select * from partition_test where locality in ('local1','local2','local3') and f1 = 'aaa'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..116.88 rows=1020 width=100) -> Index Scan using partition_test_locality_f1_1 on partition_test_local1 (cost=0.00..5.26 rows=10 width=100) Index Cond: (((f1)::text = 'aaa'::text) AND ((locality)::text = ANY ('{local1,local2,local3}'::text[]))) -> Seq Scan on partition_test_local2 (cost=0.00..101.25 rows=1000 width=100) Filter: (((f1)::text = 'aaa'::text) AND ((locality)::text = ANY ('{local1,local2,local3}'::text[]))) -> Index Scan using partition_test_locality_f1_3 on partition_test_local3 (cost=0.00..5.26 rows=10 width=100) Index Cond: (((f1)::text = 'aaa'::text) AND ((locality)::text = ANY ('{local1,local2,local3}'::text[])))复制
请注意两次索引扫描和一次 seq 扫描，因为索引之前已被删除。如果添加了分区，但忘记为分区创建索引，也会发生这种情况。
原文标题：PostgreSQL and YugabyteDB partitioning and indexes
原文作者：Frits Hoogland
原文链接：https://dev.to/yugabyte/postgresql-and-yugabytedb-partitioning-and-indexes-4633
