# 支持表分区
<a name="AuroraPostgreSQL.QPM.Partitiontable"></a>

Aurora PostgreSQL 查询计划管理（QPM）在以下版本中支持声明式表分区：
+ 15.3 及更高的 15 版本
+ 14.8 及更高的 14 版本
+ 13.11 及更高的 13 版本

有关更多信息，请参阅[表分区](https://www.postgresql.org/docs/current/ddl-partitioning.html)。

**Topics**
+ [设置表分区](#AuroraPostgreSQL.QPM.Partitiontable.setup)
+ [捕获表分区的计划](#AuroraPostgreSQL.QPM.Partitiontable.capture)
+ [强制执行表分区计划](#AuroraPostgreSQL.QPM.Partitiontable.enforcement)
+ [命名约定](#AuroraPostgreSQL.QPM.Partitiontable.naming.convention)

## 设置表分区
<a name="AuroraPostgreSQL.QPM.Partitiontable.setup"></a>

 要在 Aurora PostgreSQL QPM 中设置表分区，请执行以下操作：

1. 在数据库集群参数组中将 `apg_plan_mgmt.plan_hash_version` 设置为 3 或更多。

1. 导航到使用查询计划管理且在 `apg_plan_mgmt.dba_plans` 视图中具有条目的数据库。

1. 调用 `apg_plan_mgmt.validate_plans('update_plan_hash')` 以更新 plans 表中的 `plan_hash` 值。

1. 对所有启用了查询计划管理且在 `apg_plan_mgmt.dba_plans` 视图中具有条目的数据库重复步骤 2-3。

有关这些参数的更多信息，请参阅[Aurora PostgreSQL 查询计划管理的参数参考](AuroraPostgreSQL.Optimize.Parameters.md)。

## 捕获表分区的计划
<a name="AuroraPostgreSQL.QPM.Partitiontable.capture"></a>

在 QPM 中，不同的计划以其 `plan_hash` 值来区分。要了解 `plan_hash` 如何变化，必须先了解类似的计划。

在 Append 节点级别累积的访问方法、去掉数字的索引名称和去掉数字的分区名称的组合必须是常量，才能将计划视为相同。在计划中访问的特定分区并不重要。在以下示例中，创建了一个包含 4 个分区的表 `tbl_a`。

```
postgres=>create table tbl_a(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table tbl_a1 partition of tbl_a for values from (0) to (1000);
CREATE TABLE
postgres=>create table tbl_a2 partition of tbl_a for values from (1001) to (2000);
CREATE TABLE
postgres=>create table tbl_a3 partition of tbl_a for values from (2001) to (3000);
CREATE TABLE
postgres=>create table tbl_a4 partition of tbl_a for values from (3001) to (4000);
CREATE TABLE
postgres=>create index t_i on tbl_a using btree (i);
CREATE INDEX
postgres=>create index t_j on tbl_a using btree (j);
CREATE INDEX
postgres=>create index t_k on tbl_a using btree (k);
CREATE INDEX
```

以下计划被认为是相同的，因为无论查询查找的分区数量是多少，都使用单一扫描方法来扫描 `tbl_a`。

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 999 and j < 9910 and k > 50;
            
                        QUERY PLAN
-------------------------------------------------------------------
Seq Scan on tbl_a1 tbl_a
    Filter: ((i >= 990) AND (i <= 999) AND (j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -694232056
(3 rows)
```

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                        QUERY PLAN
-------------------------------------------------------------------
Append
    ->  Seq Scan on tbl_a1 tbl_a_1
            Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
    ->  Seq Scan on tbl_a2 tbl_a_2
            Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
    SQL Hash: 1553185667, Plan Hash: -694232056
    (6 rows)
```

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a3 tbl_a_3
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -694232056
(8 rows)
```

以下 3 个计划也被认为是相同的，因为在父级别，访问方法、去除数字的索引名称和去除数字的分区名称是 `SeqScan tbl_a`、`IndexScan (i_idx) tbl_a`。

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a2_i_idx on tbl_a2 tbl_a_2
         Index Cond: ((i >= 990) AND (i <= 1100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(7 rows)
```

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)
```

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a3 tbl_a_3
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(11 rows)
```

无论子分区中出现顺序和次数有何不同，在上述每种计划的父级别，访问方法、去除数字的索引名称和去除数字的分区名称都是常量。

但是，如果满足以下任何条件，则计划将被视为不同：
+ 计划中使用了任何其他访问方法。

  ```
  postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
                      
                                  QUERY PLAN
  --------------------------------------------------------------------------
   Append
     ->  Seq Scan on tbl_a1 tbl_a_1
           Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
     ->  Seq Scan on tbl_a2 tbl_a_2
           Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
     ->  Bitmap Heap Scan on tbl_a3 tbl_a_3
           Recheck Cond: ((i >= 990) AND (i <= 2100))
           Filter: ((j < 9910) AND (k > 50))
           ->  Bitmap Index Scan on tbl_a3_i_idx
                 Index Cond: ((i >= 990) AND (i <= 2100))
   SQL Hash: 1553185667, Plan Hash: 1134525070
  (11 rows)
  ```
+ 计划中有任何访问方法不再使用。

  ```
  postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
                      
                                 QUERY PLAN
  --------------------------------------------------------------------------
   Append
     ->  Seq Scan on tbl_a1 tbl_a_1
           Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
     ->  Seq Scan on tbl_a2 tbl_a_2
           Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   SQL Hash: 1553185667, Plan Hash: -694232056
  (6 rows)
  ```
+ 与索引方法关联的索引已更改。

  ```
  postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
                      
                               QUERY PLAN
  --------------------------------------------------------------------------
   Append
     ->  Seq Scan on tbl_a1 tbl_a_1
           Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
     ->  Index Scan using tbl_a2_j_idx on tbl_a2 tbl_a_2
           Index Cond: (j < 9910)
           Filter: ((i >= 990) AND (i <= 1100) AND (k > 50))
   SQL Hash: 1553185667, Plan Hash: -993343726
  (7 rows)
  ```

## 强制执行表分区计划
<a name="AuroraPostgreSQL.QPM.Partitiontable.enforcement"></a>

已批准的分区表计划通过位置对应关系来强制执行。这些计划并不特定于分区，可以在原始查询中引用的计划以外的分区上强制执行。对于访问的分区数量与原始批准的概况数量不同的查询，也可以强制执行计划。

例如，如果批准的概况适用于以下计划：

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))   
 SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)
```

则也可以在引用 2 个、4 个或更多分区的 SQL 查询上强制执行此计划。对于 2 个和 4 个分区访问，这些场景可能会产生以下可能的计划：

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                                QUERY PLAN
----------------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 1100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan. 
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(8 rows)
```

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a4 tbl_a_4
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041 
(12 rows)
```

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
----------------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(14 rows)
```

考虑另一项已批准的计划，每个分区使用不同的访问方法：

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Bitmap Heap Scan on tbl_a3 tbl_a_3
         Recheck Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a3_i_idx
               Index Cond: ((i >= 990) AND (i <= 2100))
 SQL Hash: 1553185667, Plan Hash: 2032136998
(12 rows)
```

在这种情况下，任何从两个分区进行读取的计划都将无法强制执行。除非批准的计划中的所有（访问方法、索引名称）组合都可用，否则该计划将无法强制执行。例如，以下计划具有不同的计划哈希值，在这些情况下无法强制执行批准的计划：

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
            
                              QUERY PLAN
-------------------------------------------------------------------------
 Append
   ->  Bitmap Heap Scan on tbl_a1 tbl_a_1
         Recheck Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a1_i_idx
               Index Cond: ((i >= 990) AND (i <= 1900))
   ->  Bitmap Heap Scan on tbl_a2 tbl_a_2
         Recheck Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a2_i_idx
               Index Cond: ((i >= 990) AND (i <= 1900))
  Note: This is not an Approved plan.  No usable Approved plan was found.
  SQL Hash: 1553185667, Plan Hash: -568647260
(13 rows)
```

```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
            
                              QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1900) AND (j < 9910) AND (k > 50))
 Note: This is not an Approved plan.  No usable Approved plan was found.
 SQL Hash: 1553185667, Plan Hash: -496793743
(8 rows)
```

## 命名约定
<a name="AuroraPostgreSQL.QPM.Partitiontable.naming.convention"></a>

为了让 QPM 强制执行声明式分区表计划，必须遵循父表、表分区和索引的特定命名规则：
+ **父表名称** – 这些名称在字母或特殊字符方面必须有所不同，而不仅仅是数字方面的不同。例如，tA、tB 和 tC 是单独父表的可接受名称，而 t1、t2 和 t3 不是。
+ **单个分区表名称** – 同一父表的分区表之间应只存在数字方面的不同。例如，tA 的可接受分区名称可以是 tA1、tA2 或 t1A、t2A 甚至多位数。

  任何其他差异（字母、特殊字符）均不能保证计划强制执行。
+ **索引名称** – 在分区表层次结构中，请确保所有索引都具有唯一的名称。这意味着名称的非数字部分必须不同。例如，如果您有一个名为 `tA` 的分区表，其索引名称为 `tA_col1_idx1`，则不能再将其他索引命名为 `tA_col1_idx2`。但是，您可以具有名为 `tA_a_col1_idx2` 的索引，因为名称的非数字部分是唯一的。此规则适用于在父表和单个分区表上创建的索引。

 不遵守上述命名约定可能会导致批准的计划无法强制执行。以下示例说明了这种无法强制执行的情况：

```
postgres=>create table t1(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table t1a partition of t1 for values from (0) to (1000);
CREATE TABLE
postgres=>create table t1b partition of t1 for values from (1001) to (2000);
CREATE TABLE
postgres=>SET apg_plan_mgmt.capture_plan_baselines TO 'manual';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 0;

                            QUERY PLAN
--------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on t1a t1_1
               Filter: (i > 0)
         ->  Seq Scan on t1b t1_2
               Filter: (i > 0)
 SQL Hash: -1720232281, Plan Hash: -1010664377
(7 rows)
```

```
postgres=>SET apg_plan_mgmt.use_plan_baselines TO 'on';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 1000;

                            QUERY PLAN
-------------------------------------------------------------------------
 Aggregate
   ->  Seq Scan on t1b t1
         Filter: (i > 1000)
 Note: This is not an Approved plan. No usable Approved plan was found.
 SQL Hash: -1720232281, Plan Hash: 335531806
(5 rows)
```

尽管这两个计划可能看起来相同，但由于子表的名称，它们的 `Plan Hash` 值有所不同。表名称因字母字符而异，而不仅仅是导致执行失败的数字。