zhangyue1818 opened a new issue, #1453:
URL: https://github.com/apache/cloudberry/issues/1453
### Apache Cloudberry version
_No response_
### What happened
`Redistribute Motion` nodes in tpcds Q95's plan.
```
postgres=# explain with ws_wh as
(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2
from web_sales ws1,web_sales ws2
where ws1.ws_order_number = ws2.ws_order_number
and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
select
count(distinct ws_order_number) as "order count"
,sum(ws_ext_ship_cost) as "total shipping cost"
,sum(ws_net_profit) as "total net profit"
from
web_sales ws1
,date_dim
,customer_address
,web_site
where
d_date between '2002-5-01' and
(cast('2002-5-01' as date) + '60 days'::interval)
and ws1.ws_ship_date_sk = d_date_sk
and ws1.ws_ship_addr_sk = ca_address_sk
and ca_state = 'TN'
and ws1.ws_web_site_sk = web_site_sk
and web_company_name = 'pri'
and ws1.ws_order_number in (select ws_order_number
from ws_wh)
and ws1.ws_order_number in (select wr_order_number
from web_returns,ws_wh
where wr_order_number = ws_wh.ws_order_number)
order by count(distinct ws_order_number)
limit 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6277.23 rows=1
width=24)
Merge Key: (count(DISTINCT ws1_1.ws_order_number))
-> Sort (cost=0.00..6277.23 rows=1 width=24)
Sort Key: (count(DISTINCT ws1_1.ws_order_number))
-> Sequence (cost=0.00..6277.23 rows=1 width=24)
-> Shared Scan (share slice:id 1:0) (cost=0.00..1927.12
rows=8077815 width=1)
-> Hash Join (cost=0.00..1919.04 rows=8077815 width=8)
Hash Cond: (ws1.ws_order_number =
ws2.ws_order_number)
Join Filter: (ws1.ws_warehouse_sk <>
ws2.ws_warehouse_sk)
-> Redistribute Motion 1:1 (slice2; segments:
1) (cost=0.00..548.86 rows=719384 width=12)
Hash Key: ws1.ws_order_number
-> Seq Scan on web_sales ws1
(cost=0.00..505.78 rows=719384 width=12)
-> Hash (cost=548.86..548.86 rows=719384
width=12)
-> Redistribute Motion 1:1 (slice3;
segments: 1) (cost=0.00..548.86 rows=719384 width=12)
Hash Key: ws2.ws_order_number
-> Seq Scan on web_sales ws2
(cost=0.00..505.78 rows=719384 width=12)
-> Redistribute Motion 1:1 (slice4) (cost=0.00..4350.11
rows=1 width=24)
-> Limit (cost=0.00..4350.11 rows=1 width=24)
-> Sort (cost=0.00..4350.11 rows=1 width=24)
Sort Key: (count(DISTINCT
ws1_1.ws_order_number))
-> Finalize Aggregate (cost=0.00..4350.11
rows=1 width=24)
-> Gather Motion 1:1 (slice5;
segments: 1) (cost=0.00..4350.11 rows=1 width=24)
-> Partial Aggregate
(cost=0.00..4350.11 rows=1 width=24)
-> Redistribute Motion
1:1 (slice6; segments: 1) (cost=0.00..4350.11 rows=232 width=20)
Hash Key:
ws1_1.ws_order_number
-> Hash Join
(cost=0.00..4350.09 rows=232 width=20)
Hash Cond:
(ws1_1.ws_web_site_sk = web_site.web_site_sk)
-> Hash Join
(cost=0.00..3918.80 rows=1439 width=24)
Hash
Cond: (ws1_1.ws_ship_addr_sk = customer_address.ca_address_sk)
->
Hash Join (cost=0.00..3479.22 rows=14608 width=28)
Hash Cond: (ws1_1.ws_ship_date_sk = date_dim.d_date_sk)
-> Result (cost=0.00..2950.95 rows=455413 width=32)
Filter: (CASE WHEN ((count(*)) = '-1'::bigint) THEN NULL::bigint ELSE
COALESCE((count(*)), '0'::bigint) END > '0'::bigint)
-> Hash Left Join (cost=0.00..2898.51 rows=719384 width=40)
Hash Cond: (ws1_1.ws_order_number = web_returns.wr_order_number)
-> Seq Scan on web_sales ws1_1 (cost=0.00..505.78 rows=719384
width=32)
-> Hash (cost=2093.00..2093.00 rows=37024 width=16)
-> Broadcast Motion 1:1 (slice7) (cost=0.00..2093.00
rows=37024 width=16)
-> Finalize HashAggregate (cost=0.00..2062.78
rows=37024 width=16)
Group Key: web_returns.wr_order_number
-> Hash Join (cost=0.00..2053.71 rows=71763
width=16)
Hash Cond: (share0_ref2.ws_order_number =
web_returns.wr_order_number)
-> Gather Motion 1:1 (slice8; segments: 1)
(cost=0.00..1577.42 rows=58484 width=16)
-> Streaming Partial HashAggregate
(cost=0.00..1571.08 rows=58484 width=16)
Group Key:
share0_ref2.ws_order_number
-> Shared Scan (share slice:id
8:0) (cost=0.00..586.74 rows=8077815 width=8)
-> Hash (cost=441.33..441.33 rows=71763
width=8)
-> Gather Motion 1:1 (slice9;
segments: 1) (cost=0.00..441.33 rows=71763 width=8)
-> Seq Scan on web_returns
(cost=0.00..436.37 rows=71763 width=8)
-> Hash (cost=438.80..438.80 rows=62 width=4)
-> Broadcast Motion 1:1 (slice10; segments: 1) (cost=0.00..438.80
rows=62 width=4)
-> Seq Scan on date_dim (cost=0.00..438.79 rows=62 width=4)
Filter: ((d_date >= '2002-05-01'::date) AND (d_date <=
'2002-06-30 00:00:00'::timestamp without time zone))
->
Hash (cost=436.39..436.39 rows=1439 width=4)
-> Broadcast Motion 1:1 (slice11; segments: 1) (cost=0.00..436.39 rows=1439
width=4)
-> Seq Scan on customer_address (cost=0.00..436.10 rows=1439 width=4)
Filter: ((ca_state)::text = 'TN'::text)
-> Hash
(cost=431.01..431.01 rows=5 width=4)
->
Broadcast Motion 1:1 (slice12; segments: 1) (cost=0.00..431.01 rows=5 width=4)
-> Seq Scan on web_site (cost=0.00..431.01 rows=5 width=4)
Filter: ((web_company_name)::text = 'pri'::text)
Optimizer: GPORCA
(62 rows)
```
### What you think should happen instead
_No response_
### How to reproduce
- make && make install
- create cluster with 1QD and 1QE
- load data with tpcds 1s
- explain q95
### Operating System
centos7
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes, I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).
--
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]
