You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2022/04/25 03:57:24 UTC
[GitHub] [incubator-doris] liaoxin01 opened a new issue, #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
liaoxin01 opened a new issue, #9208:
URL: https://github.com/apache/incubator-doris/issues/9208
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues.
### Description
At present, the decimal type has some problems.
1. The unified use of decimal128 in the vectorization execution layer brings performance and memory overhead. For low precision, decimal32 or decimal64 can be used.
2. Insufficient precision support for decimal. For decimal(p, s), 1 < p < 27 and 0 < s < 9. In some scenarios, we need higher precision and scale.
### Solution
There are several stages to optimize the performance and precision of decimal.
**Step1:** Fe passed precision and scale of decimal to be, so the vectorization execution layer can use this information.
**Step2:** The vectorization execution layer uses decimal32, decimal64 or decimal128 to calculate according to the precision and scale information from fe.
**Step3:** Add a new decimal type to the storage layer. Decimal of low precision uses decimal 32 (int32_t) or decimal 64 (int64_t) for storage. Decimal of high precision uses decimal128 (_int128) for storage. It can support the maximum precision of 38. Scale only needs to be less than precision.
### Are you willing to submit PR?
- [X] 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
[GitHub] [incubator-doris] HappenLee commented on issue #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
Posted by GitBox <gi...@apache.org>.
HappenLee commented on issue #9208:
URL: https://github.com/apache/incubator-doris/issues/9208#issuecomment-1148816058
Please compare the occupancy of the storage, before using the new decimal type of comparison
@liaoxin01
--
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
[GitHub] [doris] liaoxin01 closed issue #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
Posted by "liaoxin01 (via GitHub)" <gi...@apache.org>.
liaoxin01 closed issue #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
URL: https://github.com/apache/doris/issues/9208
--
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
[GitHub] [incubator-doris] liaoxin01 commented on issue #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
Posted by GitBox <gi...@apache.org>.
liaoxin01 commented on issue #9208:
URL: https://github.com/apache/incubator-doris/issues/9208#issuecomment-1108048896
[[feature-wip](decimalv3) fe passes precision and scale of decimal to be for ArimeticExpr #9199](https://github.com/apache/incubator-doris/pull/9199)
--
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
[GitHub] [incubator-doris] liaoxin01 commented on issue #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
Posted by GitBox <gi...@apache.org>.
liaoxin01 commented on issue #9208:
URL: https://github.com/apache/incubator-doris/issues/9208#issuecomment-1120543707
**Performance test:**
The storage layer data is still decimalv2, which is calculated using decimalv3.
Create a table and import 100 million pieces of data.
set enable_storage_vectorization = true.
```
CREATE TABLE IF NOT EXISTS decimal_data (
c_bigint bigint NOT NULL,
c_short_decimal decimal(5,2) NOT NULL,
c_mediam_decimal decimal(17,8) NOT NULL,
c_long_decimal decimal(27,9) NOT NULL
)
DUPLICATE KEY(c_bigint)
DISTRIBUTED BY HASH(c_bigint) BUCKETS 1
PROPERTIES (
"replication_num" = "1"
)
```
```
SELECT max(c_short_decimal + c_short_decimal) FROM decimal_data
doris decimalv2 : 7.993 sec
doris vectorized decimalv2 : 2.534 sec
doris vectorized decimalv3 : 1.972 sec
SELECT max(c_mediam_decimal + c_mediam_decimal) FROM decimal_data
doris decimalv2 : 7.616 sec
doris vectorized decimalv2 : 2.287 sec
doris vectorized decimalv3 : 1.788 sec
SELECT sum(c_long_decimal + c_long_decimal) FROM decimal_data
doris decimalv2 : 7.590 sec
doris vectorized decimalv2 : 2.119 sec
doris vectorized decimalv3 : 2.096 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: 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
[GitHub] [incubator-doris] liaoxin01 commented on issue #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
Posted by GitBox <gi...@apache.org>.
liaoxin01 commented on issue #9208:
URL: https://github.com/apache/incubator-doris/issues/9208#issuecomment-1118246943
> We encountered a problem of precision cast in our scenario, for example: _**sql**_
>
> ```
> select cast(1.233333 as decimal(5,2));
> ```
>
> _**result**_
>
> ```
> 1.233333
> ```
>
> _**expect result**_
>
> ```
> 1.23
> ```
>
> It's better to implement that as well :)
Good suggestion. I'll consider implementing 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: 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
[GitHub] [incubator-doris] levy5307 commented on issue #9208: [Enhancement] (decimalv3) optimize decimal performance and precision
Posted by GitBox <gi...@apache.org>.
levy5307 commented on issue #9208:
URL: https://github.com/apache/incubator-doris/issues/9208#issuecomment-1118114601
We encountered a problem of precision cast in our scenario, for example:
***sql***
```
select cast(1.233333 as decimal(5,2));
```
***result***
```
1.233333
```
***expect result***
```
1.23
```
It's better to implement that as well :)
--
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