[Bug] Redis motion in 1QD/1QE #1453
Description
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
- I agree to follow this project's Code of Conduct.