PostgreSQL 18 preview - in (values ()) 支持自动按需转换为 = any(array)

PostgreSQL 18 preview - in (values ()) 支持自动按需转换为 = any(array)

在 PostgreSQL 查询优化阶段，自动将 x IN (VALUES (v1), (v2), ...) 这种 SQL 写法，转换成 x = ANY (ARRAY[v1, v2, ...]) 的内部表示形式（具体来说是 ScalarArrayOpExpr 这种内部数据结构）。 从而改进基数估算提高JOIN方法选择精确性, 简化查询树.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c0962a113d1f2f94cb7222a7ca025a67e9ce3860

Convert 'x IN (VALUES ...)' to 'x = ANY ...'then appropriate  
author Alexander Korotkov <[email protected]>   
Fri, 4 Apr 2025 13:01:50 +0000 (16:01 +0300)  
committer Alexander Korotkov <[email protected]>   
Fri, 4 Apr 2025 13:01:50 +0000 (16:01 +0300)  
commit c0962a113d1f2f94cb7222a7ca025a67e9ce3860  
tree 6da1bad29d2394bca13b4c29c4df5ae9a4ceee67 tree  
parent d48d2e2dc8be50d3ca13305b5699384329b15433 commit | diff  
Convert 'x IN (VALUES ...)' to 'x = ANY ...'then appropriate  
This commit implements the automatic conversion of 'x IN (VALUES ...)' into  
ScalarArrayOpExpr.  That simplifies the query tree, eliminating the appearance  
of an unnecessary join.  
Since VALUES describes a relational table, and the value of such a list is  
a table row, the optimizer will likely face an underestimation problem due to  
the inability to estimate cardinality through MCV statistics.  The cardinality  
evaluation mechanism can work with the array inclusion check operation.  
If the array is small enough (< 100 elements), it will perform a statistical  
evaluation element by element.  
We perform the transformation in the convert_ANY_sublink_to_join() if VALUES  
RTE is proper and the transformation is convertible.  The conversion is only  
possible for operations on scalar values, not rows.  Also, we currently  
support the transformation only when it ends up with a constant array.  
Otherwise, the evaluation of non-hashed SAOP might be slower than the  
corresponding Hash Join with VALUES.  
Discussion: https://postgr.es/m/0184212d-1248-4f1f-a42d-f5cb1c1976d2%40tantorlabs.com  
Author: Alena Rybakina <[email protected]>  
Author: Andrei Lepikhov <[email protected]>  
Reviewed-by: Ivan Kush <[email protected]>  
Reviewed-by: Alexander Korotkov <[email protected]>  

AI 解读

补丁核心内容：

这个补丁的核心作用是，在 PostgreSQL 查询优化阶段，自动将 x IN (VALUES (v1), (v2), ...) 这种 SQL 写法，转换成 x = ANY (ARRAY[v1, v2, ...]) 的内部表示形式（具体来说是 ScalarArrayOpExpr 这种内部数据结构）。

主要目的和好处：

1. 简化查询树 (Simpler Query Tree)：

• VALUES (v1), (v2), ... 在 SQL 中被视为一个临时的、内存中的“表”。
• 因此，x IN (VALUES ...) 的原始形式在查询计划中可能会被处理成一个（通常是 Hash Join 或 Nested Loop Join）连接操作，即将主查询的表与这个临时的 VALUES 表进行连接。
• 转换成 x = ANY (ARRAY[...]) 后，它变成了一个简单的标量值（x）与一个数组的比较操作。这在内部表示上更简单，消除了不必要的连接结构，使得查询计划树更清晰、更易于优化器处理。

• 这是更重要的一个好处。优化器需要估算一个条件会匹配多少行（即“基数”），以便选择最优的查询执行计划。
• 对于 VALUES 子句，因为它被看作一个表，通过标准的统计信息（比如最常见值 MCV - Most Common Values）来精确估算 IN (VALUES ...) 条件的选择性可能比较困难，尤其当 VALUES 列表较长时，容易导致低估（underestimation）。
• 转换成数组包含操作 (= ANY (ARRAY[...])) 后，优化器可以使用针对数组的基数估算逻辑。特别是，如果数组很小（这里提到小于 100 个元素），优化器可以逐个评估数组元素对基数的影响，从而得到更准确的估算结果。准确的基数估算是生成高效查询计划的关键。

实现方式和限制条件：

1. 转换时机： 这个转换发生在查询优化的 convert_ANY_sublink_to_join() 函数内部。虽然名字带 sublink，但它也处理了这种 VALUES 的情况。
2. 转换前提：

• VALUES 部分必须是“合规的”（proper），意味着它结构简单，符合转换的预期。
• 操作必须是针对 标量值 (scalar values) 的，例如 id IN (VALUES (1), (2), (3))。不支持行值 (row values) 的比较，例如 (col1, col2) IN (VALUES (1, 'a'), (2, 'b')) 这种形式不会被转换。
• 关键限制： 目前只支持转换后能得到 常量数组 (constant array) 的情况。也就是说，VALUES 列表里的值必须是字面常量（如数字、字符串），而不是来自其他子查询或计算表达式的结果。

总结:

这个补丁通过将 x IN (VALUES ...) 转换为等价的 x = ANY (ARRAY[...]) 内部形式，优化了 PostgreSQL 处理这类查询的方式。主要优势在于简化了查询计划的内部结构，并显著改善了优化器对结果行数的估算准确性（尤其对于小型常量列表），从而有助于生成更优的查询执行计划。但该转换目前仅限于标量比较且 VALUES 列表为常量的情况。

顺便推广 if club 5.24 上海沙龙。欢迎报名参加