You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "irenjj (via GitHub)" <gi...@apache.org> on 2025/05/08 14:06:57 UTC
[PR] Add configuration for eliminating sort in subquery [datafusion]
irenjj opened a new pull request, #15993:
URL: https://github.com/apache/datafusion/pull/15993
## Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
-->
- Part of #15886
## Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
## What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
## Are these changes tested?
<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code
If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
-->
## Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please add the `api change` label.
-->
--
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] Add configuration for eliminating sort in subquery [datafusion]
Posted by "berkaysynnada (via GitHub)" <gi...@apache.org>.
berkaysynnada commented on PR #15993:
URL: https://github.com/apache/datafusion/pull/15993#issuecomment-2869869805
@irenjj I've set the default value of this config to "false", and auto-complete the slt tests. This is one of the changes:
<img width="734" alt="image" src="https://github.com/user-attachments/assets/a277abe6-c00f-4838-bbbd-4b5270adcd2c" />
as I said, even if the logical planner doesn't remove the Sort, it's still eliminated in the physical planning stage. I also thought it's so because of the count(), but if I replace it by another aggregate fn, a SortExec still doesn't emerge in the physical plan (I also consider subquery brings unique results, so remove the inner group by also to be sure). TLDR, we cannot disable subquery sort elimination by disabling it from the logical world only.
--
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] Add configuration for eliminating sort in subquery [datafusion]
Posted by "irenjj (via GitHub)" <gi...@apache.org>.
irenjj commented on PR #15993:
URL: https://github.com/apache/datafusion/pull/15993#issuecomment-2865560162
Thanks @kosiew and @berkaysynnada for your review, PTAL👀
--
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] Add configuration for eliminating sort in subquery [datafusion]
Posted by "irenjj (via GitHub)" <gi...@apache.org>.
irenjj commented on PR #15993:
URL: https://github.com/apache/datafusion/pull/15993#issuecomment-2869891230
> @irenjj I've set the default value of this config to "false", and auto-complete the slt tests. This is one of the changes:
>
> <img alt="image" width="734" src="https://private-user-images.githubusercontent.com/124376117/442507523-a277abe6-c00f-4838-bbbd-4b5270adcd2c.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NDY5NzI5MjYsIm5iZiI6MTc0Njk3MjYyNiwicGF0aCI6Ii8xMjQzNzYxMTcvNDQyNTA3NTIzLWEyNzdhYmU2LWMwMGYtNDgzOC1iYmJkLTRiNTI3MGFkY2QyYy5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjUwNTExJTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI1MDUxMVQxNDEwMjZaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT0yZDAyY2MyN2Y4YzEwMzY3OTgzMjdiYTlhNGRiYzU2Nzg0Y2Y0YzI3MTQxOGZhYzRkYTkzNDdiY2FlNDQyZWM0JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.nE_BdNz_G9eRnMJ27kO3zkZdbkATZdd-3IwHdWgel1A">
> as I said, even if the logical planner doesn't remove the Sort, it's still eliminated in the physical planning stage. I also thought it's so because of the count(), but if I replace it by another aggregate fn, a SortExec still doesn't emerge in the physical plan (I also consider subquery brings unique results, so remove the inner group by also to be sure). TLDR, we cannot disable subquery sort elimination by disabling it from the logical world only.
That's right, we need to process this more elegantly.
--
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] Add configuration for eliminating sort in subquery [datafusion]
Posted by "irenjj (via GitHub)" <gi...@apache.org>.
irenjj commented on PR #15993:
URL: https://github.com/apache/datafusion/pull/15993#issuecomment-2865547053
> Hi @irenjj. Thank you working on this, but did you try to set that config to false and run the tests?
>
> I think people should see here how the plans are changed if we do so (perhaps you can open another PR for that -- I don't know if there is an easier way)
>
> As I said in the discussion, I afraid our physical rules are not ready for this. we might either miss some sort eliminations, or possibly eliminate some subquery sorts even if config says opposite. Therefore; I was suggesting enabling this option from lower level to higher. If people still consent this, I couldn't say more but we should add some warnings at least.
Yep, I've tried to set config to false and it can forbid eliminating sort in subquery. I'll add a test.
>If I approach this case practically, when "order by" clauses are given in subqueries: these are converted into SortExecs at somewhere in the plan. However, in enforce_sorting, we don't track ordering requirements through SortExecs directly (otherwise, we wouldn't be able to eliminate truly necessary SortExecs). Instead, we track the requirement by inserting OutputRequirementExec at the top of the plan, which corresponds to the global ordering - that is the ordering expected when an explicit ORDER BY is given in the outermost query.
>
> If we decide to introduce a new config for this setting, we first need to improve the optimizer phase. Specifically, OutputRequirementExecs could be inserted into intermediate nodes in the plan, meaning the subplan beneath must guarantee to bring the required ordering at that point.
So, TLDR, unless we adapt the current enforce_sorting rule accordingly, we risk losing the ability to eliminate unnecessary SortExecs in some cases.
This is a very good approach. We can implement it later to replace the current approach of adding configuration
--
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] Add configuration for eliminating sort in subquery [datafusion]
Posted by "berkaysynnada (via GitHub)" <gi...@apache.org>.
berkaysynnada commented on PR #15993:
URL: https://github.com/apache/datafusion/pull/15993#issuecomment-2865433405
Hi @irenjj. Thank you working on this, but did you try to set that config to false and run the tests?
I think people should see here how the plans are changed if we do so (perhaps you can open another PR for that -- I don't know if there is an easier way)
As I said in the discussion, I afraid our physical rules are not ready for this. we might either miss some sort eliminations, or possibly eliminate some subquery sorts even if config says opposite. Therefore; I was suggesting enabling this option from lower level to higher. If people still consent this, I couldn't say more but we should add some warnings at least.
--
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] Add configuration for eliminating sort in subquery [datafusion]
Posted by "irenjj (via GitHub)" <gi...@apache.org>.
irenjj closed pull request #15993: Add configuration for eliminating sort in subquery
URL: https://github.com/apache/datafusion/pull/15993
--
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] Add configuration for eliminating sort in subquery [datafusion]
Posted by "kosiew (via GitHub)" <gi...@apache.org>.
kosiew commented on code in PR #15993:
URL: https://github.com/apache/datafusion/pull/15993#discussion_r2080888952
##########
datafusion/sql/src/relation/mod.rs:
##########
@@ -241,7 +246,7 @@ fn optimize_subquery_sort(plan: LogicalPlan) -> Result<Transformed<LogicalPlan>>
}
match c {
LogicalPlan::Sort(s) => {
- if !has_limit {
+ if !has_limit && enalbe_eliminate {
Review Comment:
enable_eliminate
##########
datafusion/sql/tests/sql_integration.rs:
##########
@@ -3385,6 +3387,7 @@ fn ident_normalization_parser_options_ident_normalization() -> ParserOptions {
map_varchar_to_utf8view: false,
enable_options_value_normalization: false,
collect_spans: false,
+ enable_eliminate_subquery_sort: true,
}
}
Review Comment:
Would it be a good idea to add tests for
`enable_eliminate_subquery_sort: false`
too?
##########
datafusion/sql/src/relation/mod.rs:
##########
@@ -226,7 +228,10 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
}
}
-fn optimize_subquery_sort(plan: LogicalPlan) -> Result<Transformed<LogicalPlan>> {
+fn optimize_subquery_sort(
+ plan: LogicalPlan,
+ enalbe_eliminate: bool,
Review Comment:
This should be
```rust
enable_eliminate: bool
```
--
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