Predicate Pushdown: Move Filters Closer to the Data谓词下推：把过滤条件移到更靠近数据的位置

What Is Predicate Pushdown?什么是谓词下推？

A predicate is an expression that evaluates to true, false or unknown under SQL's three-valued logic. Pushdown changes where that expression is evaluated, not what it means. The optimizer must prove that moving it across joins, aggregations, windows, casts and source boundaries preserves the same rows, duplicates and NULL behavior.

谓词是在SQL三值逻辑下求值为真、假或未知的表达式。下推改变的是表达式的求值位置，而不是含义。优化器必须证明：把条件跨越连接、聚合、窗口、类型转换或数据源边界移动后，行、重复项与NULL行为仍然一致。

How Predicate Pushdown Works Through a Query Plan谓词下推如何穿过查询计划

1. Resolve the predicate.解析谓词。

   The engine binds names and types, expands views where supported, and represents the condition in its logical plan. Valid syntax does not guarantee pushdown.

   引擎绑定名称与类型，在支持时展开视图，并在逻辑计划中表示条件。语法合法并不保证可以下推。

2. Prove a legal movement.证明移动合法。

   Rewrite rules determine whether the predicate can cross projections, joins, aggregates or other operators without changing semantics. Sometimes only one conjunct is movable.

   重写规则判断谓词能否在不改变语义的情况下跨越投影、连接、聚合或其他算子。有时只有一个合取项可以移动。

3. Ask the scan or connector what it supports.询问扫描器或连接器支持范围。

   A file reader may accept limited comparisons; a connector may translate expressions to a remote dialect. Capability varies by type, operator, function and release.

   文件读取器可能只接受有限比较；连接器可能把表达式翻译为远端方言。能力会因类型、操作符、函数与版本而变化。

4. Split accepted and residual filters.拆分已接受与残余过滤。

   The source handles the accepted constraint. The engine keeps a residual when the source returns a safe superset, handles only part, or cannot guarantee identical semantics.

   数据源执行已接受约束；当源端返回安全超集、只处理部分条件或无法保证语义一致时，引擎保留残余过滤。

5. Cost and execute the new plan.估算并执行新计划。

   The optimizer compares legal placements using estimated selectivity, scan, transfer and source costs. Runtime reduction can differ from estimates, so measurement remains necessary.

   优化器利用估算选择率、扫描、传输与源端成本比较合法位置。实际减少量可能偏离估算，因此仍需测量。

Predicate Pushdown vs Partition Pruning and Projection Pushdown谓词下推与分区裁剪、投影下推有什么区别？

These mechanisms can compound. A date condition may prune partitions, row-group statistics may skip blocks, projection pushdown may avoid unused columns, and a residual filter may remove nonmatches inside retained groups. Report each effect separately.

这些机制可以叠加：日期条件裁剪分区，行组统计跳过数据块，投影下推避免读取无用列，残余过滤再删除保留行组中的不匹配行。应分别报告每种效果。

Semantic Safety Comes Before Filter Placement过滤位置必须服从语义安全

A pushdown is legal only when the moved expression produces an equivalent multiset of rows. SQL's NULL and duplicate semantics make apparently obvious rewrites dangerous. Source translation adds risk because systems may compare strings, timestamps, decimals or invalid values differently.

只有移动后的表达式产生等价行多重集时，下推才合法。SQL的NULL与重复项语义会让看似显然的重写变得危险；源端翻译还会增加风险，因为不同系统比较字符串、时间戳、小数或无效值的方式可能不同。

Why Predicate Pushdown Does Not Happen为什么谓词下推没有发生？

A filter can remain above the scan because movement is illegal, the implementation lacks a translation, the connector rejects the expression, or another placement is estimated cheaper. Diagnose the category before rewriting SQL.

过滤留在扫描上方，可能是移动不合法、实现缺少翻译、连接器拒绝表达式，或其他位置估算成本更低。重写SQL前，应先判断类别。

• Expression shape: a function or cast wraps the column; an unsupported operator, correlated subquery or complex disjunction blocks translation.表达式形态：函数或转换包裹源列，或不受支持的操作符、相关子查询、复杂析取阻止翻译。
• Operator boundary: an aggregate, window, limit, set operation or outer join changes which rows the predicate may see.算子边界：聚合、窗口、限制、集合操作或外连接改变了谓词可见的行。
• Source mismatch: types, collation, time zone, decimal scale, NULL or function semantics differ.源端不匹配：类型、排序规则、时区、小数精度、NULL或函数语义不同。
• Capability and permissions: the connector lacks support, remote syntax cannot express it, or a function is not allowed.能力与权限：连接器不支持、远端语法无法表达，或函数不允许调用。
• Late values: parameters or variables may prevent a compile-time constraint in some products.延迟值：某些产品中的参数或变量可能阻止编译期约束。
• Cost: a legal pushdown may lose when estimated selectivity is weak or source/network costs favor local work.成本：估算选择性较弱，或源端/网络成本支持本地执行时，合法下推也可能不采用。

Predicate Pushdown SQL Example谓词下推SQL示例

Consider this hypothetical query. Names and numbers are illustrative; they do not describe a customer or benchmark.

考虑下面的假设查询。名称与数字仅用于说明，不代表客户或基准测试。

SELECT o.order_id, c.segment
FROM remote_orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-07-01'
  AND o.status = 'OPEN';

Without connector pushdown, the remote scan might return all orders to the coordinator, which applies both conditions. With safe pushdown, the connector can generate a remote request containing the date and status constraints, so only qualifying rows cross the network before the local join. A local residual may remain as a safety check.

如果连接器没有下推，远端扫描可能把所有订单返回协调器，再执行两个条件。安全下推后，连接器可以生成包含日期与状态约束的远端请求，使只有符合条件的行在本地连接前跨越网络。本地残余过滤仍可能保留作为安全检查。

How to Verify Predicate Pushdown Step by Step如何逐步验证谓词下推

1. Freeze the baseline.固定基线。

   Record query, versions, parameters, cache state, concurrency and source load. Capture results and a plan before changes.

   记录查询、版本、参数、缓存状态、并发与源端负载，并在变更前保存结果与计划。

2. Locate filters and boundaries.定位过滤与边界。

   Mark scan predicates, join conditions, residuals, exchanges, connectors and remote operators. Identify the earliest legal placement.

   标记扫描谓词、连接条件、残余过滤、数据交换、连接器与远端算子，并确定最早合法位置。

3. Inspect the delegated expression.检查已委派表达式。

   Read scan properties, pushed-filter fields, remote SQL or API parameters. Confirm types, casts and literal boundaries.

   读取扫描属性、下推字段、远端SQL或API参数，并核对类型、转换与字面值边界。

4. Measure both sides.测量边界两侧。

   Compare rows and bytes scanned/returned, transfer, files or groups opened, source CPU/I/O, coordinator work, latency and variance.

   比较扫描/返回行与字节、传输、打开的文件或行组、源端CPU/I/O、协调器工作、延迟与波动。

5. Prove result equivalence.证明结果等价。

   Compare counts, keys, duplicates, NULLs and totals over typical and adversarial fixtures. A faster wrong result fails.

   在典型与对抗样本上比较计数、键、重复项、NULL与汇总。结果错误时，再快也不合格。

6. Test one reversible change.测试一个可逆变更。

   Change one supported expression, connector option, layout or access path at a time. Keep rollback and rerun the evidence set.

   每次只修改一个受支持的表达式、连接器选项、布局或访问路径。保留回滚并重跑证据集。

Review Filter Structure Before Engine-Native Pushdown Testing在引擎原生下推测试前审查过滤结构

Prepare sanitized, complete SQL and identify its dialect. The visible InfiniSynapse SQL Complexity Checker performs browser-side static analysis of nested queries, CTEs, joins, windows, aggregations and dialect-specific constructs. It can help locate filters wrapped in complex query blocks and prioritize plan inspection.

请准备经过脱敏的完整SQL，并确认方言。InfiniSynapse SQL Complexity Checker会在浏览器中静态分析嵌套查询、CTE、连接、窗口、聚合与方言特定结构，可帮助定位复杂查询块中的过滤并安排计划检查优先级。

The checker does not execute SQL, inspect schema, partitions, row-group metadata, connectors, generated remote SQL or plans. It cannot prove pushdown or predict bytes saved. Use it for structural review, then collect engine-native evidence. The existing InfiniSynapse SQL query optimization guide explains the broader workflow; the local query optimization guide, cost based optimizer guide and data federation guide provide related context without claiming these local files are deployed.

该检查器不会执行SQL，不会检查Schema、分区、行组元数据、连接器、生成的远端SQL或计划，不能证明下推，也不能预测节省的字节。完成结构审查后，应收集引擎原生证据。现有InfiniSynapse SQL查询优化指南解释更广泛流程；本地查询优化指南、基于成本的优化器指南与数据联邦指南提供相关背景，但不声称这些本地文件已部署。

Predicate Pushdown FAQ谓词下推常见问题

Official Predicate Pushdown Sources谓词下推权威来源

• Trino documentation: predicate and other pushdown optimizationsTrino文档：谓词及其他下推优化
• Trino connector documentation: filter contracts and semantic safetyTrino连接器文档：过滤契约与语义安全
• PostgreSQL documentation: postgres_fdw remote conditionsPostgreSQL文档：postgres_fdw远端条件
• Microsoft Learn: verifying PolyBase external pushdownMicrosoft Learn：验证PolyBase外部下推
• AWS Athena documentation: pushdown in Parquet and ORCAWS Athena文档：Parquet与ORC下推
• Apache Parquet documentation: statistics, dictionaries and Bloom filtersApache Parquet文档：统计信息、字典与布隆过滤器

These first-party sources cover different products and layers. Support, syntax, plan labels, remote-query visibility and metrics vary by engine, connector, source and version. Verify the exact environment before changing SQL, connector configuration or physical layout.

这些一手资料针对不同产品与层级。支持范围、语法、计划标记、远端查询可见性与指标会随引擎、连接器、数据源和版本变化。修改SQL、连接器配置或物理布局前，必须核对准确环境。