Cloudberry DB exhibits extremely poor performance when executing aggregate queries involving window functions compared with Greenplum 6 #1523
Description
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)