my-ship-it commented on PR #1014:
URL: https://github.com/apache/cloudberry/pull/1014#issuecomment-2760113328
> > Nice feature! Have we tested how much improvement there is in
performance?
>
> 100G local TPCDS 67
>
> no open the split window function
>
> ```
>
QUERY PLAN
>
>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
-------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..177596.00 rows=100 width=68) (actual
time=523610.609..523610.756 rows=100 loops=1)
> -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..177595.99
rows=100 width=68) (actual time=523610.603..523610.719 rows=100 loops=1)
> Merge Key: share0_ref2.i_category, share0_ref2.i_class,
share0_ref2.i_brand, share0_ref2.i_product_name, share0_ref2.d_year,
share0_ref2.d_qoy, share0_ref2.d_moy, share0_ref2.s_store_id, (sum(COALESCE((sha
> re0_ref2.ss_sales_price * (share0_ref2.ss_quantity)::numeric),
'0'::numeric))), (rank() OVER (?))
> -> Limit (cost=0.00..177595.97 rows=34 width=68) (actual
time=522531.804..522531.843 rows=100 loops=1)
> -> Sort (cost=0.00..177595.97 rows=3670746 width=68)
(actual time=522531.798..522531.811 rows=100 loops=1)
> Sort Key: share0_ref2.i_category,
share0_ref2.i_class, share0_ref2.i_brand, share0_ref2.i_product_name,
share0_ref2.d_year, share0_ref2.d_qoy, share0_ref2.d_moy,
share0_ref2.s_store_id, (sum(CO
> ALESCE((share0_ref2.ss_sales_price * (share0_ref2.ss_quantity)::numeric),
'0'::numeric))), (rank() OVER (?))
> Sort Method: top-N heapsort Memory: 76kB
> Sort Method: quicksort Memory: 50kB
> -> Result (cost=0.00..146731.77 rows=3670746
width=68) (actual time=486153.708..522531.281 rows=500 loops=1)
> Filter: ((rank() OVER (?)) <= 100)
> -> WindowAgg (cost=0.00..146429.85
rows=9176865 width=68) (actual time=487303.951..522454.943 rows=19826918
loops=1)
> Partition By: share0_ref2.i_category
> Order By:
(sum(COALESCE((share0_ref2.ss_sales_price *
(share0_ref2.ss_quantity)::numeric), '0'::numeric)))
> -> Sort (cost=0.00..145879.24
rows=9176865 width=60) (actual time=487303.888..500640.596 rows=19826918
loops=1)
> Sort Key: share0_ref2.i_category,
(sum(COALESCE((share0_ref2.ss_sales_price *
(share0_ref2.ss_quantity)::numeric), '0'::numeric))) DESC
> Sort Method: external merge Disk:
4971392kB
> -> Redistribute Motion 3:3
(slice2; segments: 3) (cost=0.00..73669.44 rows=9176865 width=60) (actual
time=95016.791..374486.029 rows=19826918 loops=1)
> Hash Key:
share0_ref2.i_category
> -> Sequence
(cost=0.00..71946.02 rows=9176865 width=60) (actual time=95009.120..294309.818
rows=16474083 loops=1)
> -> Shared Scan (share
slice:id 2:0) (cost=0.00..50970.94 rows=19877815 width=1) (actual
time=64576.346..64576.415 rows=0 loops=1)
> -> Hash Join
(cost=0.00..50951.06 rows=19877815 width=93) (actual time=80.849..55414.852
rows=17955350 loops=1)
> Hash Cond:
(store_sales.ss_item_sk = item.i_item_sk)
> Extra Text:
(seg2) Initial batch 0:
> (seg2) Wrote 2624K bytes to inner workfile.
> (seg2) Wrote 563584K bytes to outer workfile.
> (seg2) Initial batch 1:
> (seg2) Read 2654K bytes from inner workfile.
> (seg2) Read 563608K bytes from outer workfile.
> (seg2) Hash chain length 2.4 avg, 12 max, using 28696 of 32768 buckets.
> -> Hash
Join (cost=0.00..39959.34 rows=19877815 width=43) (actual
time=30.457..43266.945 rows=17955350 loops=1)
> Hash
Cond: (store_sales.ss_store_sk = store.s_store_sk)
> Extra
Text: (seg1) Hash chain length 1.0 avg, 2 max, using 396 of 16384 buckets.
> ->
Hash Join (cost=0.00..32718.49 rows=19877815 width=30) (actual
time=28.526..38525.198 rows=18388460 loops=1)
>
Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
>
Extra Text: (seg1) Hash chain length 1.0 avg, 2 max, using 362 of 16384
buckets.
>
-> Seq Scan on store_sales (cost=0.00..8298.12 rows=95999008 width=22)
(actual time=28.246..25372.148 rows=96136561 loops=1)
>
-> Hash (cost=433.92..433.92 rows=378 width=16) (actual time=0.163..0.182
rows=366 loops=1)
>
Buckets: 16384 Batches: 1 Memory Usage: 146kB
>
-> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..433.92
rows=378 width=16) (actual time=0.050..0.101 rows=366 loops=1)
>
-> Seq Scan on date_dim (cost=0.00..433.81 rows=126 width=16)
(actual time=7.888..9.099 rows=131 loops=1)
>
Filter: ((d_month_seq >= 1194) AND (d_month_seq <= 1205))
> ->
Hash (cost=431.06..431.06 rows=402 width=21) (actual time=1.820..1.825
rows=402 loops=1)
>
Buckets: 16384 Batches: 1 Memory Usage: 149kB
>
-> Seq Scan on store (cost=0.00..431.06 rows=402 width=21) (actual
time=1.662..1.723 rows=402 loops=1)
> -> Hash
(cost=442.52..442.52 rows=68000 width=58) (actual time=51.313..51.314
rows=68056 loops=1)
>
Buckets: 16384 Batches: 2 Memory Usage: 3195kB
> ->
Seq Scan on item (cost=0.00..442.52 rows=68000 width=58) (actual
time=8.209..27.778 rows=68056 loops=1)
> -> Append
(cost=0.00..20424.47 rows=9176865 width=60) (actual time=29690.265..227495.952
rows=16474083 loops=1)
> -> HashAggregate
(cost=0.00..4067.30 rows=2261654 width=91) (actual time=29690.256..58869.239
rows=15717060 loops=1)
> Group Key:
share0_ref2.i_category, share0_ref2.i_class, share0_ref2.i_brand,
share0_ref2.i_product_name, share0_ref2.d_year, share0_ref2.d_qoy, share0_
> ref2.d_moy, share0_ref2.s_store_id
> Planned
Partitions: 64
> ->
Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..1596.25
rows=2261654 width=93) (actual time=0.031..12446.519 rows=17958340 loops=1)
> Hash
Key: share0_ref2.i_category, share0_ref2.i_class, share0_ref2.i_brand,
share0_ref2.i_product_name, share0_ref2.d_year, share0_ref2.d_qoy, sh
> are0_ref2.d_moy, share0_ref2.s_store_id
> ->
Shared Scan (share slice:id 4:0) (cost=0.00..937.90 rows=2261654 width=93)
(actual time=64175.709..67544.595 rows=17955350 loops=1)
> -> HashAggregate
(cost=0.00..3508.14 rows=2261654 width=74) (actual time=26920.341..39780.737
rows=467057 loops=1)
> Group Key:
share0_ref3.i_category, share0_ref3.i_class, share0_ref3.i_brand,
share0_ref3.i_product_name, share0_ref3.d_year, share0_ref3.d_qoy, share0_
> ref3.d_moy
> Planned
Partitions: 64
> ->
Redistribute Motion 3:3 (slice5; segments: 3) (cost=0.00..1383.25
rows=2261654 width=76) (actual time=0.045..10845.428 rows=17976018 loops=1)
> Hash
Key: share0_ref3.i_category, share0_ref3.i_class, share0_ref3.i_brand,
share0_ref3.i_product_name, share0_ref3.d_year, share0_ref3.d_qoy, sh
> are0_ref3.d_moy
> ->
Result (cost=0.00..845.24 rows=2261654 width=76) (actual
time=64176.217..71746.121 rows=17955350 loops=1)
>
-> Shared Scan (share slice:id 5:0) (cost=0.00..845.24 rows=2261654 width=76)
(actual time=64176.204..68475.124 rows=17955350 loops=1)
> -> HashAggregate
(cost=0.00..3156.72 rows=2261654 width=70) (actual time=25750.792..37326.978
rows=166615 loops=1)
> Group Key:
share0_ref4.i_category, share0_ref4.i_class, share0_ref4.i_brand,
share0_ref4.i_product_name, share0_ref4.d_year, share0_ref4.d_qoy
> Planned
Partitions: 64
> ->
Redistribute Motion 3:3 (slice6; segments: 3) (cost=0.00..1333.13
rows=2261654 width=72) (actual time=0.038..9769.600 rows=17994686 loops=1)
> Hash
Key: share0_ref4.i_category, share0_ref4.i_class, share0_ref4.i_brand,
share0_ref4.i_product_name, share0_ref4.d_year, share0_ref4.d_qoy
> ->
Result (cost=0.00..823.44 rows=2261654 width=72) (actual
time=64176.062..70529.421 rows=17955350 loops=1)
>
-> Shared Scan (share slice:id 6:0) (cost=0.00..823.44 rows=2261654 width=72)
(actual time=64176.053..67366.830 rows=17955350 loops=1)
> -> HashAggregate
(cost=0.00..2807.33 rows=2261654 width=66) (actual time=23863.814..34288.686
rows=78737 loops=1)
> Group Key:
share0_ref5.i_category, share0_ref5.i_class, share0_ref5.i_brand,
share0_ref5.i_product_name, share0_ref5.d_year
> Planned
Partitions: 64
> ->
Redistribute Motion 3:3 (slice7; segments: 3) (cost=0.00..1283.01
rows=2261654 width=68) (actual time=0.039..8534.496 rows=17989739 loops=1)
> Hash
Key: share0_ref5.i_category, share0_ref5.i_class, share0_ref5.i_brand,
share0_ref5.i_product_name, share0_ref5.d_year
> ->
Result (cost=0.00..801.64 rows=2261654 width=68) (actual
time=64176.002..70641.900 rows=17955350 loops=1)
>
-> Shared Scan (share slice:id 7:0) (cost=0.00..801.64 rows=2261654 width=68)
(actual time=64175.991..67525.520 rows=17955350 loops=1)
> -> Finalize
HashAggregate (cost=0.00..1985.44 rows=68000 width=62) (actual
time=42704.419..55000.120 rows=45314 loops=1)
> Group Key:
share0_ref6.i_category, share0_ref6.i_class, share0_ref6.i_brand,
share0_ref6.i_product_name
> Planned
Partitions: 4
> ->
Redistribute Motion 3:3 (slice8; segments: 3) (cost=0.00..1948.54 rows=68000
width=62) (actual time=0.027..29152.912 rows=13920693 loops=1)
> Hash
Key: share0_ref6.i_category, share0_ref6.i_class, share0_ref6.i_brand,
share0_ref6.i_product_name
> ->
Streaming Partial HashAggregate (cost=0.00..1935.35 rows=68000 width=62)
(actual time=64193.775..94813.201 rows=13890651 loops=1)
>
Group Key: share0_ref6.i_category, share0_ref6.i_class, share0_ref6.i_brand,
share0_ref6.i_product_name
>
Planned Partitions: 4
>
-> Shared Scan (share slice:id 8:0) (cost=0.00..779.84 rows=2261654 width=64)
(actual time=64175.078..67437.062 rows=17955350 loops=1)
> -> Finalize
HashAggregate (cost=0.00..1534.46 rows=62040 width=39) (actual
time=2.850..3.251 rows=424 loops=1)
> Group Key:
share0_ref7.i_category, share0_ref7.i_class, share0_ref7.i_brand
> Planned
Partitions: 4
> ->
Redistribute Motion 3:3 (slice9; segments: 3) (cost=0.00..1509.95 rows=62040
width=39) (actual time=0.037..0.418 rows=1078 loops=1)
> Hash
Key: share0_ref7.i_category, share0_ref7.i_class, share0_ref7.i_brand
> ->
Streaming Partial HashAggregate (cost=0.00..1502.38 rows=62040 width=39)
(actual time=79021.352..79022.281 rows=1039 loops=1)
>
Group Key: share0_ref7.i_category, share0_ref7.i_class, share0_ref7.i_brand
>
Planned Partitions: 4
>
-> Shared Scan (share slice:id 9:0) (cost=0.00..654.47 rows=2261654 width=41)
(actual time=64174.789..67057.898 rows=17955350 loops=1)
> -> Finalize
HashAggregate (cost=0.00..1115.83 rows=207 width=22) (actual time=0.256..0.283
rows=67 loops=1)
> Group Key:
share0_ref8.i_category, share0_ref8.i_class
> ->
Redistribute Motion 3:3 (slice10; segments: 3) (cost=0.00..1115.78 rows=207
width=22) (actual time=0.019..0.055 rows=154 loops=1)
> Hash
Key: share0_ref8.i_category, share0_ref8.i_class
> ->
Streaming Partial HashAggregate (cost=0.00..1115.76 rows=207 width=22) (actual
time=78380.489..78380.586 rows=153 loops=1)
>
Group Key: share0_ref8.i_category, share0_ref8.i_class
>
-> Shared Scan (share slice:id 10:0) (cost=0.00..561.81 rows=2261654
width=24) (actual time=64174.998..67021.572 rows=17955350 loops=1)
> -> Finalize
HashAggregate (cost=0.00..793.15 rows=4 width=14) (actual time=0.088..0.091
rows=5 loops=1)
> Group Key:
share0_ref9.i_category
> ->
Redistribute Motion 3:3 (slice11; segments: 3) (cost=0.00..793.15 rows=4
width=14) (actual time=0.007..0.015 rows=15 loops=1)
> Hash
Key: share0_ref9.i_category
> ->
Streaming Partial HashAggregate (cost=0.00..793.15 rows=4 width=14) (actual
time=76016.915..76016.924 rows=11 loops=1)
>
Group Key: share0_ref9.i_category
>
-> Shared Scan (share slice:id 11:0) (cost=0.00..518.21 rows=2261654
width=16) (actual time=64174.583..67087.364 rows=17955350 loops=1)
> -> Result
(cost=0.00..490.57 rows=1 width=60) (actual time=0.021..0.021 rows=1 loops=1)
> ->
Redistribute Motion 1:3 (slice12) (cost=0.00..490.57 rows=1 width=8) (actual
time=0.009..0.009 rows=1 loops=1)
> ->
Finalize Aggregate (cost=0.00..490.57 rows=1 width=8) (actual
time=73987.592..73987.593 rows=1 loops=1)
>
-> Gather Motion 3:1 (slice13; segments: 3) (cost=0.00..490.57 rows=1
width=8) (actual time=73087.948..73987.500 rows=3 loops=1)
>
-> Partial Aggregate (cost=0.00..490.57 rows=1 width=8) (actual
time=73224.866..73224.868 rows=1 loops=1)
>
-> Shared Scan (share slice:id 13:0) (cost=0.00..485.51
rows=2261654 width=10) (actual time=64174.706..67247.439 rows=1795535
> 0 loops=1)
> Planning Time: 177.489 ms
> (slice0) Executor memory: 6455K bytes.
> (slice1) Executor memory: 26257K bytes avg x 3x(0) workers, 26401K
bytes max (seg1). Work_mem: 9294K bytes max.
> * (slice2) Executor memory: 99381K bytes avg x 3x(0) workers, 99696K
bytes max (seg0). Work_mem: 140185K bytes max, 6247K bytes wanted.
> (slice3) Executor memory: 15830K bytes avg x 3x(0) workers, 15830K
bytes max (seg0).
> (slice4) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice5) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice6) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice7) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice8) Executor memory: 4312K bytes avg x 3x(0) workers, 4312K
bytes max (seg2). Work_mem: 6937K bytes max.
> (slice9) Executor memory: 1227K bytes avg x 3x(0) workers, 1232K
bytes max (seg0). Work_mem: 1297K bytes max.
> (slice10) Executor memory: 141K bytes avg x 3x(0) workers, 143K bytes
max (seg2). Work_mem: 160K bytes max.
> (slice11) Executor memory: 127K bytes avg x 3x(0) workers, 127K bytes
max (seg0). Work_mem: 24K bytes max.
> (slice12) Executor memory: 120K bytes (entry db).
> (slice13) Executor memory: 122K bytes avg x 3x(0) workers, 122K bytes
max (seg0).
> Memory used: 128000kB
> Memory wanted: 177989kB
> Optimizer: GPORCA
> Execution Time: 524116.710 ms
> (132 rows)
> ```
>
> open the split window function
>
> ```
>
QUERY PLAN
>
>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
-------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..30985.00 rows=100 width=68) (actual
time=414318.540..414318.818 rows=100 loops=1)
> -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..30984.99
rows=100 width=68) (actual time=414318.530..414318.773 rows=100 loops=1)
> Merge Key: share0_ref2.i_category, share0_ref2.i_class,
share0_ref2.i_brand, share0_ref2.i_product_name, share0_ref2.d_year,
share0_ref2.d_qoy, share0_ref2.d_moy, share0_ref2.s_store_id, (sum(COALESCE((sha
> re0_ref2.ss_sales_price * (share0_ref2.ss_quantity)::numeric),
'0'::numeric))), (rank() OVER (?))
> -> Limit (cost=0.00..30984.97 rows=34 width=68) (actual
time=414315.650..414315.686 rows=100 loops=1)
> -> Sort (cost=0.00..30984.96 rows=3670746 width=68)
(actual time=414315.645..414315.656 rows=100 loops=1)
> Sort Key: share0_ref2.i_category,
share0_ref2.i_class, share0_ref2.i_brand, share0_ref2.i_product_name,
share0_ref2.d_year, share0_ref2.d_qoy, share0_ref2.d_moy,
share0_ref2.s_store_id, (sum(CO
> ALESCE((share0_ref2.ss_sales_price * (share0_ref2.ss_quantity)::numeric),
'0'::numeric))), (rank() OVER (?))
> Sort Method: top-N heapsort Memory: 76kB
> Sort Method: quicksort Memory: 50kB
> -> Result (cost=0.00..120.77 rows=3670746 width=68)
(actual time=414313.331..414315.182 rows=500 loops=1)
> Filter: ((rank() OVER (?)) <= 100)
> -> WindowAgg (cost=0.00..0.00 rows=3670746
width=68) (actual time=414313.323..414315.038 rows=1500 loops=1)
> Partition By: share0_ref2.i_category
> Order By:
(sum(COALESCE((share0_ref2.ss_sales_price *
(share0_ref2.ss_quantity)::numeric), '0'::numeric)))
> -> Sort (cost=0.00..172930.83
rows=3670746 width=60) (actual time=414313.268..414313.375 rows=1500 loops=1)
> Sort Key: share0_ref2.i_category,
(sum(COALESCE((share0_ref2.ss_sales_price *
(share0_ref2.ss_quantity)::numeric), '0'::numeric))) DESC
> Sort Method: quicksort Memory:
536kB
> -> Redistribute Motion 3:3
(slice2; segments: 3) (cost=0.00..145697.72 rows=3670746 width=60) (actual
time=397585.236..414308.966 rows=1500 loops=1)
> Hash Key:
share0_ref2.i_category
> -> Result
(cost=0.00..145008.35 rows=3670746 width=60) (actual
time=381889.133..413604.970 rows=1100 loops=1)
> Filter: ((rank() OVER
(?)) <= 100)
> -> WindowAgg
(cost=0.00..144706.44 rows=9176865 width=68) (actual
time=382110.872..412837.000 rows=16474083 loops=1)
> Partition By:
share0_ref2.i_category
> Order By:
(sum(COALESCE((share0_ref2.ss_sales_price *
(share0_ref2.ss_quantity)::numeric), '0'::numeric)))
> -> Sort
(cost=0.00..144155.82 rows=9176865 width=60) (actual
time=382110.744..394853.266 rows=16474083 loops=1)
> Sort Key:
share0_ref2.i_category, (sum(COALESCE((share0_ref2.ss_sales_price *
(share0_ref2.ss_quantity)::numeric), '0'::numeric))) DESC
> Sort
Method: external merge Disk: 4971424kB
> ->
Sequence (cost=0.00..71946.02 rows=9176865 width=60) (actual
time=92851.018..287538.555 rows=16474083 loops=1)
> ->
Shared Scan (share slice:id 2:0) (cost=0.00..50970.94 rows=19877815 width=1)
(actual time=64096.019..64096.134 rows=0 loops=1)
>
-> Hash Join (cost=0.00..50951.06 rows=19877815 width=93) (actual
time=89.246..55333.745 rows=17955350 loops=1)
>
Hash Cond: (store_sales.ss_item_sk = item.i_item_sk)
>
Extra Text: (seg2) Initial batch 0:
> (seg2) Wrote 2624K bytes to inner workfile.
> (seg2) Wrote 563584K bytes to outer workfile.
> (seg2) Initial batch 1:
> (seg2) Read 2654K bytes from inner workfile.
> (seg2) Read 563608K bytes from outer workfile.
> (seg2) Hash chain length 2.4 avg, 12 max, using 28696 of 32768 buckets.
>
-> Hash Join (cost=0.00..39959.34 rows=19877815 width=43) (actual
time=34.522..43323.503 rows=17955350 loops=1)
>
Hash Cond: (store_sales.ss_store_sk = store.s_store_sk)
>
Extra Text: (seg1) Hash chain length 1.0 avg, 2 max, using 396 of
16384 buckets.
>
-> Hash Join (cost=0.00..32718.49 rows=19877815 width=30) (actual
time=32.546..38605.147 rows=18388460 loops=1)
>
Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
>
Extra Text: (seg1) Hash chain length 1.0 avg, 2 max, using
362 of 16384 buckets.
>
-> Seq Scan on store_sales (cost=0.00..8298.12
rows=95999008 width=22) (actual time=32.223..24974.741 rows=96136561 loo
> ps=1)
>
-> Hash (cost=433.92..433.92 rows=378 width=16) (actual
time=0.158..0.197 rows=366 loops=1)
>
Buckets: 16384 Batches: 1 Memory Usage: 146kB
>
-> Broadcast Motion 3:3 (slice3; segments: 3)
(cost=0.00..433.92 rows=378 width=16) (actual time=0.043..0.086 ro
> ws=366 loops=1)
>
-> Seq Scan on date_dim (cost=0.00..433.81
rows=126 width=16) (actual time=8.465..9.638 rows=131 loops=1)
>
Filter: ((d_month_seq >= 1194) AND
(d_month_seq <= 1205))
>
-> Hash (cost=431.06..431.06 rows=402 width=21) (actual
time=1.570..1.574 rows=402 loops=1)
>
Buckets: 16384 Batches: 1 Memory Usage: 149kB
>
-> Seq Scan on store (cost=0.00..431.06 rows=402 width=21)
(actual time=1.422..1.482 rows=402 loops=1)
>
-> Hash (cost=442.52..442.52 rows=68000 width=58) (actual
time=54.991..54.998 rows=68056 loops=1)
>
Buckets: 16384 Batches: 2 Memory Usage: 3195kB
>
-> Seq Scan on item (cost=0.00..442.52 rows=68000 width=58)
(actual time=9.099..29.329 rows=68056 loops=1)
> ->
Append (cost=0.00..20424.47 rows=9176865 width=60) (actual
time=29250.062..222573.570 rows=16474083 loops=1)
>
-> HashAggregate (cost=0.00..4067.30 rows=2261654 width=91) (actual
time=29250.052..56813.808 rows=15717060 loops=1)
>
Group Key: share0_ref2.i_category, share0_ref2.i_class,
share0_ref2.i_brand, share0_ref2.i_product_name, share0_ref2.d_year, share0_r
> ef2.d_qoy, share0_ref2.d_moy, share0_ref2.s_store_id
>
Planned Partitions: 64
>
-> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..1596.25
rows=2261654 width=93) (actual time=0.708..12617.517 rows=179
> 58340 loops=1)
>
Hash Key: share0_ref2.i_category, share0_ref2.i_class,
share0_ref2.i_brand, share0_ref2.i_product_name, share0_ref2.d_year, sha
> re0_ref2.d_qoy, share0_ref2.d_moy, share0_ref2.s_store_id
>
-> Shared Scan (share slice:id 4:0) (cost=0.00..937.90
rows=2261654 width=93) (actual time=63805.090..67028.352 rows=17955350
> loops=1)
>
-> HashAggregate (cost=0.00..3508.14 rows=2261654 width=74) (actual
time=26552.708..39255.732 rows=467057 loops=1)
>
Group Key: share0_ref3.i_category, share0_ref3.i_class,
share0_ref3.i_brand, share0_ref3.i_product_name, share0_ref3.d_year, share0_r
> ef3.d_qoy, share0_ref3.d_moy
>
Planned Partitions: 64
>
-> Redistribute Motion 3:3 (slice5; segments: 3) (cost=0.00..1383.25
rows=2261654 width=76) (actual time=0.043..11135.810 rows=179
> 76018 loops=1)
>
Hash Key: share0_ref3.i_category, share0_ref3.i_class,
share0_ref3.i_brand, share0_ref3.i_product_name, share0_ref3.d_year, sha
> re0_ref3.d_qoy, share0_ref3.d_moy
>
-> Result (cost=0.00..845.24 rows=2261654 width=76) (actual
time=63804.232..70554.258 rows=17955350 loops=1)
>
-> Shared Scan (share slice:id 5:0) (cost=0.00..845.24
rows=2261654 width=76) (actual time=63804.223..67289.819 rows=17
> 955350 loops=1)
>
-> HashAggregate (cost=0.00..3156.72 rows=2261654 width=70) (actual
time=25094.724..36630.710 rows=166615 loops=1)
>
Group Key: share0_ref4.i_category, share0_ref4.i_class,
share0_ref4.i_brand, share0_ref4.i_product_name, share0_ref4.d_year, share0_r
> ef4.d_qoy
>
Planned Partitions: 64
>
-> Redistribute Motion 3:3 (slice6; segments: 3) (cost=0.00..1333.13
rows=2261654 width=72) (actual time=0.037..9229.505 rows=1799
> 4686 loops=1)
>
Hash Key: share0_ref4.i_category, share0_ref4.i_class,
share0_ref4.i_brand, share0_ref4.i_product_name, share0_ref4.d_year, sha
> re0_ref4.d_qoy
>
-> Result (cost=0.00..823.44 rows=2261654 width=72) (actual
time=63804.724..70328.415 rows=17955350 loops=1)
>
-> Shared Scan (share slice:id 6:0) (cost=0.00..823.44
rows=2261654 width=72) (actual time=63804.716..67093.220 rows=17
> 955350 loops=1)
>
-> HashAggregate (cost=0.00..2807.33 rows=2261654 width=66) (actual
time=22360.839..32882.021 rows=78737 loops=1)
>
Group Key: share0_ref5.i_category, share0_ref5.i_class,
share0_ref5.i_brand, share0_ref5.i_product_name, share0_ref5.d_year
>
Planned Partitions: 64
>
-> Redistribute Motion 3:3 (slice7; segments: 3) (cost=0.00..1283.01
rows=2261654 width=68) (actual time=0.048..7905.640 rows=1798
> 9739 loops=1)
>
Hash Key: share0_ref5.i_category, share0_ref5.i_class,
share0_ref5.i_brand, share0_ref5.i_product_name, share0_ref5.d_year
>
-> Result (cost=0.00..801.64 rows=2261654 width=68) (actual
time=63804.325..70084.480 rows=17955350 loops=1)
>
-> Shared Scan (share slice:id 7:0) (cost=0.00..801.64
rows=2261654 width=68) (actual time=63804.317..67023.074 rows=17
> 955350 loops=1)
>
-> Finalize HashAggregate (cost=0.00..1985.44 rows=68000 width=62) (actual
time=42461.294..54990.900 rows=45314 loops=1)
>
Group Key: share0_ref6.i_category, share0_ref6.i_class,
share0_ref6.i_brand, share0_ref6.i_product_name
>
Planned Partitions: 4
>
-> Redistribute Motion 3:3 (slice8; segments: 3) (cost=0.00..1948.54
rows=68000 width=62) (actual time=0.039..29150.048 rows=13920
> 693 loops=1)
>
Hash Key: share0_ref6.i_category, share0_ref6.i_class,
share0_ref6.i_brand, share0_ref6.i_product_name
>
-> Streaming Partial HashAggregate (cost=0.00..1935.35 rows=68000
width=62) (actual time=63817.909..94373.413 rows=13890651 l
> oops=1)
>
Group Key: share0_ref6.i_category, share0_ref6.i_class,
share0_ref6.i_brand, share0_ref6.i_product_name
>
Planned Partitions: 4
>
-> Shared Scan (share slice:id 8:0) (cost=0.00..779.84
rows=2261654 width=64) (actual time=63803.481..67249.046 rows=17
> 955350 loops=1)
>
-> Finalize HashAggregate (cost=0.00..1534.46 rows=62040 width=39) (actual
time=1.949..2.290 rows=424 loops=1)
>
Group Key: share0_ref7.i_category, share0_ref7.i_class,
share0_ref7.i_brand
>
Planned Partitions: 4
>
-> Redistribute Motion 3:3 (slice9; segments: 3) (cost=0.00..1509.95
rows=62040 width=39) (actual time=0.255..0.632 rows=1078 loop
> s=1)
>
Hash Key: share0_ref7.i_category, share0_ref7.i_class,
share0_ref7.i_brand
>
-> Streaming Partial HashAggregate (cost=0.00..1502.38 rows=62040
width=39) (actual time=78456.724..78457.648 rows=1039 loops
> =1)
>
Group Key: share0_ref7.i_category, share0_ref7.i_class,
share0_ref7.i_brand
>
Planned Partitions: 4
>
-> Shared Scan (share slice:id 9:0) (cost=0.00..654.47
rows=2261654 width=41) (actual time=63802.813..66669.679 rows=17
> 955350 loops=1)
>
-> Finalize HashAggregate (cost=0.00..1115.83 rows=207 width=22) (actual
time=0.225..0.254 rows=67 loops=1)
>
Group Key: share0_ref8.i_category, share0_ref8.i_class
>
-> Redistribute Motion 3:3 (slice10; segments: 3) (cost=0.00..1115.78
rows=207 width=22) (actual time=0.019..0.065 rows=154 loops=
> 1)
>
Hash Key: share0_ref8.i_category, share0_ref8.i_class
>
-> Streaming Partial HashAggregate (cost=0.00..1115.76 rows=207
width=22) (actual time=76435.377..76435.481 rows=153 loops=1)
>
Group Key: share0_ref8.i_category, share0_ref8.i_class
>
-> Shared Scan (share slice:id 10:0) (cost=0.00..561.81
rows=2261654 width=24) (actual time=63804.490..66601.543 rows=1
> 7955350 loops=1)
>
-> Finalize HashAggregate (cost=0.00..793.15 rows=4 width=14) (actual
time=0.090..0.096 rows=5 loops=1)
>
Group Key: share0_ref9.i_category
>
-> Redistribute Motion 3:3 (slice11; segments: 3) (cost=0.00..793.15
rows=4 width=14) (actual time=0.010..0.019 rows=15 loops=1)
>
Hash Key: share0_ref9.i_category
>
-> Streaming Partial HashAggregate (cost=0.00..793.15 rows=4
width=14) (actual time=75535.403..75535.413 rows=11 loops=1)
>
Group Key: share0_ref9.i_category
>
-> Shared Scan (share slice:id 11:0) (cost=0.00..518.21
rows=2261654 width=16) (actual time=63804.398..66633.023 rows=1
> 7955350 loops=1)
>
-> Result (cost=0.00..490.57 rows=1 width=60) (actual time=0.021..0.022
rows=1 loops=1)
>
-> Redistribute Motion 1:3 (slice12) (cost=0.00..490.57 rows=1
width=8) (actual time=0.013..0.014 rows=1 loops=1)
>
-> Finalize Aggregate (cost=0.00..490.57 rows=1 width=8) (actual
time=72588.584..72588.585 rows=1 loops=1)
>
-> Gather Motion 3:1 (slice13; segments: 3)
(cost=0.00..490.57 rows=1 width=8) (actual time=72310.130..72588.489 rows=
> 3 loops=1)
>
-> Partial Aggregate (cost=0.00..490.57 rows=1
width=8) (actual time=72589.020..72589.023 rows=1 loops=1)
>
-> Shared Scan (share slice:id 13:0)
(cost=0.00..485.51 rows=2261654 width=10) (actual time=63803.363..6673
> 2.948 rows=17955350 loops=1)
> Planning Time: 180.127 ms
> (slice0) Executor memory: 6488K bytes.
> (slice1) Executor memory: 234K bytes avg x 3x(0) workers, 284K bytes
max (seg0). Work_mem: 164K bytes max.
> * (slice2) Executor memory: 99388K bytes avg x 3x(0) workers, 99698K
bytes max (seg0). Work_mem: 140185K bytes max, 6247K bytes wanted.
> (slice3) Executor memory: 15830K bytes avg x 3x(0) workers, 15830K
bytes max (seg0).
> (slice4) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice5) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice6) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice7) Executor memory: 115K bytes avg x 3x(0) workers, 115K bytes
max (seg0).
> (slice8) Executor memory: 4312K bytes avg x 3x(0) workers, 4312K
bytes max (seg2). Work_mem: 6937K bytes max.
> (slice9) Executor memory: 1227K bytes avg x 3x(0) workers, 1232K
bytes max (seg0). Work_mem: 1297K bytes max.
> (slice10) Executor memory: 141K bytes avg x 3x(0) workers, 143K bytes
max (seg2). Work_mem: 160K bytes max.
> (slice11) Executor memory: 127K bytes avg x 3x(0) workers, 127K bytes
max (seg0). Work_mem: 24K bytes max.
> (slice12) Executor memory: 120K bytes (entry db).
> (slice13) Executor memory: 122K bytes avg x 3x(0) workers, 122K bytes
max (seg0).
> Memory used: 128000kB
> Memory wanted: 190581kB
> Optimizer: GPORCA
> Execution Time: 415836.637 ms
> (140 rows)
> ```
>
> 523610.756 -> 414318.818
There are only 3 segments in the test. If we increase the number of
segments, If I understand correctly, the acceleration effect is more obvious
because of data skew.
--
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]
