Repository navigation
Improve filter push-down #19929
Description
This issue covers two related filter push-down improvements.
Pass previously pushed filters to supports_filters_pushdown
Currently, the optimization does not pass filters that were pushed in a previous run (TableScan::filters) to TableProvider::supports_filters_pushdown(...).
If the optimizer runs multiple times, it may try to push filters into the table provider multiple times. In our DataFusion-based project, supports_filters_pushdown(...) has context-dependent behavior: the provider supports any single filter like column = value, but not multiple such filters at the same time.
Consider the following optimizer pipeline pattern:
- Try to push
a = 1, b = 1.
supports_filters_pushdownreturns[Exact, Inexact]
OK: the optimizer records thata = 1is pushed and creates a filter node forb = 1.
...
Another optimization iteration.
- Try to push b = 1.
supports_filters_pushdownreturns[Exact]. Of course, the table provider can’t remember
all previously pushed filters, so it has no choice but to answerExact.
Now, the optimizer thinks the conjunctiona = 1 AND b = 1is supported exactly, but it is not.
To prevent this problem, I suggest passing filters that were already pushed into the scan earlier to supports_filters_pushdown(...).
Do not assume that filter support decision is stable
Consider the next scenario:
-
supports_filters_pushdownreturnsExacton some filter, e.g. "a = 1", where column "a" is not
required by the query projection. -
"a" is removed from the table provider projection by "optimize projection" rule.
-
supports_filters_pushdownchanges a decision and returnsInexacton this filter the next time.
For example, input filters were changed and it prefers to use a new one. -
"a" is not returned to the table provider projection which leads to filter that references a column which is
not a part of the schema.
Suggest to extend logic with the following actions:
-
Collect columns that are not used in the current table provider projection, but required for filter
expressions. Call itadditional_projection. -
If
additional_projectionis empty -- leave all as is. -
Otherwise extend a table provider projection and wrap a plan with an additional projection node
to preserve schema used prior to this rule.
Activity
askalt commentedon Jan 21, 2026
take
adriangb commentedon Jan 21, 2026
I feel this is somewhat related to #19894. I think we could avoid these sorts of issues if we accumulated filters on the
TableScannode without modifying projections, etc. and only at the end of the optimizer run we do a single call into theTableProviderto sort outExactfilters and the final projectionfix: pass filters pushed earlier to `supports_filter_pushdown`
fix: pass filters pushed earlier to `supports_filter_pushdown`
askalt commentedon Jan 26, 2026
Mentioned improvements could fix the artifacts, I pushed a small fix that just makes the rule itself idempotent regarding a table scan, maybe it will be useful.
fix: pass filters pushed earlier to `supports_filter_pushdown`
xudong963 commentedon Jan 27, 2026
It sounds like the case will cause a wrong result, potentially, do you have a case for it?
askalt commentedon Jan 27, 2026
In our DF based project we have a custom table provider that roughly can be described as a provider that supports only single filter exactly (reading from the tree-like index), but not a conjunction (internal engine can read from the single index at the moment, but not both indexes same time), it is mocked here as a part of the possible rule fix https://github.com/apache/datafusion/pull/20003/changes#diff-3fbd6073c97534f759a8c4cdab71bd506618972220eb58225c38cabf7061981bR4318
Yes, it is so, we have written SQL fuzzer (generate and select some projection + filter from custom provider based on our in-memory column engine) which helped to find and investigate this issue.
alamb commentedon Jan 27, 2026
What do you think about extending the "can pushdown filters" API somehow to include filters that are already planned to be pushed down?
fix: pass filters pushed earlier to `supports_filter_pushdown`
fix: pass filters pushed earlier to `supports_filter_pushdown`
fix: pass filters pushed earlier to `supports_filter_pushdown`
fix: pass filters pushed earlier to `supports_filter_pushdown`
fix: pass filters pushed earlier to `supports_filter_pushdown`