fanfuxiaoran opened a new issue, #840:
URL: https://github.com/apache/cloudberry/issues/840
### Apache Cloudberry version
_No response_
### What happened
TPCDS 39 SQL
```
explain analyze with inv as
(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
,stdev,mean, case mean when 0 then null else stdev/mean end cov
from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
,stddev_samp(inv_quantity_on_hand)
stdev,avg(inv_quantity_on_hand) mean
from inventory
,item
,warehouse
,date_dim
where inv_item_sk = i_item_sk
and inv_warehouse_sk = w_warehouse_sk
and inv_date_sk = d_date_sk
and d_year =1999
group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
where case mean when 0 then 0 else stdev/mean end > 1)
select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
from inv inv1,inv inv2
where inv1.i_item_sk = inv2.i_item_sk
and inv1.w_warehouse_sk = inv2.w_warehouse_sk
and inv1.d_moy=4
and inv2.d_moy=4+1
order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
,inv2.d_moy,inv2.mean, inv2.cov;
```
the result of `EXPLAIN ANALYZE`
```
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 4:1 (slice1; segments: 4) (cost=0.00..10611.03 rows=1
width=56) (actual time=271918.249..271920.951 rows=1642 loops=1)
Merge Key: share0_ref3.w_warehouse_sk, share0_ref3.i_item_sk,
share0_ref3.d_moy, share0_ref3.mean, share0_ref3.cov, share0_ref2.d_moy,
share0_ref2.mean, share0_ref2.cov
-> Sort (cost=0.00..10611.03 rows=1 width=56) (actual
time=271908.661..271908.745 rows=438 loops=1)
Sort Key: share0_ref3.w_warehouse_sk, share0_ref3.i_item_sk,
share0_ref3.d_moy, share0_ref3.mean, share0_ref3.cov, share0_ref2.d_moy,
share0_ref2.mean, share0_ref2.cov
Sort Method: quicksort Memory: 342kB
-> Sequence (cost=0.00..10611.03 rows=1 width=56) (actual
time=271899.415..271908.168 rows=438 loops=1)
-> Shared Scan (share slice:id 1:0) (cost=0.00..9749.03
rows=1 width=1) (actual time=271396.666..271399.381 rows=15195 loops=1)
-> Result (cost=0.00..9749.03 rows=1 width=36)
(actual time=270462.927..271390.897 rows=15195 loops=1)
Filter: (CASE
(avg(inventory.inv_quantity_on_hand)) WHEN '0'::numeric THEN '0'::numeric ELSE
((stddev_samp(inventory.inv_quantity_on_hand)) /
(avg(inventory.inv_quantity_on_hand))) END > '1'::numeric)
-> GroupAggregate (cost=0.00..9749.03 rows=1
width=36) (actual time=270702.186..271702.301 rows=255457 loops=1)
Group Key: warehouse.w_warehouse_name,
warehouse.w_warehouse_sk, item.i_item_sk, date_dim.d_moy
-> Sort (cost=0.00..9749.03 rows=1
width=24) (actual time=270702.115..270882.301 rows=1149680 loops=1)
Sort Key: warehouse.w_warehouse_name,
warehouse.w_warehouse_sk, item.i_item_sk, date_dim.d_moy
Sort Method: quicksort Memory:
576720kB
-> Redistribute Motion 4:4 (slice2;
segments: 4) (cost=0.00..9749.03 rows=1 width=24) (actual
time=266582.489..268482.509 rows=1149680 loops=1)
Hash Key:
warehouse.w_warehouse_name, warehouse.w_warehouse_sk, item.i_item_sk,
date_dim.d_moy
-> Hash Join
(cost=0.00..9749.03 rows=1 width=24) (actual time=266579.279..267906.337
rows=1155600 loops=1)
Hash Cond:
(item.i_item_sk = inventory.inv_item_sk)
Extra Text: (seg3) Hash
chain length 92.2 avg, 360 max, using 49791 of 1048576 buckets.
-> Seq Scan on item
(cost=0.00..435.28 rows=25500 width=4) (actual time=13.391..63.298 rows=25692
loops=1)
-> Hash
(cost=9309.07..9309.07 rows=1 width=24) (actual time=266564.791..266564.795
rows=4590000 loops=1)
Buckets: 1048576
Batches: 1 Memory Usage: 299550kB
-> Broadcast
Motion 4:4 (slice3; segments: 4) (cost=0.00..9309.07 rows=1 width=24) (actual
time=219859.695..264574.467 rows=4590000 loops=1)
-> Hash Join
(cost=0.00..9309.07 rows=1 width=24) (actual time=224132.763..262579.814
rows=1530000 loops=1)
Hash
Cond: (date_dim.d_date_sk = inventory.inv_date_sk)
Extra
Text: (seg0) Initial batch 0:
-> Seq
Scan on date_dim (cost=0.00..433.71 rows=27 width=8) (actual
time=20.864..32.408 rows=17 loops=1)
Filter: ((d_year = 1999) AND ((d_moy = 4) OR (d_moy = 5)))
->
Hash (cost=8875.36..8875.36 rows=1 width=24) (actual
time=219839.658..219839.662 rows=133110000 loops=1)
Buckets: 2097152 (originally 1048576) Batches: 32 (originally 1) Memory
Usage: 507780kB
-> Broadcast Motion 4:4 (slice4; segments: 4) (cost=0.00..8875.36 rows=1
width=24)
.179315.333 rows=133110000 loops=1)
-> Hash Join (cost=0.00..8875.36 rows=1 width=24) (actual
time=40.545..164455.
ops=1)
Hash Cond: (inventory.inv_warehouse_sk = warehouse.w_warehouse_sk)
Extra Text: (seg3) Hash chain length 1.0 avg, 1 max, using 10 of
2097152 buckets.
-> Seq Scan on inventory (cost=0.00..1346.13 rows=33277500
width=16) (ac
4895.814 rows=33282571 loops=1)
-> Hash (cost=431.00..431.00 rows=1 width=12) (actual
time=0.057..0.061 rows=10 loops=1)
Buckets: 2097152 Batches: 1 Memory Usage: 16385kB
-> Broadcast Motion 4:4 (slice5; segments: 4)
(cost=0.00..431.00
tual time=0.020..0.029 rows=10 loops=1)
-> Seq Scan on warehouse (cost=0.00..431.00 rows=1
width=12)
.6.642 rows=4 loops=1)
-> Hash Join (cost=0.00..862.00 rows=1 width=56) (actual
time=12.980..21.686 rows=438 loops=1)
Hash Cond: ((share0_ref3.i_item_sk =
share0_ref2.i_item_sk) AND (share0_ref3.w_warehouse_sk =
share0_ref2.w_warehouse_sk))
Extra Text: (seg0) Hash chain length 1.0 avg, 2 max,
using 8836 of 1048576 buckets.
-> Redistribute Motion 4:4 (slice6; segments: 4)
(cost=0.00..431.00 rows=1 width=28) (actual time=0.015..3.750 rows=6197 loops=1)
Hash Key: share0_ref3.i_item_sk,
share0_ref3.w_warehouse_sk
-> Result (cost=0.00..431.00 rows=1 width=28)
(actual time=271401.081..271407.101 rows=6253 loops=1)
Filter: (share0_ref3.d_moy = 4)
-> Shared Scan (share slice:id 6:0)
(cost=0.00..431.00 rows=1 width=28) (actual time=271401.067..271404.665
rows=15195 loops=1)
-> Hash (cost=431.00..431.00 rows=1 width=28) (actual
time=498.891..498.894 rows=8956 loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 8756kB
-> Redistribute Motion 4:4 (slice7; segments:
4) (cost=0.00..431.00 rows=1 width=28) (actual time=0.035..495.240 rows=8956
loops=1)
Hash Key: share0_ref2.i_item_sk,
share0_ref2.w_warehouse_sk
-> Result (cost=0.00..431.00 rows=1
width=28) (actual time=271381.034..271387.405 rows=8993 loops=1)
Filter: (share0_ref2.d_moy = 5)
-> Shared Scan (share slice:id 7:0)
(cost=0.00..431.00 rows=1 width=28) (actual time=271395.678..271398.164
rows=15195 loops=1)
Planning Time: 230.080 ms
(slice0) Executor memory: 741K bytes.
(slice1) Executor memory: 126218K bytes avg x 4x(0) workers, 126360K
bytes max (seg3). Work_mem: 68275K bytes max.
(slice2) Executor memory: 309091K bytes avg x 4x(0) workers, 309091K
bytes max (seg0). Work_mem: 299550K bytes max.
* (slice3) Executor memory: 554838K bytes avg x 4x(0) workers, 554893K
bytes max (seg3). Work_mem: 507780K bytes max, 8257772K bytes wanted.
(slice4) Executor memory: 16467K bytes avg x 4x(0) workers, 16467K
bytes max (seg0). Work_mem: 16385K bytes max.
(slice5) Executor memory: 27K bytes avg x 4x(0) workers, 27K bytes max
(seg0).
(slice6) Executor memory: 80K bytes avg x 4x(0) workers, 80K bytes max
(seg0).
(slice7) Executor memory: 80K bytes avg x 4x(0) workers, 80K bytes max
(seg0).
Memory used: 2097152kB
Memory wanted: 74321941kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution Time: 272064.651 ms
```
It uses broadcast motion instead of hash redistributed motion for the join
result of `warehouse` and `inventory` as it estimates that there are only 1 row
for the result. For the join condition 'inventory.inv_warehouse_sk =
warehouse.w_warehouse_sk' , orca doesn't know the foreign key info , so it
gives out a wrong estimation.
### What you think should happen instead
_No response_
### How to reproduce
psql -U gpadmin -v ON_ERROR_STOP=1 -A -q -t -P pager=off -v
EXPLAIN_ANALYZE=" " -f
/home/gpadmin/workspace/TPC-DS-HashData/05_sql/139.dsbench.39.sql
### 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]
