You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "trikker (via GitHub)" <gi...@apache.org> on 2023/12/22 07:53:33 UTC
[I] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
trikker opened a new issue, #28868:
URL: https://github.com/apache/doris/issues/28868
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Version
2.0.3
### What's Wrong?
The SQL is too slow and it executes fast after I rewrite the SQL.
The original SQL and rewrited SQL and table structure are in attatchment file because they are too long and cannot be written in issue.
[slow_sql.log](https://github.com/apache/doris/files/13749640/slow_sql.log)
### What You Expected?
The query plan should realize that the field stmt is too large and should read the field after fitering the top 100 rows by where condition. That is something like "late materialization". Doris's document has introduction about late materialization in https://doris.apache.org/zh-CN/docs/query-acceleration/join-optimization/doris-join-optimization?_highlight=%E5%BB%B6%E8%BF%9F&_highlight=%E7%89%A9%E5%8C%96#runtime-filter--join-%E4%BC%98%E5%8C%96
### How to Reproduce?
It is easy to reproduce but my data are too large and cannot upload the data.
### Anything Else?
How can I set variables or parameters to make the SQL execute fast?
### 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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "elsa0520 (via GitHub)" <gi...@apache.org>.
elsa0520 commented on issue #28868:
URL: https://github.com/apache/doris/issues/28868#issuecomment-1892043176
Doris support the lazy materialized in storage layer which means that If there is where predicate can push down to storage layer the big size column could be lazy ready in storage layer. —— Not suitable for your case.
BTW could your please supply your Doris version ?
--
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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "morningman (via GitHub)" <gi...@apache.org>.
morningman closed issue #28868: [Bug] query plan can be rewrited to get better performance when reading big text column
URL: https://github.com/apache/doris/issues/28868
--
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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "trikker (via GitHub)" <gi...@apache.org>.
trikker commented on issue #28868:
URL: https://github.com/apache/doris/issues/28868#issuecomment-1892122121
> The order by operator is a blocking operator which means Doris need to load all of data (include all columns) in memory. 100KB * 150K rows = 15000000 kb => 15G The error message " process memory used 180.24 GB exceed limit 151.01 GB or sys available memory 38.30 GB less than low water mark 16.00 GB. " May be your process is running other query which already cost lots of memory. So could your please only run this query and confirm memory used by this query ?
100k-150k is the average rows in one partition(one day). The SQL queries the range of two months. Which is about 300GB per month. In MySQL, the table ibd file is about 4GB per partition. That is at least 120GB per month and 240GB of two months. 150GB is limited by setting "mem_limit = 30%" in be.conf. The server's memory is 512GB.
--
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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "elsa0520 (via GitHub)" <gi...@apache.org>.
elsa0520 commented on issue #28868:
URL: https://github.com/apache/doris/issues/28868#issuecomment-1892142316
--
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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "elsa0520 (via GitHub)" <gi...@apache.org>.
elsa0520 commented on issue #28868:
URL: https://github.com/apache/doris/issues/28868#issuecomment-1892149731
The code strategy is above. Doris has this optimize of topn node when the above restriction can be matched.
But now this Doris optimize rule can cover few situation ---- I'm not sure whether is it suit for your case.
--
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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "elsa0520 (via GitHub)" <gi...@apache.org>.
elsa0520 commented on issue #28868:
URL: https://github.com/apache/doris/issues/28868#issuecomment-1892055824
The order by operator is a blocking operator which means Doris need to load all of data (include all columns) in memory.
100KB * 150K rows = 15000000 kb => 15G
The error message " process memory used 180.24 GB exceed limit 151.01 GB or sys available memory 38.30 GB less than low water mark 16.00 GB. "
May be your process is running other query which already cost lots of memory.
So could your please only run this query and confirm memory used by this query ?
--
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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "trikker (via GitHub)" <gi...@apache.org>.
trikker commented on issue #28868:
URL: https://github.com/apache/doris/issues/28868#issuecomment-1892106434
> Doris support the lazy materialized in storage layer which means that If there is where predicate can push down to storage layer the big size column could be lazy ready in storage layer. —— Not suitable for your case.
>
> BTW could your please supply your Doris version ?
it's 2.0.3，provided at the beginning of the issue.
--
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] [Bug] query plan can be rewrited to get better performance when reading big text column [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #28868:
URL: https://github.com/apache/doris/issues/28868#issuecomment-3287228449
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