[Bug] ORCA cannot collect and use the table FK info

[fanfuxiaoran]

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

• I agree to follow this project's Code of Conduct.