sql - Greenplum中的分区消除

Question

我有这样一个场景:

SELECT * FROM PACKAGE WHERE PACKAGE_TYPE IN ('BOX','CARD')

该表按 PACKAGE_TYPE 字段分区。假设 PACKAGE_TYPE 字段有二十个可能的值。所以有二十个分区，包括BOX、CARD 和DEFAULT 分区。运行上述查询时，分区消除会正确进行，并且只会扫描 BOX 和 CARD 分区。结果很快。

但是，当同样的查询写成这样时:

SELECT * FROM PACKAGE WHERE PACKAGE_TYPE IN (SELECT PACKAGE_TYPE FROM PACKAGE_LIST_TABLE)，其中 PACKAGE_LIST_TABLE 中的列 PACKAGE_TYPE 包含两个值 BOX 和 CARD。

运行上述查询时，将扫描所有 20 个分区。它会降低性能。

编译器似乎无法正确识别第二个查询，因此所有分区都被访问了。

有什么解决方法可以解决这个问题？

提前致谢。

Answer 1

The Postgres manual page on Partitioning包括这个警告

Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.

为了消除对分区的查找，Postgres 必须知道在创建查询计划时该分区中没有相关的行。在您的查询中，这仅在子查询完成后发生，因此必须将查询分成两部分，第二部分仅在第一部分完成后才计划。

如果分区包括分区列 (PACKAGE_TYPE) 上的索引以及约束，则规划器可能选择对每个分区使用索引扫描，导致无论如何，在运行时合理有效地消除了不正确的分区。 (也就是说，将进行 20 次索引扫描，但每次扫描需要的资源非常少。)

另一种方法是自己拆分查询，然后动态构建 SQL。由于 SELECT PACKAGE_TYPE FROM PACKAGE_LIST_TABLE 最多只能返回 20 个不同的值，因此您可以将这些值选择到应用程序或用户定义函数中的数组/集合中。然后，您可以像在第一个示例中那样将它们作为 IN ( ... ) 子句中的文字传递(或等效地 = ANY(array_expression))，并实现分区消除。