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: [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]
