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)
Apache Cloudberry version
No response
What happened
Redistribute Motionnodes in tpcds Q95's plan.What you think should happen instead
No response
How to reproduce
Operating System
centos7
Anything else
No response
Are you willing to submit PR?
Code of Conduct