KingabseES执行计划-分区剪枝(partition pruning)
概述
分区修剪(Partition Pruning)是分区表性能的查询优化技术 。在分区修剪中，优化器分析SQL语句中的FROM和WHERE子句，以在构建分区访问列表时消除不需要的分区。此功能使数据库只能在与SQL语句相关的分区上执行操作。
参数 enable_partition_pruning 设置启用或禁用分区剪枝。
分区修剪的好处
分区修剪大大减少了从磁盘检索的数据量，缩短了处理时间，从而提高了查询性能并优化了资源利用率。
根据实际的SQL语句，Kingbase数据库可使用静态或动态修剪。静态修剪发生在编译时，预先访问有关分区的信息。动态修剪发生在运行时，这意味着语句要访问的确切分区事先是未知的。静态修剪的示例场景是一个SQL语句，该语句包含一个WHERE条件，分区键列上有一个常量文本。动态修剪的一个例子是在WHERE条件中使用运算符或函数。
可用于分区修剪的信息
可以对分区列执行分区修剪。
当您在范围或列表分区列上使用range、LIKE、 = 和IN列表谓词时，以及当您在哈希分区列中使用 = 或 IN列表谓词后，Kingbase数据库将修剪分区。
对于多级分区对象，Kingbase数据库可以使用相关谓词在每个级别上进行修剪。
Kingbase使用分区列上的谓词执行分区修剪，如下所示：
- 当使用范围分区时，Kingbase只访问分区p2和p3，表示2020年二月和三月的分区。
- 当使用哈希子分区时，Kingbase只访问每个分区中存储productid=100行的一个子分区。子分区和谓词之间的映射是基于Kingbase的内部哈希分布函数计算的。
CREATE TABLE orders_range_hash
(
productid int,
saledate DATE,
custid int,
totalprice numeric
)
PARTITION BY RANGE (saledate) SUBPARTITION BY HASH (productid) SUBPARTITIONS 8
(PARTITION p1 VALUES LESS THAN
(TO_DATE('2020-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN
(TO_DATE('2022-02-01', 'YYYY-MM-DD')),
PARTITION p3 VALUES LESS THAN
(TO_DATE('2022-03-01', 'YYYY-MM-DD')),
PARTITION p4 VALUES LESS THAN
(TO_DATE('2022-04-01', 'YYYY-MM-DD')));
SELECT *
FROM orders_range_hash
WHERE saledate BETWEEN (TO_DATE('2020-01-10', 'YYYY-MM-DD')) AND (TO_DATE('2020-02-11', 'YYYY-MM-DD'))
AND productid = 100;
如何确定是否已使用分区修剪
不仅在给定查询的规划期间可以执行分区剪枝，在其执行期间也能执行分区剪枝。 这非常有用，因为如果子句中包含查询规划时值未知的表达式时，这可以剪枝掉更多的分区； 例如在PREPARE语句中定义的参数会使用从子查询拿到的值，或者嵌套循环连接内侧关系上的参数化值。 执行期间的分区剪枝可能在下列任何时刻执行：
- 在查询计划的初始化期间。对于执行的初始化阶段就已知值的参数，可以在这里执行分区剪枝。这个阶段中被剪枝掉的分区将不会显示在查询的
EXPLAIN或EXPLAIN ANALYZE结果中。通过观察EXPLAIN输出的“Subplans Removed”属性，可以确定被剪枝掉的分区数。 - 在查询计划的实际执行期间。这里可以使用只有在实际查询执行时才能知道的值执行分区剪枝。这包括来自子查询的值以及来自执行时参数的值（例如来自于参数化嵌套循环连接的参数）。由于在查询执行期间这些参数的值可能会改变多次，所以只要分区剪枝使用到的执行参数发生改变，就会执行一次分区剪枝。要判断分区是否在这个阶段被剪枝，需要仔细地观察
EXPLAIN ANALYZE输出中的loops属性。 对应于不同分区的子计划可以具有不同的值，这取决于在执行期间每个分区被修剪的次数。 如果每次都被剪枝，有些分区可能会显示为(never executed)。
静态分区修剪
根据静态谓词确定何时使用静态修剪。
在许多情况下，优化器确定编译时要访问的分区。如果使用静态谓词，则会发生静态分区修剪。
如果在解析时，优化器可以识别访问的连续分区集，则执行计划中，将显示正在访问的分区的条件范围。
CREATE TABLE orders_list
(
productid int,
saledate DATE,
custid int,
totalprice numeric
)
PARTITION BY LIST (custid)
(PARTITION p1 VALUES (1,2),
PARTITION p2 VALUES (3,4),
PARTITION p2 VALUES (5,6));
explain analyzed
select * from orders_list
where custid = 3;
Seq Scan on orders_list_p2 (cost=0.00..23.38 rows=5 width=48) (actual time=0.016..0.020 rows=17 loops=1)
Filter: (custid = 3)
Rows Removed by Filter: 17
Planning Time: 0.107 ms
Execution Time: 0.037 ms
动态分区修剪
如果可以修剪，但无法进行静态修剪，则进行动态修剪，因为分区键值仅在执行时获知。
使用绑定变量进行动态修剪
对分区列使用绑定变量的语句会导致动态修剪。
\set vid 4
explain select * from orders_list where custid = :vid;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on orders_list_p2 (cost=0.00..23.38 rows=5 width=48)
Filter: (custid = 4)
(2 行记录)
do
$$
declare
c1 text;
begin
for c1 in execute 'explain select * from orders_list where custid = :vid' using (random() * 10)::int % 6 + 1
loop
raise info '%',c1;
end loop;
end;
$$;
信息: Seq Scan on orders_list_p1 (cost=0.00..23.38 rows=5 width=48)
信息: Filter: (custid = 2)
ANONYMOUS BLOCK
使用子查询进行动态修剪
对分区列显式使用子查询的语句会导致动态修剪。
分区节点的(never executed)，表示执行了分区修剪。如果过滤条件使用IN子查询，则不能分区修剪。
explain (costs off,analyze)
with v as (select (random() * 10)::int % 2 + 1 id)
select *
from orders_list
where custid = (select v.id from v);
QUERY PLAN
-----------------------------------------------------------------------------
Append (actual time=0.028..0.033 rows=17 loops=1)
CTE v
-> Result (actual time=0.004..0.004 rows=1 loops=1)
InitPlan 2 (returns $1)
-> CTE Scan on v (actual time=0.007..0.008 rows=1 loops=1)
-> Seq Scan on orders_list_p1 (actual time=0.015..0.018 rows=17 loops=1)
Filter: (custid = $1)
Rows Removed by Filter: 16
-> Seq Scan on orders_list_p2 (never executed)
Filter: (custid = $1)
-> Seq Scan on orders_list_p3 (never executed)
Filter: (custid = $1)
Planning Time: 0.172 ms
Execution Time: 0.069 ms
(14 行记录)
explain (costs off,analyze)
with v as (select (random() * 10)::int % 2 + 1 id)
select *
from orders_list
where custid in (select v.id from v);
QUERY PLAN
-----------------------------------------------------------------------------------
Hash Semi Join (actual time=0.046..0.067 rows=16 loops=1)
Hash Cond: (orders_list_p1.custid = v.id)
CTE v
-> Result (actual time=0.005..0.005 rows=1 loops=1)
-> Append (actual time=0.009..0.023 rows=100 loops=1)
-> Seq Scan on orders_list_p1 (actual time=0.008..0.010 rows=33 loops=1)
-> Seq Scan on orders_list_p2 (actual time=0.003..0.004 rows=34 loops=1)
-> Seq Scan on orders_list_p3 (actual time=0.002..0.004 rows=33 loops=1)
-> Hash (actual time=0.012..0.012 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> CTE Scan on v (actual time=0.008..0.008 rows=1 loops=1)
Planning Time: 0.303 ms
Execution Time: 0.095 ms
(13 行记录)
具有关联需求的动态修剪
等于(=)谓词，限制子查询的结果只能有一行。IN 、EXISTS、ANY 等方式，使用子查询时，不能执行动态修剪。多表连接，也不能执行动态修剪。
这种不能使用动态修剪的情况，可以使用LATERAL语法解决。
LATERAL子查询不能是简单子查询。
explain (costs off ,analyze)
with t (id) as (values (1), (2))
select *
from t
, lateral ( select * from orders_list t1 where t1.custid = t.id limit all) t1;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (actual time=0.022..0.037 rows=33 loops=1)
-> Values Scan on "*VALUES*" (actual time=0.002..0.003 rows=2 loops=1)
-> Append (actual time=0.009..0.013 rows=16 loops=2)
-> Seq Scan on orders_list_p1 t1 (actual time=0.007..0.009 rows=16 loops=2)
Filter: (custid = "*VALUES*".column1)
Rows Removed by Filter: 16
-> Seq Scan on orders_list_p2 t1_1 (never executed)
Filter: (custid = "*VALUES*".column1)
-> Seq Scan on orders_list_p3 t1_2 (never executed)
Filter: (custid = "*VALUES*".column1)
Planning Time: 0.189 ms
Execution Time: 0.072 ms
分区修剪提示
使用分区修剪时，应考虑以下事项：
-
数据类型转换
若要从分区修剪中获得最大的性能优势，应避免使用需要数据库转换指定数据类型的构造。
-
函数调用
避免在分区列上使用隐式或显式函数。如果您的查询通常使用函数调用，请考虑在这些情况下使用虚拟列和虚拟列分区，以从分区修剪中受益。
KINGBASE研究院
