支持表分区 - Amazon Aurora

支持表分区

Aurora PostgreSQL 查询计划管理(QPM)在以下版本中支持声明式表分区:

  • 15.3 及更高的 15 版本

  • 14.8 及更高的 14 版本

  • 13.11 及更高的 13 版本

有关更多信息,请参阅表分区

设置表分区

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

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

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

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

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

有关这些参数的更多信息,请参阅Aurora PostgreSQL 查询计划管理的参数参考

捕获表分区的计划

在 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_aIndexScan (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)

强制执行表分区计划

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

例如,如果批准的概况适用于以下计划:

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)

命名约定

为了让 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 值有所不同。表名称因字母字符而异,而不仅仅是导致执行失败的数字。