pg_clickhouse 0.10增强查询下推，TPC-H Q17降至37毫秒

继续我们对 pg_clickhouse 的投资，提高分析工作负载的下推覆盖率仍然是我们的首要关注点，将 TPC-H 基准测试套件的完整下推作为我们的直接指标。自我们在 六月 的上次更新以来，我们取得了很大进展，包括 TPC-H 记分牌上的进展，自 我们的介绍性文章 于去年十二月发布以来，我们还没有真正谈论过记分牌，所以我们将从这里开始。随着 v0.10.0 的发布，我们的记分牌已从 22 个 TPC-H 查询中的 12 个完全下推进到 16 个，只剩下 6 个就能完成整个集合。

在此过程中，我们还

• 在新的纯 C 客户端库上重建了二进制驱动程序，
• 下推的函数和聚合的表面面积增加了一倍多，
• 还修复了二进制驱动程序中的几个并发错误，详见下文。

记分牌

现在有三个更多的 TPC-H 查询完全下推。这三个查询以前都非常低效，因为由于查询的形状，pg_clickhouse 不得不从 ClickHouse 逐行获取每一行，然后在本地计算子查询（完整图表）：

（ ✔ = 整个查询是单个外部扫描 ） （ ✼ = 已下推，但作为多个远程查询；通常是外部扫描加上一个 InitPlan 扫描。）

Q17 是奖杯：一个相关的子查询，平均计算每个零件的 l_quantity，在规模因子为 1 时，它以前对 600 万行订单项逐外层行评估一次，耗时 32.7 秒。完全下推后，只需要 37 毫秒。这相差了三个数量级，并展示了一个明确的情况，即 pg_clickhouse 在此查询上优于原生 PostgreSQL 自身的执行计划（2.1 秒）。

还有六个查询未下推：Q13、Q15、Q16、Q18、Q20、Q21。Q16 和 Q18 为我们指明了前进方向；pg_clickhouse 已经下推了它们所需的 SQL 形状（IN 和 NOT IN 被去解析为反连接/半连接，如 Q2 和 Q17 中那样）；阻碍它们的是 PostgreSQL 将其子查询展平为反连接/半连接，而这些连接的 输入本身是连接，而去解析器还没有遍历连接两侧的连接树。Q15 和 Q20 遇到了同一问题的变体。这是子查询下推的下一个连贯部分。

完成子查询的故事

十二月的头条功能 是教会规划器将整个相关的 EXISTS 子查询作为单个 LEFT SEMI JOIN 下推，而不是嵌套循环，每个外层行往返一次 ClickHouse。这将指针从 22 个 TPC-H 查询中的 3 个移动到了 12 个。剩下的十个查询有一个共同的问题：规划器根本无法将子查询折叠成连接，因此它留下了一个 SubPlan。这是一个查询计划的一部分，描述了一个作为完整查询执行一部分的单独查询的完整计划，通常每行执行一次。将其下推是我们在路线图上的第五项，我们完成了它（#289），自最新发布（0.10.0）以来。现在，Postgres 中的子查询变成了 ClickHouse 中的子查询：

1EXPLAIN (VERBOSE, COSTS OFF)2SELECT s.sale_id, s.amount FROM sales s3WHERE s.amount > (SELECT1.5*avg(s2.amount) FROM sales s24WHERE s2.item_id = s.item_id)5ORDERBY s.sale_id;

1Foreign Scan on subplan_test.sales s2   Output: s.sale_id, s.amount3   Remote SQL: SELECT sale_id, amount FROM subplan_test.sales r1 WHERE ((r1.amount > (SELECT (1.5 * avg(q1_1.amount)) FROM subplan_test.sales q1_1 WHERE ((q1_1.item_id = (r1.item_id)))))) ORDER BY r1.sale_id ASC NULLS LAST4   SubPlan expr_15     ->  Foreign Scan6           Output: ((1.5 * avg(s2.amount)))7           Relations: Aggregate on (sales s2)8           Remote SQL: SELECT (1.5 * avg(amount)) FROM subplan_test.sales WHERE ((item_id = {p1:Int32}))9(8 rows)

该EXPLAIN 仍然显示 SubPlan 节点（这只是 PostgreSQL 对关联的簿记），但您可以看到顶部的 Remote SQL 包含整个比较，包括子查询，在一个语句中发送给 ClickHouse。相同的机制使得 pg_clickhouse 能够下推整个 TPC-H Q2：一个 Foreign Scan 和一个远程查询。 NOT IN 通过 LEFT ANTI JOIN（v0.1.0 的半连接的反向表亲）获得同样的处理，只要规划器能够证明转换是安全的。

请注意，这些功能在 ClickHouse 25.8 以下版本中均不可用，这些版本不支持相关的子查询 SQL 形状；pg_clickhouse 会在计划时检查服务器版本，并在旧服务器上回退到本地评估，与它一贯对不支持形状的处理方式相同。

正确实现 NOT IN

下推 SQL 是容易的部分。更难的部分是确保它计算出与 PostgreSQL 相同的答案（#315, #317），这本身就是一个兔子洞。ClickHouse 的 IN 基于二值逻辑，而 PostgreSQL 基于三值逻辑。这意味着 x NOT IN (1, NULL) 在 PostgreSQL 中可以是 假 (x=1) 或 NULL，但永远不会是 TRUE。如果天真地下推，这些表达式会在任何涉及 NULL 的比较中静默地反转结果，WHERE NOT IN 返回 PostgreSQL 会过滤掉的行，GROUP BY 将 NULL 组并入 FALSE，等等。这些在普通测试中都不会出现，这正是它在下推扩展中的危险之处；计划看起来正确，但输出却微妙地错误。

V0.10 的修复跟踪了每个表达式的结果如何被使用，因此我们知道查询必须做多少防护才能保持结果一致。这就是兔子洞呈现出 门格海绵 般感觉的地方：

• 过滤条件可以免费将 NULL 视为 FALSE，因此 ClickHouse 的行为在 NOT 之外的条件中是没问题的。
• 值位置或否定需要额外检查空值，以将正确的 Postgres 值注入结果中。
• 如果 pg_clickhouse 能证明操作数不可能为 NULL（通过追溯到非 NULL 常量，或具有 NOT NULL 约束且未被任何外连接重新置空的列，或通过这些的非空操作等），它可以跳过注入 Postgres 行为的防护。

因此，一个带有我们的防护和 Postgres 行为实现的查询看起来像这样：

1EXPLAIN (VERBOSE, COSTS OFF)2SELECT id FROM tnull WHERE xn NOTIN (1, NULL) ORDERBY id;

1Foreign Scan on in_null_test.tnull2   Output: id3   Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASEWHEN xn ISNULLAND notEmpty([1,NULL]) THENNULLWHEN countEqual([1,NULL], xn) >0THENfalseWHEN countEqual([1,NULL], NULL) >0THENNULLELSEtrueEND)) ORDERBY id ASCNULLS LAST4(3rows)

而一个常见的、无 NULL 的情况可以作为普通的本地 IN 发送：

1EXPLAIN (VERBOSE, COSTS OFF)2SELECT id FROM tnull WHERE xn NOTIN (1, 500) ORDERBY id;

1Foreign Scan on in_null_test.tnull2   Output: id3   Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((xn NOTIN (1,500))) ORDERBY id ASCNULLS LAST4(3rows)

后续（#317）将此行为推广到整个 IN 运算符家族（IN, NOT IN, = ANY, = ALL, <> ANY, <> ALL），标量和数组形式都一样，允许它们无条件地下推，而不是在无法证明非空性时回退到本地评估。

此外，我们有一个小错误，即 <> ANY(array) 实际上计算的是 <> ALL，我们在顺手的时候也解决了。

所有这些都基于一个假设，即 ClickHouse 的 IN 确实按照我们描述的二值方式运行。服务器级别的设置（transform_null_in）可以改变这一点。因此我们在默认的 transform_null_in 0 中添加了 pg_clickhouse.session_settings，以确保 ClickHouse 服务器配置文件不会悄无声息地破坏我们刚才描述的防护措施。

立即开始使用 ClickHouse Managed Postgres

想看看 ClickHouse Managed Postgres 如何在您的数据上运作？几分钟内即可开始使用 ClickHouse Cloud，并获得 300 美元的免费额度。

注册

扩展下推范围

除了连接和子查询的工作，下推的个别函数、运算符和聚合函数列表也大幅增加。此处无法完整列出（详情请参阅更新日志），但这里列举了一些广度来源的示例：

• 正则表达式：我们在此前的新闻文章中已经介绍了许多更新，不妨去看看！
• corr、covar_pop/samp
• stddev_pop/samp
• var_pop/samp
• any_value

有序集合聚合函数（#291）：映射到 ClickHouse 的参数化形式。

• percentile_cont/disc → quantile(s)/quantileExactLow

分区聚合（#298）：如果您已将分析密集型分区移至 ClickHouse，而将事务密集型分区保留在 Postgres 中，此功能非常有用。

• 适用于可分解聚合函数，如count、sum、min、max、avg对整数的操作。
• 需要enable_partitionwise_aggregate。

其他一切：

• 格式化和编码： encode(bytea, 'hex'|'base64'|'base64url')（#302）。
• 字符串：三参数ltrim/rtrim/btrim（#307），
• 成本估算：修复了一些成本函数，以确保规划器在 MIN/MAX on ClickHouse 上选择更便宜的选项。（#310）
• 间隔算术扩展至date/timestamp操作数和减法（#301）
• 调整了CURRENT_*/now()/clock_timestamp()系列，以支持会话时区和亚秒精度。

所有这些背后是一个重要的架构变更：内置函数下推现在默认不启用（#245）。早期，任何名称与 ClickHouse 函数匹配的 Postgres 内置函数都会默认下推，这可能导致签名或行为差异静默地改变结果。迫使这一变更的具体案例是三角函数（asin/acos/atanh/acosh）：当这些函数在超出定义域的值上调用时，Postgres 会抛出错误，而 ClickHouse 返回NaN。自 v0.3.0 起，函数仅在显式映射时才会下推，这虽然使列表增长缓慢，但保证了列表在保留 Postgres 语义方面是可靠的。

驱动更新

我们的介绍文章描述了通过采用clickhouse-cpp实现原生协议访问，来对旧版 clickhouse_fdw 进行现代化改造。这已经过时了：在 v0.3.1 中，我们完全用ClickHouse/clickhouse-c替换了它，这是一个作为 git 子模块包含的新 C 客户端（#254）。这次切换不仅仅是依赖升级；将 C++ 异常处理与 PostgreSQL 自身的setjmp/longjmp-基于的错误处理混合在一起曾是崩溃的根源，而 clickhouse-cpp 的一体化结果缓冲意味着内存使用与结果大小成比例增长。相比之下，clickhouse-c 逐块流式传输结果，并将所包含库的构建时间和大小减少了超过75%。

在最初的 clickhouse-c 切换之后，我们继续整合：HTTP 驱动现在也使用 ClickHouse 的原生格式：与二进制驱动相同的编解码方式，取代了旧的基于 TSV 的路径（#328）。这一变更的一个牺牲品是fetch_size，即 HTTP 驱动早期流式工作的批处理选项。原生解码器每次流式传输一个 curl 块，因此该设置无需配置，现已弃用。在写入方面，二进制驱动现在刷新超过 64MiB 的缓冲INSERT/COPY FROM数据（#303），而不是将整个批次保存在内存中。两个驱动都增加了显式的compression（none/lz4/zstd）（#268）和 TLS 控制（secure = on/off/auto、min_tls_version）（#272），取代了从主机名和端口推断 TLS 的启发式方法。类型覆盖范围扩大：我们现在支持两个驱动的读写多维数组（#233），以及通过二进制协议插入Array(Nullable(T))（#316）。

两个驱动现在共享一个二进制编码路径也使得本轮多个可靠性修复成为可能。二进制驱动上的并发外部扫描（例如相关子查询或两个外部表上的嵌套循环连接）过去会在共享连接上发生冲突并崩溃；现在每个并发扫描都有自己的连接（#296），这也修复了重新扫描外部扫描批处理内存上下文中的相关释放后使用问题。我们还修复了一个特定查询结构在执行时因选择无效关系 OID 而失败的问题，该问题出现在选择运行查询的用户时（#319）。

更深入一些，我们还修复了通过 HTTP 插入时间戳时亚秒精度丢失的问题（#300），并且最重要的是，对代码库进行更广泛的静态分析发现并修复了一些其他潜在错误（#313）；这些错误尚未在实地出现，但在它们可能发生之前修复是值得的。

新功能范围

一些新增功能扩展了您可以执行的操作，而不仅仅是自动下推的内容：clickhouse_query(server, sql)针对配置的服务器运行任意查询，并根据您的列定义列表对结果进行类型化，现在支持二进制驱动（#309）。该列定义列表是必需的（Postgres 在开始获取行之前需要知道行的形状），但这也意味着clickhouse_query()无法运行像CREATE TABLE这样不返回行且没有可声明形状的语句。这就是其新同伴clickhouse_perform(server, sql)的用途：一个过程，使用CALL而不是SELECT调用，用于为效果而非行执行的语句（#329）。此外，主要供内部条件逻辑使用，clickhouse_server_version(server)报告连接服务器的版本（#293）。

如果您一直使用clickhouse_raw_query()，它现已弃用，将在下个版本中移除（#329）。请改用clickhouse_query()或CALL clickhouse_perform()而是通过配置的外部服务器进行，该服务器有自己独立的连接处理和驱动程序选择，而不是原始的临时连接字符串。

旧有功能覆盖

我们曾在别处撰文介绍过去版本中的这些功能，但在这里忘了提及，想在此澄清事实。

• 操作符和函数： ->/->> 以及 jsonb_extract_path[_text]() 映射到 ClickHouse 的 子列语法 (#169, #176)。
• 类型： ClickHouse 原生 JSON 类型映射到 Postgres 的 json，并支持相同的操作符。

数组：

• array_cat
• append
• remove
• to_string
• length
• hasAll/hasAny 用于 @>/<@/&&
• 切片语法 (arr[L:U]) 作为 arraySlice()
• 还有更多……

聚合函数：

• ROW_NUMBER
• RANK
• LEAD/LAG
• NTILE

布尔和字符串 (#184): bool_and, bool_or, string_agg 其他所有功能：

• to_char() 带格式字符串验证 (#244)
• split_part() (#206)
• fuzzystrmatch 的 soundex()/levenshtein() (#210)。

剩下什么

除了剩余的六个 TPC-H 查询之外，大部分原始路线图 仍未完成：其余未覆盖的 PostgreSQL 函数、轻量级 DELETE/UPDATE，以及 UNION 下推。阻止 Q15/16/18/20 的 join-tree-on-both-sides 限制是最关键的部分，也是本系列下一篇博客的自然主题，如果我们在下个版本之前没有完成所有六个查询。