PostgreSQL/GreenPlum 分区消除和左连接

Question

是否可以使用分区消除和分区表的左外部连接？

我的理解是，分区消除仅在分区键位于 where 子句中时才有效，因此where right_table.date_key = '2016-02-01'会执行分区消除，但这与左连接不兼容，因为它会消除right_table中不存在的任何行。

如果我放where (right_table.date_key = '2016-02-02' or right_table.date_key is null)那么它不会做任何分区消除。

我被要求发布完整的查询，所以这里有一个精简版本(真正的内容很大，有几十列，还有几个表格，一些大的案例陈述和机密的客户业务逻辑(：

select voyage.std -- timestamp
     , person.name
     , fact1.score score_1
     , fact2.score score_2
from fact1
     join voyage on voyage.voyage_sk = fact1.voyage_sk
     join person on person.person_sk = fact1.person_sk
left join fact2  on fact2.person_sk  = person.person_sk
where voyage.std = '2016-02-02 14:33:00'

所以fact1总是存在的，但fact2是可选的。所有表均未分区。

现在为了分区，我正在添加一个新列， voyage_sdd ，这是voyage.std的日期部分。我在新的日期列上对事实数据表和航行表进行了分区。然后查询变为：

select voyage.std -- timestamp
     , person.name
     , fact1.score score_1
     , fact2.score score_2
from fact1
     join voyage on voyage.voyage_sk = fact1.voyage_sk
     join person on person.person_sk = fact1.person_sk
left join fact2  on fact2.person_sk  = person.person_sk
where voyage.std = '2016-02-02 14:33:00'
and voyage.voyage_sdd = '2016-02-02'
and fact1.voyage_sdd = '2016-02-02'
and fact2.voyage_sdd = '2016-02-02'

最后一行使fact2成为内部联接。如果我省略最后一行，那么查询仍然有效并返回正确的数据，但它的效率低于非分区查询，因为它必须扫描所有分区。如果我fact2未分区，那么我在只有少量数据集的测试环境中获得了轻微的性能改进，我希望当我们获得更多磁盘空间和具有代表性的数据量时，这种情况会有所改善测试。

所以重申我的问题，我怎样才能划分 fact2 并且仍然有一个左连接？

更新 这有效：

select voyage.std -- timestamp
     , person.name
     , fact1.score score_1
     , fact2.score score_2
from voyage 
     join person on person.person_sk = fact1.person_sk
     join fact1  on fact1.voyage_sk  = voyage.voyage_sk and fact1.voyage_sdd = voyage.voyage_sdd
left join fact2  on fact2.person_sk  = person.person_sk and fact2.voyage_sdd = voyage.voyage_sdd
where voyage.std = '2016-02-02 14:33:00'
and voyage.voyage_sdd = '2016-02-02'

优化程序知道 fact2(和 fact1(表在连接键上分部分，并且由于航行表对连接键有约束，因此可以消除事实表分区。

Answer 1

首先，where (right_table.date_key = '2016-02-02' or right_table.date_key is null) NULL 上的or条件可能是阻止分区消除的问题。

其次，对于"如何划分f2"的问题。大多数时候，我总是在"日期"上进行分区，因为大多数 DW 查询都有一个谓词来缩小"日期"。就像你在最后一行所做的那样 fact2.voyage_sdd = '2016-02-02'.

此外，如果遵循您的业务逻辑，我会将所有分区列包含在"join"列中。在这种情况下，如果优化器支持通过连接消除动态分区，如 GPORCA (http://pivotal.io/big-data/white-paper/optimizing-queries-over-partitioned-tables-in-mpp-systems(，那么您可以从中受益。

希望能回答你的问题。

Answer 2

你问的是不可能的。条件where (right_table.date_key = '2016-02-02' or right_table.date_key is null)在其他术语中表示The date is '2016-02-02' or no other record exists)。因此，我们不能只局限于那张桌子。

如果你真正想要的不是 left join fact2 on fact2.person_sk = person.person_sk and fact2.voyage_sdd = '2016-02-02'

你能做的最好的事情就是尝试通过以另一种方式编写查询来获得更好的计划，例如：

select voyage.std -- timestamp
     , person.name
     , fact1.score score_1
     , fact2.score score_2
from fact1
join voyage on voyage.voyage_sk = fact1.voyage_sk
join person on person.person_sk = fact1.person_sk
left join fact2 on fact2.person_sk = person.person_sk
   AND fact2.voyage_sdd = '2016-02-02'
where voyage.std = '2016-02-02 14:33:00'
and voyage.voyage_sdd = '2016-02-02'
and fact1.voyage_sdd = '2016-02-02'
and (fact2.voyage_sdd = '2016-02-02' OR NOT EXISTS (SELECT * FROM fact2 WHERE fact2.person_sk = person.person_sk)