You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by "RealGrayRabbit (via GitHub)" <gi...@apache.org> on 2026/01/09 06:00:22 UTC
[I] Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
RealGrayRabbit opened a new issue, #1523:
URL: https://github.com/apache/cloudberry/issues/1523
A performance test was conducted between a Greenplum 6 cluster and a Cloudberry DB cluster with identical hardware and software configurations, the same data structure, data content, and query statements; **the results showed that the Greenplum 6 cluster completed the query in only 33 seconds, while the Cloudberry DB cluster took 151 seconds,** indicating that the performance of the Cloudberry DB cluster is far behind that of the Greenplum 6 cluster under the same test conditions.
The table structure is as below:
```
CREATE TABLE test_orders (
order_id BIGINT,
customer_id BIGINT,
order_date DATE,
order_amount DECIMAL(18,2),
order_status VARCHAR(20),
product_category VARCHAR(50),
region VARCHAR(50),
notes TEXT
) DISTRIBUTED BY (order_id);
CREATE INDEX idx_orders_customer ON test_orders(customer_id);
CREATE INDEX idx_orders_date ON test_orders(order_date);
CREATE INDEX idx_orders_status ON test_orders(order_status);
```
Then insert 500 millions test data into the table. ANALYZE test_orders, execute below query:
```
SELECT
customer_id,
order_date,
order_amount,
SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank,
LAG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount
FROM test_orders
WHERE customer_id IN (
SELECT customer_id
FROM test_orders
GROUP BY customer_id
HAVING COUNT(*) > 100
LIMIT 100
)
ORDER BY customer_id, order_date
LIMIT 1000;
```
Then using cbcopy to copy data from Greenplum 6 to Cloudberry DB. Execute same query. You will find the result.
The version of Cloudberry DB is
PostgreSQL 14.4 (Apache Cloudberry 2.0.0-incubating build dev) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.1 20220127 (Red H
at 11.2.1-9), 64-bit compiled on Dec 23 2025 11:08:00 (with assert checking)
--
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@cloudberry.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "RealGrayRabbit (via GitHub)" <gi...@apache.org>.
RealGrayRabbit commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3838339561
I've rebuild Cloudberry using configuration as below, but the performance is still poor. Could you suggest a full configuration parameters so that I can test again?
`./configure --prefix=/usr/local/cloudberry --disable-external-fts --enable-gpcloud --enable-ic-proxy --enable-mapreduce --enable-orafce --enable-orca -enable-tap-tests --with-gssapi --with-ldap --with-libxml --with-lz4 --with-pam --with-perl --with-pgport=5432 --with-python --with-pythonsrc-ext --with-ssl=openssl --with-uuid=e2fs --with-includes=/usr/local/xerces-c/include --with-libraries=/usr/local/cloudberry/lib CFLAGS="-O3 -march=native" CXXFLAGS="-O3 -march=native"`
P.S. I'm using https://github.com/apache/cloudberry/archive/refs/tags/2.0.0-incubating.tar.gz for this test.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "tuhaihe (via GitHub)" <gi...@apache.org>.
tuhaihe commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3846186542
> After change configuration of statement_mem, we got a better performance.
>
> <img alt="Image" width="407" height="52" src="https://private-user-images.githubusercontent.com/185448221/544730685-84e6618d-e78b-494b-beb7-06074ddb6295.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NzAxOTU5ODYsIm5iZiI6MTc3MDE5NTY4NiwicGF0aCI6Ii8xODU0NDgyMjEvNTQ0NzMwNjg1LTg0ZTY2MThkLWU3OGItNDk0Yi1iZWI3LTA2MDc0ZGRiNjI5NS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjYwMjA0JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI2MDIwNFQwOTAxMjZaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT0xNDgxODEwYzA3ZjI3ZjdmZDczMWI4NjZjNzQ4ZDU0NGYxNjc2MzM0MTFmMDJkMmIyNDE5YmI0MTMzZWUyOTc0JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.c74eZEOPPSIgW2u1GXk-Hhlvmp1tCrAD5xUUFSGZQnE">
Good result! Thanks for the updates!
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3855986797
sloved.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "RealGrayRabbit (via GitHub)" <gi...@apache.org>.
RealGrayRabbit commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3727589801
> Thanks for the reporting. It is very helpful if you could paste the plan compared with that in GP6, which is related to data size.
Greenplum 6 query plan:
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..22282.86 rows=84 width=44)
-> Gather Motion 12:1 (slice5; segments: 12) (cost=0.00..22282.82 rows=1000 width=44)
Merge Key: test_orders.customer_id, test_orders.order_date
-> Limit (cost=0.00..22282.71 rows=84 width=44)
-> Sort (cost=0.00..22282.70 rows=1666868 width=44)
Sort Key: test_orders.customer_id, test_orders.order_date
-> WindowAgg (cost=0.00..13687.62 rows=1666868 width=44)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..13627.61 rows=1666868 width=36)
Sort Key: test_orders.customer_id, test_orders.order_date
-> WindowAgg (cost=0.00..6595.27 rows=1666868 width=36)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..6565.26 rows=1666868 width=18)
Sort Key: test_orders.customer_id, test_orders.order_date
-> Redistribute Motion 12:12 (slice4; segments: 12) (cost=0.00..3049.09 rows=1666868 width=18)
Hash Key: test_orders.customer_id
-> Hash Semi Join (cost=0.00..2955.18 rows=1666868 width=18)
Hash Cond: (test_orders.customer_id = test_orders_1.customer_id)
-> Seq Scan on test_orders (cost=0.00..676.24 rows=4167169 width=18)
-> Hash (cost=1264.48..1264.48 rows=4 width=8)
-> Broadcast Motion 1:12 (slice3) (cost=0.00..1264.48 rows=48 width=8)
-> Limit (cost=0.00..1264.48 rows=1 width=8)
-> Gather Motion 12:1 (slice2; segments: 12) (cost=0.00..1264.48 rows=4 width=8)
-> Result (cost=0.00..1264.48 rows=1 width=8)
Filter: ((count((count()))) > 100)
-> GroupAggregate (cost=0.00..1264.48 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Sort (cost=0.00..1264.48 rows=1 width=16)
Sort Key: test_orders_1.customer_id
-> Redistribute Motion 12:12 (slice1; segments: 12) (cost=0.00..1264.48 rows=1 width=16)
Hash Key: test_orders_1.customer_id
-> Result (cost=0.00..1264.48 rows=1 width=16)
-> HashAggregate (cost=0.00..1264.48 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Seq Scan on test_orders test_orders_1 (cost=0.00..676.24 rows=4167169 width=8)
Optimizer: Pivotal Optimizer (GPORCA)
(38 rows)
```
Query Plan in Cloudberry DB:
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..22275.20 rows=1000 width=44)
-> Gather Motion 12:1 (slice1; segments: 12) (cost=0.00..22275.16 rows=1000 width=44)
Merge Key: test_orders.customer_id, test_orders.order_date
-> Limit (cost=0.00..22275.04 rows=84 width=44)
-> Sort (cost=0.00..22275.04 rows=1666334 width=44)
Sort Key: test_orders.customer_id, test_orders.order_date
-> WindowAgg (cost=0.00..13682.90 rows=1666334 width=44)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..13622.92 rows=1666334 width=36)
Sort Key: test_orders.customer_id, test_orders.order_date DESC
-> WindowAgg (cost=0.00..6592.98 rows=1666334 width=36)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..6562.99 rows=1666334 width=18)
Sort Key: test_orders.customer_id, test_orders.order_date
-> Redistribute Motion 12:12 (slice2; segments: 12) (cost=0.00..3048.02 rows=1666334 width=18)
Hash Key: test_orders.customer_id
-> Hash Semi Join (cost=0.00..2954.14 rows=1666334 width=18)
Hash Cond: (test_orders.customer_id = test_orders_1.customer_id)
-> Seq Scan on test_orders (cost=0.00..685.32 rows=4165834 width=18)
-> Hash (cost=1254.68..1254.68 rows=4 width=8)
-> Broadcast Motion 1:12 (slice3) (cost=0.00..1254.68 rows=4 width=8)
-> Limit (cost=0.00..1254.68 rows=4 width=8)
-> Gather Motion 12:1 (slice4; segments: 12) (cost=0.00..1254.68 rows=4 width=8)
-> Result (cost=0.00..1254.68 rows=1 width=8)
Filter: ((count(*)) > 100)
-> Finalize HashAggregate (cost=0.00..1254.68 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Redistribute Motion 12:12 (slice5; segments: 12) (cost=0.00..1254.68 rows=1 width=16)
Hash Key: test_orders_1.customer_id
-> Streaming Partial HashAggregate (cost=0.00..1254.68 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Seq Scan on test_orders test_orders_1 (cost=0.00..685.32 rows=4165834 width=8)
Optimizer: GPORCA
(35 rows)
```
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "RealGrayRabbit (via GitHub)" <gi...@apache.org>.
RealGrayRabbit commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3844995109
I download https://github.com/apache/cloudberry/tree/REL_2_STABLE source code and build it with same configuration. Then check the performance with same database. Now it cost 165795ms. Still no improvement.
<img width="1001" height="195" alt="Image" src="https://github.com/user-attachments/assets/2c93f56d-12f1-4a80-af68-a12278793968" />
<img width="1024" height="172" alt="Image" src="https://github.com/user-attachments/assets/6c0af25e-24d4-4958-a5a5-d0e2ace7c160" />
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz closed issue #1523: Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6
URL: https://github.com/apache/cloudberry/issues/1523
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3760883022
# Root Cause Identified
## Summary
I analyzed your execution plans and found **two critical issues** explaining the 4.5x performance gap:
1. **WindowAgg operations are 8x slower in CBDB** (131s vs 16s)
2. **CBDB is using a DEBUG build** which causes 3-4x slowdown
---
## 1. WindowAgg Execution Time Comparison
From the EXPLAIN ANALYZE output:
| System | WindowAgg #1 | WindowAgg #2 | Total WindowAgg | Overall Query |
|--------|--------------|--------------|-----------------|---------------|
| **CBDB** | 46.6s | 84.5s | **131s** | 151s |
| **GP6** | 5.2s | 11.2s | **16.4s** | 33s |
| **Gap** | 9x | 7.5x | **8x** | 4.6x |
**WindowAgg accounts for 87% of CBDB's execution time** (131/151 seconds), making it the primary bottleneck.
---
## 2. Debug Compilation Issue 🚨
Your CBDB version string shows:
```
PostgreSQL 14.4 (Apache Cloudberry 2.0.0-incubating build dev)
compiled on Dec 23 2025 11:08:00 (with assert checking)
^^^^^^^^^^^^^^^^^^
DEBUG BUILD!
you can psql, then
SHOW debug_assertions;
```
**`with assert checking` = DEBUG compilation**, which causes:
- 3-4x performance loss for compute-intensive operations like WindowAgg
- All Assert() statements are executed (adding massive overhead)
- Compiler optimizations disabled (-O0 instead of -O3)
**This likely explains 75% of your performance gap.**
---
## Solution
### Rebuild CBDB as a Release version:
```bash
-- check option before
pg_config --configure
./configure \
--prefix=/usr/local/cloudberry \
CFLAGS="-O3 -march=native" \
CXXFLAGS="-O3 -march=native"
# DO NOT add --enable-cassert
make -j$(nproc)
make install
```
### Verify the fix:
```sql
-- After rebuild, check:
SHOW debug_assertions; -- Should return 'off'
SELECT version(); -- Should NOT show 'with assert checking'
```
**Expected result:** Query time should drop from **151s to ~40-50s**, bringing it close to GP6's 33s.
---
## Why This Matters
Debug builds should **NEVER** be used for:
- ❌ Performance testing
- ❌ Benchmarking
- ❌ Production environments
They are only for development and debugging.
Your current test is comparing:
```
CBDB (Debug, -O0) vs GP6 (likely Release, -O3)
```
Please retest with a release build for a fair comparison. The remaining 1.2-1.5x difference (if any) can then be investigated further.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3874992940
Adjust statement_mem from default 125MB to 2GB , got better performance then gpdb6.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3742947793
Thank you for providing the plan!
Based on the query plan, the difference between the two mainly lies in the Aggregation part below, which may be due to cost estimation issues. Could you please help provide two more pieces of information:
The results of "explain analyze", which can be used to analyze which operator is further slow
Turn on the following switch, and place the output as attached file for further diagnosis:
SET client_min_messages=log;
SET optimizer_print_memo_after_optimization=on;
SET optimizer_print_optimization_context=on;
SET optimizer_print_memo_after_exploration=on;
SET optimizer_print_memo_after_implementation=on;
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3727336657
Thanks for the reporting. It is very helpful if you could paste the plan compared with that in GP6, which is related to data size.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3845045381
Please email me your wechat.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "RealGrayRabbit (via GitHub)" <gi...@apache.org>.
RealGrayRabbit commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3844590308
I've followed above description to rebuild cbdb.
**Step 1,** as your mentioned
- make clean , make distclean;
- ./configure --prefix=/usr/local/cloudberry-db --enable-orca --with-gssapi --with-ldap --with-libxml --with-lz4 --with-openssl --with-pam --with-perl --with-python --with-pythonsrc-ext --with-ssl=openssl --with-zlib --with-uuid=e2fs --with-includes=/usr/local/xerces-c/include --with-libraries=/usr/local/cloudberry-db/lib
and then ,select version(), no any with assert checking.
```
select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.4 (Apache Cloudberry 2.0.0-incubating build dev) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.1 20220127 (Red Hat 11.2.1-9), 64-bit compiled on Jan 30 2026 17:08:44
(1 row)
```
Then I check the performance with same data as previous. But it still no improvement.
Step 2
I rebuild 3 host as a cluster with same configuration. Then follow the step. Still no improvement.
What's the problem? Maybe you can contact me with wechat and connect remotely to check this cluster.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "RealGrayRabbit (via GitHub)" <gi...@apache.org>.
RealGrayRabbit commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3758851044
> Thank you for providing the plan! Based on the query plan, the difference between the two mainly lies in the Aggregation part below, which may be due to cost estimation issues. Could you please help provide two more pieces of information:
>
> ```
> 1. The results of "explain analyze", which can be used to analyze which operator is further slow
> 2. Turn on the following GUCs, and place the output as attached file of both GP6 and Cloudberry for further diagnosis:
>
> SET client_min_messages=log;
> SET optimizer_print_memo_after_optimization=on;
> SET optimizer_print_optimization_context=on;
> SET optimizer_print_memo_after_exploration=on;
> SET optimizer_print_memo_after_implementation=on;
> ```
I've already run with different configuration under GP6 and CBDB 2 cluster. Please refer to attached log file.
[cbdb_on.log](https://github.com/user-attachments/files/24666902/cbdb_on.log)
[gp6_on.log](https://github.com/user-attachments/files/24666901/gp6_on.log)
[cbdb_off.log](https://github.com/user-attachments/files/24666903/cbdb_off.log)
[gp6_off.log](https://github.com/user-attachments/files/24666900/gp6_off.log)
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3838383139
first make clean , make distclean;
```sql
./configure --prefix=/usr/local/cloudberry-db \
--enable-orca \
--with-gssapi \
--with-ldap \
--with-libxml \
--with-lz4 \
--with-openssl \
--with-zlib \
--with-pam \
--with-perl \
--with-python \
--with-pythonsrc-ext \
--with-ssl=openssl \
--with-openssl \
--with-uuid=e2fs \
--with-includes=/usr/local/xerces-c/include:/usr/local/include \
--with-libraries=/usr/local/cloudberry-db/lib:/usr/local/lib
```
then , select version(), no any `with assert checking`.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "RealGrayRabbit (via GitHub)" <gi...@apache.org>.
RealGrayRabbit commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3845266004
After change configuration of statement_mem, we got a better performance.
<img width="407" height="52" alt="Image" src="https://github.com/user-attachments/assets/84e6618d-e78b-494b-beb7-06074ddb6295" />
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3844628678
please use `https://github.com/apache/cloudberry/tree/REL_2_STABLE` branch to test.
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "RealGrayRabbit (via GitHub)" <gi...@apache.org>.
RealGrayRabbit commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3727456920
Greenplum 6 query plan:
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..22282.86 rows=84 width=44)
-> Gather Motion 12:1 (slice5; segments: 12) (cost=0.00..22282.82 rows=1000 width=44)
Merge Key: test_orders.customer_id, test_orders.order_date
-> Limit (cost=0.00..22282.71 rows=84 width=44)
-> Sort (cost=0.00..22282.70 rows=1666868 width=44)
Sort Key: test_orders.customer_id, test_orders.order_date
-> WindowAgg (cost=0.00..13687.62 rows=1666868 width=44)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..13627.61 rows=1666868 width=36)
Sort Key: test_orders.customer_id, test_orders.order_date
-> WindowAgg (cost=0.00..6595.27 rows=1666868 width=36)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..6565.26 rows=1666868 width=18)
Sort Key: test_orders.customer_id, test_orders.order_date
-> Redistribute Motion 12:12 (slice4; segments: 12) (cost=0.00..3049.09 rows=1666868 width=18)
Hash Key: test_orders.customer_id
-> Hash Semi Join (cost=0.00..2955.18 rows=1666868 width=18)
Hash Cond: (test_orders.customer_id = test_orders_1.customer_id)
-> Seq Scan on test_orders (cost=0.00..676.24 rows=4167169 width=18)
-> Hash (cost=1264.48..1264.48 rows=4 width=8)
-> Broadcast Motion 1:12 (slice3) (cost=0.00..1264.48 rows=48 width=8)
-> Limit (cost=0.00..1264.48 rows=1 width=8)
-> Gather Motion 12:1 (slice2; segments: 12) (cost=0.00..1264.48 rows=4 width=8)
-> Result (cost=0.00..1264.48 rows=1 width=8)
Filter: ((count((count()))) > 100)
-> GroupAggregate (cost=0.00..1264.48 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Sort (cost=0.00..1264.48 rows=1 width=16)
Sort Key: test_orders_1.customer_id
-> Redistribute Motion 12:12 (slice1; segments: 12) (cost=0.00..1264.48 rows=1 width=16)
Hash Key: test_orders_1.customer_id
-> Result (cost=0.00..1264.48 rows=1 width=16)
-> HashAggregate (cost=0.00..1264.48 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Seq Scan on test_orders test_orders_1 (cost=0.00..676.24 rows=4167169 width=8)
Optimizer: Pivotal Optimizer (GPORCA)
(38 rows)
```
Query Plan in Cloudberry DB:
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..22275.20 rows=1000 width=44)
-> Gather Motion 12:1 (slice1; segments: 12) (cost=0.00..22275.16 rows=1000 width=44)
Merge Key: test_orders.customer_id, test_orders.order_date
-> Limit (cost=0.00..22275.04 rows=84 width=44)
-> Sort (cost=0.00..22275.04 rows=1666334 width=44)
Sort Key: test_orders.customer_id, test_orders.order_date
-> WindowAgg (cost=0.00..13682.90 rows=1666334 width=44)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..13622.92 rows=1666334 width=36)
Sort Key: test_orders.customer_id, test_orders.order_date DESC
-> WindowAgg (cost=0.00..6592.98 rows=1666334 width=36)
Partition By: test_orders.customer_id
Order By: test_orders.order_date
-> Sort (cost=0.00..6562.99 rows=1666334 width=18)
Sort Key: test_orders.customer_id, test_orders.order_date
-> Redistribute Motion 12:12 (slice2; segments: 12) (cost=0.00..3048.02 rows=1666334 width=18)
Hash Key: test_orders.customer_id
-> Hash Semi Join (cost=0.00..2954.14 rows=1666334 width=18)
Hash Cond: (test_orders.customer_id = test_orders_1.customer_id)
-> Seq Scan on test_orders (cost=0.00..685.32 rows=4165834 width=18)
-> Hash (cost=1254.68..1254.68 rows=4 width=8)
-> Broadcast Motion 1:12 (slice3) (cost=0.00..1254.68 rows=4 width=8)
-> Limit (cost=0.00..1254.68 rows=4 width=8)
-> Gather Motion 12:1 (slice4; segments: 12) (cost=0.00..1254.68 rows=4 width=8)
-> Result (cost=0.00..1254.68 rows=1 width=8)
Filter: ((count(*)) > 100)
-> Finalize HashAggregate (cost=0.00..1254.68 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Redistribute Motion 12:12 (slice5; segments: 12) (cost=0.00..1254.68 rows=1 width=16)
Hash Key: test_orders_1.customer_id
-> Streaming Partial HashAggregate (cost=0.00..1254.68 rows=1 width=16)
Group Key: test_orders_1.customer_id
-> Seq Scan on test_orders test_orders_1 (cost=0.00..685.32 rows=4165834 width=8)
Optimizer: GPORCA
(35 rows)
```
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Cloudberry DB exhibits poor performance when executing aggregate queries involving window functions compared with Greenplum 6 [cloudberry]
Posted by "tuhaihe (via GitHub)" <gi...@apache.org>.
tuhaihe commented on issue #1523:
URL: https://github.com/apache/cloudberry/issues/1523#issuecomment-3794515313
Hi @RealGrayRabbit, could you please rebuild the Cloudberry following the @yjhjstz suggestion and rerun the performance test? Thanks!
--
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@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org