You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "lordk911 (via GitHub)" <gi...@apache.org> on 2023/11/30 10:28:31 UTC
[I] [Enhancement] The query optimizer's optimization of subqueries is not good enough. [doris]
lordk911 opened a new issue, #27830:
URL: https://github.com/apache/doris/issues/27830
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Description
A 'insert into select ' statement , which the 'select' part with subquery and [CTE](https://dev.mysql.com/doc/refman/8.0/en/with.html) :
```
insert into table_result
with temp_bhvr as (
select a,b,c from table_a where 1=1 and exists (
select * from table_b
)
)
select a,b,c,d,e,f from temp_bhvr bdfs left join (
select * from table_c where 1=1 and ...
)wtcui on bdfs.a = wtcui.a
```
Through the query profile, it was found that all records of table_c seem to be fully loaded into memory.
<img width="878" alt="企业微信截图_1885fc54-4942-4674-8f76-cd3794c9c104" src="https://github.com/apache/doris/assets/19989300/9883a7e9-dba7-4da8-9f75-a28b6618654b">
It is true that, the SQL can be write better. If I move the table_c out of subquery and do a left join directly. The execution time of SQL will be significantly reduced. Will the query optimizer could do this transparently?
### Solution
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
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: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Enhancement] The query optimizer's optimization of subqueries is not good enough. [doris]
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #27830:
URL: https://github.com/apache/doris/issues/27830#issuecomment-1837698747
https://repo.maven.apache.org/maven2/org/apache/doris/spark-doris-connector-3.3_2.12/1.3.0/
--
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: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Enhancement] The query optimizer's optimization of subqueries is not good enough. [doris]
Posted by "morningman (via GitHub)" <gi...@apache.org>.
morningman closed issue #27830: [Enhancement] The query optimizer's optimization of subqueries is not good enough.
URL: https://github.com/apache/doris/issues/27830
--
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: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Enhancement] The query optimizer's optimization of subqueries is not good enough. [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27830:
URL: https://github.com/apache/doris/issues/27830#issuecomment-3287229954
We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!
--
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: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Enhancement] The query optimizer's optimization of subqueries is not good enough. [doris]
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #27830:
URL: https://github.com/apache/doris/issues/27830#issuecomment-1835252644
I've tried to use :
`set enable_nereids_dml=true;`
but not help.
--
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: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org