PostgreSQL中的partition-wise join

2019-10-10 14:09 abce 阅读(1147) 评论(0) 编辑收藏举报

与基于继承的分区(inheritance-based partitioning)不同，PostgreSQL 10中引入的声明式分区对数据如何划分没有任何影响。PostgreSQL 11的查询优化器正准备利用这种“无推理”表示。第一个提交的是partition-wise join。

什么是partition-wise join

如果连接表的分区键之间存在相等连接条件，那么两个类似分区表之间的连接可以分解为它们的匹配分区之间的连接。分区键之间的等连接意味着一个分区表的给定分区中给定行的所有连接伙伴必须在另一个分区表的相应分区中。因此，分区表之间的连接可以分解为匹配分区之间的连接。这种将分区表之间的连接分解为分区之间的连接的技术称为partition-wise join。

PostgreSQL中的partition-wise join

让我们从一个例子开始。考虑按如下方式分区的两个表:

create table prt1 (a int, b int, c varchar) partition by range(a);
create table prt1_p1 partition of prt1 for values from (0) to (5000);
create table prt1_p2 partition of prt1 for values from (5000) to (15000);
create table prt1_p3 partition of prt1 for values from (15000) to (30000);
 
create table prt2 (a int, b int, c varchar) partition by range(b);
create table prt2_p1 partition of prt2 for values from (0) to (5000);
create table prt2_p2 partition of prt2 for values from (5000) to (15000);
create table prt2_p3 partition of prt2 for values from (15000) to (30000);

prt1_p1中一行的所有连接伙伴都来自prt2_p1。
prt1_p2中一行的所有连接伙伴都来自prt2_p2。
而prt1_p3中一行的所有连接伙伴都来自prt2_p3。
这三个组成了匹配的分区对。没有partition-wise join，这两个表之间的连接计划如下:

explain (costs off)
select * from prt1 t1, prt2 t2 where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
                       QUERY PLAN             
-------------------------------------------------------
 Hash Join
   Hash Cond: (t2.b = t1.a)
   ->  Append
         ->  Seq Scan on prt2_p1 t2
               Filter: ((b >= 0) AND (b <= 10000))
         ->  Index Scan using prt2_p2_b on prt2_p2 t2_1
               Index Cond: ((b >= 0) AND (b <= 10000))
   ->  Hash
         ->  Append
               ->  Seq Scan on prt1_p1 t1
                     Filter: (b = 0)
               ->  Seq Scan on prt1_p2 t1_1
                     Filter: (b = 0)
               ->  Seq Scan on prt1_p3 t1_2
                     Filter: (b = 0)
(15 rows)

partition-wise join的加入计划为相同的查询如下:

explain (costs off)
select * from prt1 t1, prt2 t2 where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
                               QUERY PLAN                            
 
------------------------------------------------------------------------
 Append
   ->  Hash Join
         Hash Cond: (t2.b = t1.a)
         ->  Seq Scan on prt2_p1 t2
               Filter: ((b >= 0) AND (b <= 10000))
         ->  Hash
               ->  Seq Scan on prt1_p1 t1
                     Filter: (b = 0)
   ->  Nested Loop
         ->  Seq Scan on prt1_p2 t1_1
               Filter: (b = 0)
         ->  Index Scan using prt2_p2_b on prt2_p2 t2_1
               Index Cond: ((b = t1_1.a) AND (b >= 0) AND (b <= 10000))
(13 rows)

这里有几点需要注意:

1.存在一个等价连接条件t1.a=t2.b,包括来自两个表的分区键。
2.在没有partition-wise join的情况下，连接将在“appending”来自任何分区表的每个分区的所有行之后执行连接。对于partition-wise join，在匹配分区之间的连接后并附加结果。当连接结果的大小明显小于叉乘的结果时，这是有利的。更有利的是，如果分区本身是外部表，即分区中的数据驻留在外部服务器上。
3.在没有partition-wise join的情况下，它使用散列连接，但是在partition-wise join的情况下，它对分区之间的每个连接使用不同的策略，为每个连接选择最佳策略。例如，prt1_p2和prt2_p2之间的连接使用带有prt2_p2_b索引扫描的嵌套循环连接作为参数化的内端，而另一个连接使用散列连接。
4.条件t2.b between 0和10000之间消除了分区prt2_p3，因此在没有partition-wise join的情况下不会被计划扫描。但是它没有注意到prt1_p3中的任何一行都没有连接伙伴，并且仍然扫描该分区。使用partition-wise join，它意识到没有匹配的分区，消除了对prt1_p3的扫描。消除整个分区是一个重大的改进，因为顺序扫描非常昂贵。

Partition-wise join优于未分区连接，因为它可以利用分区的属性，并使用更小的哈希表，这些哈希表可能完全在内存中，更快的内存排序，在外部分区情况下的连接下推，等等。

基本的Partition-wise join之外

在提交的基本版本中，当连接表具有完全相同的分区键数据类型并具有完全匹配的分区边界时，将应用该技术。但有几个增强的可能性:

1.即使分区边界不完全匹配，当一个分区表中的每个分区最多有一个与另一个分区表匹配的分区时，也可以使用该技术。目前正在为此开发一个补丁。
2.通过将未分区表与每个分区分别连接并合并这些连接的结果，可以使用此技术执行未分区表和已分区表之间的连接。当查询中的一些表是未分区的，而其他表是类似分区的，并且一个最佳计划将分区表和未分区表交错时，这可能会有所帮助。
3.这种技术使用更多的内存和CPU，即使partition-wise join不是最佳策略。减少这种技术的内存和CPU占用。
4.当连接两个不同分区的表时，对其中一个表重新分区以匹配另一个表的分区方案，然后使用partition-wise join进行连接;一种通常有助于通过重新分布数据来连接不同的切分表的技术。