Is your feature request related to a problem or challenge?
Repeated expensive expressions could be evaluated once per row instead of many times, but a projection-pushdown interaction currently prevents that on file scans.
The logical Common Subexpression Elimination (CSE) pass extracts a repeated expensive expression (e.g. power(a, 2)) into a single intermediate projection referenced by column, so it is evaluated once per row. For file sources that can absorb computed projections (e.g. Parquet), the physical projection-pushdown rule then merges that intermediate projection into the scan's DataSourceExec, substituting the expression back into every reference site, so it ends up evaluated once per reference instead of once in total. Results are correct — this is purely an optimization.
Example:
SELECT power(a, 2) + b, power(a, 2) - b, power(a, 2) * c FROM t;
Logical CSE produces __common_expr_1 = power(a, 2) referenced 3×. After physical
projection pushdown the DataSourceExec projection becomes:
projection=[power(a, 2) + b, power(a, 2) - b, power(a, 2) * c]
i.e. power(a, 2) is evaluated three times per row instead of once.
This is the performance sibling of #23220 (which is the correctness case, for
volatile expressions). It only reproduces on file scans (Parquet/CSV), not
in-memory tables.
Describe the solution you'd like
Do not merge a projection into a file source when the merge would duplicate an expensive (non-trivial) computed expression that CSE deduplicated — i.e. extend the volatility guard added for #23220 to also cover repeated scalar-function calls, keeping the extracted expression in a single ProjectionExec above the scan.
Describe alternatives you've considered
A general physical CSE rule (#12599) would also address this. The implementation (#13046) was not merged — it was kept out of the default optimizer as it mainly benefits front-ends that build physical plans directly (e.g. Comet), and had open questions around metric accounting for the extra projections. A targeted guard in projection pushdown is smaller and self-contained.
Additional context
Related: #23220 (correctness case), #10337, #12599.
I'm working on a fix that extends the #23220 guard to also block re-inlining an expensive scalar-function call referenced more than once, together with a cse_projection_pushdown benchmark. Measured improvement on repeated expressions ranges from ~7% (abs) to ~45% (power(a, 2)), with no change on queries that have no repeated expressions.
Is your feature request related to a problem or challenge?
Repeated expensive expressions could be evaluated once per row instead of many times, but a projection-pushdown interaction currently prevents that on file scans.
The logical Common Subexpression Elimination (CSE) pass extracts a repeated expensive expression (e.g.
power(a, 2)) into a single intermediate projection referenced by column, so it is evaluated once per row. For file sources that can absorb computed projections (e.g. Parquet), the physical projection-pushdown rule then merges that intermediate projection into the scan'sDataSourceExec, substituting the expression back into every reference site, so it ends up evaluated once per reference instead of once in total. Results are correct — this is purely an optimization.Example:
Logical CSE produces
__common_expr_1 = power(a, 2)referenced 3×. After physicalprojection pushdown the
DataSourceExecprojection becomes:i.e.
power(a, 2)is evaluated three times per row instead of once.This is the performance sibling of #23220 (which is the correctness case, for
volatile expressions). It only reproduces on file scans (Parquet/CSV), not
in-memory tables.
Describe the solution you'd like
Do not merge a projection into a file source when the merge would duplicate an expensive (non-trivial) computed expression that CSE deduplicated — i.e. extend the volatility guard added for #23220 to also cover repeated scalar-function calls, keeping the extracted expression in a single
ProjectionExecabove the scan.Describe alternatives you've considered
A general physical CSE rule (#12599) would also address this. The implementation (#13046) was not merged — it was kept out of the default optimizer as it mainly benefits front-ends that build physical plans directly (e.g. Comet), and had open questions around metric accounting for the extra projections. A targeted guard in projection pushdown is smaller and self-contained.
Additional context
Related: #23220 (correctness case), #10337, #12599.
I'm working on a fix that extends the #23220 guard to also block re-inlining an expensive scalar-function call referenced more than once, together with a
cse_projection_pushdownbenchmark. Measured improvement on repeated expressions ranges from ~7% (abs) to ~45% (power(a, 2)), with no change on queries that have no repeated expressions.