gongxun0928 opened a new pull request, #1178:
URL: https://github.com/apache/cloudberry/pull/1178
During the TPC-DS tests, we observed an issue where CloudberryDB (CBDB)
performed worse than Greenplum (GPDB) when the query plan generated a
multi-stage aggregation (e.g., TPC-DS query 04). The phenomenon showed that the
deduplication effect of CBDB’s **Streaming Partial HashAggregate** was
significantly worse. As a result, the **Finalize HashAggregate** operator in
CBDB processed significantly more data compared to GPDB under the same dataset.
Example plan from CBDB:
Gather Motion 32:1 (slice1; segments: 32) (cost=0.00..19988.81
rows=1800000 width=76)
-> Finalize HashAggregate (cost=0.00..19663.99 rows=56250 width=81)
Group Key: customer_gp.c_customer_id, customer_gp.c_first_name,
customer_gp.c_last_name, customer_gp.c_preferred_cust_flag,
customer_gp.c_birth_country, customer_gp.c_login,
customer_gp.c_email_address, date_dim_gp.d_year
-> Redistribute Motion 32:32 (slice2; segments: 32)
(cost=0.00..19603.35 rows=56250 width=81)
Hash Key: customer_gp.c_customer_id,
customer_gp.c_first_name, customer_gp.c_last_name,
customer_gp.c_preferred_cust_flag,
customer_gp.c_birth_country, customer_gp.c_login,
customer_gp.c_email_address, date_dim_gp.d_year
-> Streaming Partial HashAggregate (cost=0.00..19589.09
rows=56250 width=81)
Group Key: customer_gp.c_customer_id,
customer_gp.c_first_name, customer_gp.c_last_name,
customer_gp.c_preferred_cust_flag,
customer_gp.c_birth_country, customer_gp.c_login,
customer_gp.c_email_address, date_dim_gp.d_year
-> Hash Join (cost=0.00..12346.24 rows=6935137
width=95)
Hash Cond: (store_sales_gp.ss_customer_sk =
customer_gp.c_customer_sk)
...
...
Upon further investigation, we found that the **`NumericAggState`**
structure in CloudberryDB contained two additional fields compared to Greenplum:
```c
int64 pInfcount; /* count of +Inf values */
int64 nInfcount; /* count of -Inf values */
These fields were introduced in PostgreSQL 14 to support +/- Inf values for
numeric types. Consequently, the size of the NumericAggState structure
increased from 128 bytes to 144 bytes.
In the Streaming Partial HashAggregate, a NumericAggState structure is
created for each grouping key to track statistics for numeric types.
This results in CBDB allocating 16 more bytes per grouping key compared
to GPDB. This additional memory allocation contributes to the observed
performance difference.
To address this issue, we need to adjust the aggstate->hash_mem_limit
Inspired by PostgreSQL’s handling of hash_mem_limit, we introduce a
scaling factor called hash_mem_multiplier. Following the changes made
in PostgreSQL 15, we should set the default value of hash_mem_multiplier
to 2.0 to ensure better memory utilization during hash-based aggregation
operations. In actual TPC-DS tests, we found that under most of the same
statement_mem conditions, when hash_mem_multiplier=1.5 is set, the
performance of greenplum and cloudberrydb is similar.
https://www.postgresql.org/message-id/flat/CAH2-Wzndc_ROk6CY-bC6p9O53q974Y0Ey4WX8jcPbuTZYM4Q3A%40mail.gmail.com
<!-- Thank you for your contribution to Apache Cloudberry (Incubating)! -->
Fixes #ISSUE_Number
### What does this PR do?
<!-- Brief overview of the changes, including any major features or fixes -->
### Type of Change
- [ ] Bug fix (non-breaking change)
- [ ] New feature (non-breaking change)
- [ ] Breaking change (fix or feature with breaking changes)
- [ ] Documentation update
### Breaking Changes
<!-- Remove if not applicable. If yes, explain impact and migration path -->
### Test Plan
<!-- How did you test these changes? -->
- [ ] Unit tests added/updated
- [ ] Integration tests added/updated
- [ ] Passed `make installcheck`
- [ ] Passed `make -C src/test installcheck-cbdb-parallel`
### Impact
<!-- Remove sections that don't apply -->
**Performance:**
<!-- Any performance implications? -->
**User-facing changes:**
<!-- Any changes visible to users? -->
**Dependencies:**
<!-- New dependencies or version changes? -->
### Checklist
- [ ] Followed [contribution
guide](https://cloudberry.apache.org/contribute/code)
- [ ] Added/updated documentation
- [ ] Reviewed code for security implications
- [ ] Requested review from [cloudberry
committers](https://github.com/orgs/apache/teams/cloudberry-committers)
### Additional Context
<!-- Any other information that would help reviewers? Remove if none -->
### CI Skip Instructions
<!--
To skip CI builds, add the appropriate CI skip identifier to your PR title.
The identifier must:
- Be in square brackets []
- Include the word "ci" and either "skip" or "no"
- Only use for documentation-only changes or when absolutely necessary
-->
---
<!-- Join our community:
- Mailing list:
[[email protected]](https://lists.apache.org/[email protected])
(subscribe: [email protected])
- Discussions: https://github.com/apache/cloudberry/discussions -->
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
