You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "JoeryH (via GitHub)" <gi...@apache.org> on 2026/10/14 16:18:53 UTC

[PR] perf: compute using columns once per SELECT, not per item [datafusion]

JoeryH opened a new pull request, #26317:
URL: https://github.com/apache/datafusion/pull/26317

   ## Which issue does this PR close?
   
   - Closes #26313.
   
   ## Rationale for this change
   
   Planning a SELECT costs O(items × input plan size), because `SqlToRel::sql_select_to_rex` calls `LogicalPlan::using_columns()` once per SELECT item. Each call walks the whole input plan, subqueries and expressions included, and the result depends only on the input plan. Nested wide SELECTs (views over views) therefore plan in roughly O(width² × depth²). The issue has the numbers. At 4000 columns × 12 levels, `statement_to_plan` goes from 8.0 s to 4.4 s with this change alone, and to 0.6 s once the `DFSchema` scan from #26314 is also gone.
   
   ## What changes are included in this PR?
   
   - `prepare_select_exprs` computes `plan.using_columns()` once and passes it to `sql_select_to_rex`. `plan_wildcard_options` does the same for a wildcard's `REPLACE` items.
   - A `sql_planner` benchmark case, `logical_nested_select_700_columns_8_levels`. On an M1 Max its query goes from 178 ms to 122 ms in `statement_to_plan`, measured with a standalone harness because the full benchmark needs the clickbench data.
   
   One behaviour difference: if `using_columns()` itself fails, the error now surfaces once for the SELECT instead of once per item through `DataFusionErrorBuilder`. `using_columns` only errors if the tree walk does, so I don't expect this to be observable, but I can keep the old shape with a lazily filled `Option` if you'd prefer.
   
   ## What is the testing strategy for this PR?
   
   The planned output is unchanged, so this relies on the existing `datafusion-sql` tests and sqllogictests, which pass locally. Those cover USING / NATURAL joins and wildcard expansion over them, the cases `using_columns` exists for. The new benchmark case covers the performance side.
   
   ## Are there any user-facing changes?
   
   No. Planning is faster and plans are identical.
   
   ---
   
   Prepared with AI assistance (Claude Code).
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "JoeryH (via GitHub) via github" <gi...@datafusion.apache.org>.
JoeryH commented on code in PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#discussion_r4495525613


##########
datafusion/sql/src/select.rs:
##########
@@ -1003,9 +1003,16 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
     ) -> Result<Vec<SelectExpr>> {
         let mut prepared_select_exprs = Vec::with_capacity(projection.len());
         let mut error_builder = DataFusionErrorBuilder::new();
+        let using_columns = plan.using_columns()?;

Review Comment:
   good find, this caused about a 2% performance regression. making it lazy removed this regression



##########
datafusion/sql/src/select.rs:
##########
@@ -1130,13 +1138,15 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
             rename: options.opt_rename,
         };
         if let Some(replace) = options.opt_replace {
+            let using_columns = plan.using_columns()?;

Review Comment:
   added this to [wildcard.slt](https://github.com/apache/datafusion/pull/26317/changes#diff-865fa640b89d8db42fa46b8ba62dce797b7826bb4477785c2c3751e0e0fea474)



##########
datafusion/core/benches/sql_planner.rs:
##########
@@ -850,6 +850,26 @@ fn criterion_benchmark(c: &mut Criterion) {
         });
     }
 
+    // Nested SELECTs that each name every column of the one below
+    {
+        let columns = |qualifier: &str| {
+            (0..700)
+                .map(|i| format!("{qualifier}.c{i} AS c{i}"))
+                .collect::<Vec<_>>()
+                .join(", ")
+        };
+        let mut query = format!("SELECT {} FROM t700", columns("t700"));
+        for level in 0..8 {
+            query = format!(
+                "SELECT {} FROM ({query}) l{level}",
+                columns(&format!("l{level}"))
+            );
+        }

Review Comment:
   Thanks for the suggestion, I added small width/depth case and qualified-wildcard-only. Here are some of the results
   
   | case        | parent | before | now   |
   |-------------|--------|--------|-------|
   | named 700×8 | 168.6  | 52.3   | 51.0  |
   | named 100×8 | 8.70   | 5.88   | 5.90  |
   | q.* 700×1   | 3.96   | 4.04   | 3.97  |
   | q.* 700×8   | 18.31  | 18.63  | 18.38 |



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "JoeryH (via GitHub) via github" <gi...@datafusion.apache.org>.
JoeryH commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-7012219129

   thanks @kosiew ! I'm a little bit confused now, should I put the benchmarks back into this PR?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "kosiew (via GitHub) via github" <gi...@datafusion.apache.org>.
kosiew commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-7004834284

   [benchmark run](https://github.com/apache/datafusion/pull/27582#issuecomment-7004624903):
   
   ```
   group                                                     HEAD                                   bench-26317
   -----                                                     ----                                   -----------
   logical_nested_qualified_wildcard_700_columns_1_levels    1.00      3.4±0.01ms        ? ?/sec    1.00      3.4±0.01ms        ? ?/sec
   logical_nested_qualified_wildcard_700_columns_8_levels    1.00     15.5±0.04ms        ? ?/sec    1.01     15.6±0.04ms        ? ?/sec
   logical_nested_select_100_columns_1_levels                1.05  1457.5±35.76µs        ? ?/sec    1.00   1393.4±6.39µs        ? ?/sec
   logical_nested_select_100_columns_8_levels                1.35      7.1±0.03ms        ? ?/sec    1.00      5.3±0.01ms        ? ?/sec
   logical_nested_select_700_columns_1_levels                1.23     11.6±0.05ms        ? ?/sec    1.00      9.4±0.02ms        ? ?/sec
   logical_nested_select_700_columns_8_levels                2.65    119.8±1.20ms        ? ?/sec    1.00     45.2±0.15ms        ? ?/sec
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6986276508

   > No. Planning is faster and plans are identical.
   
   
   
   What evidence do we haev that planning is faster?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "kosiew (via GitHub) via github" <gi...@datafusion.apache.org>.
kosiew commented on code in PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#discussion_r4492030722


##########
datafusion/sql/src/select.rs:
##########
@@ -1130,13 +1138,15 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
             rename: options.opt_rename,
         };
         if let Some(replace) = options.opt_replace {
+            let using_columns = plan.using_columns()?;

Review Comment:
   As an optional regression test, could you add a compact SLT combining multiple REPLACE expressions with a USING join, asserting both replacement values and the deduplicated join key? Existing tests cover these features separately, but their combination would specifically protect the newly shared normalization input.



##########
datafusion/core/benches/sql_planner.rs:
##########
@@ -850,6 +850,26 @@ fn criterion_benchmark(c: &mut Criterion) {
         });
     }
 
+    // Nested SELECTs that each name every column of the one below
+    {
+        let columns = |qualifier: &str| {
+            (0..700)
+                .map(|i| format!("{qualifier}.c{i} AS c{i}"))
+                .collect::<Vec<_>>()
+                .join(", ")
+        };
+        let mut query = format!("SELECT {} FROM t700", columns("t700"));
+        for level in 0..8 {
+            query = format!(
+                "SELECT {} FROM ({query}) l{level}",
+                columns(&format!("l{level}"))
+            );
+        }

Review Comment:
   For future scaling coverage, consider adding a small width/depth matrix with a shallow control while retaining this benchmark. A qualified-wildcard-only variant would also help validate the traversal regression noted above. Keep SQL generation outside the timed closure and compare identical cases on parent and candidate without timing assertions in correctness tests.



##########
datafusion/sql/src/select.rs:
##########
@@ -1003,9 +1003,16 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
     ) -> Result<Vec<SelectExpr>> {
         let mut prepared_select_exprs = Vec::with_capacity(projection.len());
         let mut error_builder = DataFusionErrorBuilder::new();
+        let using_columns = plan.using_columns()?;

Review Comment:
   The unconditional `using_columns()` call adds full-plan traversal to wildcard-only projections that previously avoided it. For nested `SELECT q.*` wrappers, this can add O(W × D²) expression visits; plain `SELECT *` also duplicates discovery already performed during expansion. Please compute the shared value only when an `UnnamedExpr` or `ExprWithAlias` needs it, or initialize it lazily, while preserving discovery for REPLACE expressions. Validate the explicit-expression speedup against a qualified-wildcard-only control using traversal counts or comparable parent/candidate timings.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6992524155

   Thanks @JoeryH  -- can you please split the tests into their own PR so we can run them with our own bot runner to confirm?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "JoeryH (via GitHub) via github" <gi...@datafusion.apache.org>.
JoeryH commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6984114246

   @alamb this is ready to merge if you have a moment 🙏 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "kosiew (via GitHub) via github" <gi...@datafusion.apache.org>.
kosiew commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-7029801450

   @JoeryH 
   
   > should I put the benchmarks back into this PR?
   
   No, I included the above to verify the performance impact.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "JoeryH (via GitHub) via github" <gi...@datafusion.apache.org>.
JoeryH commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6691078113

   thanks so much for the review @kosiew. I addressed your comments


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "JoeryH (via GitHub) via github" <gi...@datafusion.apache.org>.
JoeryH commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6993215044

   @alamb done: the benchmark cases are in #27572, and this PR now holds only the planner change and its slt. Once #27572 lands I'll merge main in here so the bot can run `sql_planner` on it.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6986262904

   We normally leave PRs open for at least 24 hours after approval to give time for others to review if they want to. We'll get this merged shortly
   
   Thanks @JoeryH 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "JoeryH (via GitHub) via github" <gi...@datafusion.apache.org>.
JoeryH commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6991111452

   > > No. Planning is faster and plans are identical.
   > 
   > What evidence do we haev that planning is faster?
   
    The sql_planner benches added here (cargo bench -p datafusion --bench sql_planner -- logical_nested; CLICKBENCH_DATA_PATH=datafusion/core/tests/data/clickbench_hits_10.parquet is enough, the nested cases plan against an in-memory 700-column table). Rerun just now, main vs this PR:
   
   | case                | main     | this PR |
   |---------------------|----------|---------|
   | 700 cols × 8 levels | 168.2 ms | 50.3 ms |
   | 700 × 1             | 14.7 ms  | 11.0 ms |
   | 100 × 8             | 8.5 ms   | 5.9 ms  |
   | 100 × 1             | 1.66 ms  | 1.57 ms |
   | q.* 700 × 8         | 18.2 ms  | 18.3 ms |
   | q.* 700 × 1         | 3.96 ms  | 4.01 ms |


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] perf: compute using columns once per SELECT, not per item [datafusion]

Posted by "JoeryH (via GitHub) via github" <gi...@datafusion.apache.org>.
JoeryH commented on PR #26317:
URL: https://github.com/apache/datafusion/pull/26317#issuecomment-6958525719

   @kosiew thanks! anyone we should ask for a review?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org