jiaqizho commented on PR #1014:
URL: https://github.com/apache/cloudberry/pull/1014#issuecomment-2757258520
For the tpcds-sql-67. local 10G
plan without split window function:
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..7038.29 rows=100 width=68) (actual
time=10153.758..10154.752 rows=100 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..7038.28
rows=100 width=68) (actual time=10153.748..10154.707 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..7038.25 rows=34 width=68) (actual
time=10150.843..10150.932 rows=100 loops=1)
-> Sort (cost=0.00..7038.25 rows=38131 width=68) (actual
time=10150.834..10150.848 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: 77kB
Sort Method: quicksort Memory: 52kB
-> Result (cost=0.00..6814.51 rows=38131 width=68)
(actual time=9072.669..10148.623 rows=500 loops=1)
Filter: ((rank() OVER (?)) <= 100)
-> WindowAgg (cost=0.00..6811.38 rows=95327
width=68) (actual time=9072.660..10119.180 rows=208682 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..6805.66 rows=95327
width=60) (actual time=9072.576..9414.017 rows=208682 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:
48480kB
-> Redistribute Motion 3:3 (slice2;
segments: 3) (cost=0.00..6269.25 rows=95327 width=60) (actual
time=3027.517..7256.960 rows=208682 loops=1)
Hash Key: share0_ref2.i_category
-> Sequence
(cost=0.00..6251.34 rows=95327 width=60) (actual time=3018.873..6660.456
rows=172882 loops=1)
-> Shared Scan (share
slice:id 2:0) (cost=0.00..2209.68 rows=182629 width=1) (actual
time=2383.044..2383.075 rows=0 loops=1)
-> Hash Join
(cost=0.00..2209.50 rows=182629 width=89) (actual time=24.197..2274.242
rows=181070 loops=1)
Hash Cond:
(store_sales.ss_item_sk = item.i_item_sk)
Extra Text:
(seg1) Hash chain length 1.4 avg, 5 max, using 4330 of 8192 buckets.
-> Hash Join
(cost=0.00..1675.53 rows=182629 width=43) (actual time=9.717..2123.126
rows=181070 loops=1)
Hash
Cond: (store_sales.ss_store_sk = store.s_store_sk)
Extra
Text: (seg1) Hash chain length 1.0 avg, 1 max, using 12 of 16384 buckets.
->
Hash Join (cost=0.00..1181.96 rows=182629 width=30) (actual
time=7.809..1846.978 rows=185482 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..509.68 rows=960135 width=22) (actual
time=7.419..822.884 rows=980715 loops=1)
-> Hash (cost=433.91..433.91 rows=347 width=16) (actual time=0.294..0.301
rows=366 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 146kB
-> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..433.91
rows=347 width=16) (actual time=0.056..0.186 rows=366 loops=1)
-> Seq Scan on date_dim (cost=0.00..433.81 rows=116 width=16)
(actual time=10.089..14.519 rows=131 loops=1)
Filter: ((d_month_seq >= 1194) AND (d_month_seq <= 1205))
->
Hash (cost=431.00..431.00 rows=12 width=21) (actual time=2.079..2.082 rows=12
loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 129kB
-> Seq Scan on store (cost=0.00..431.00 rows=12 width=21) (actual
time=2.055..2.068 rows=12 loops=1)
-> Hash
(cost=432.00..432.00 rows=6000 width=54) (actual time=14.257..14.261 rows=6106
loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 588kB
-> Seq
Scan on item (cost=0.00..432.00 rows=6000 width=54) (actual time=6.114..10.397
rows=6106 loops=1)
-> Append
(cost=0.00..4035.94 rows=95327 width=60) (actual time=923.653..4546.777
rows=172882 loops=1)
-> HashAggregate
(cost=0.00..463.83 rows=20780 width=87) (actual time=555.017..1181.432
rows=113790 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
->
Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..441.25 rows=20780
width=89) (actual time=2.425..507.919 rows=177732 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..435.46 rows=20780 width=89)
(actual time=2470.814..2535.839 rows=181070 loops=1)
-> HashAggregate
(cost=0.00..458.70 rows=20780 width=70) (actual time=508.477..832.686
rows=36087 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
->
Redistribute Motion 3:3 (slice5; segments: 3) (cost=0.00..439.29 rows=20780
width=72) (actual time=0.048..103.865 rows=179338 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..434.61 rows=20780 width=72) (actual time=2469.669..2613.962
rows=181070 loops=1)
-> Shared Scan (share slice:id 5:0) (cost=0.00..434.61 rows=20780 width=72)
(actual time=2469.656..2536.910 rows=181070 loops=1)
-> HashAggregate
(cost=0.00..455.48 rows=20780 width=66) (actual time=548.505..686.800
rows=12622 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
->
Redistribute Motion 3:3 (slice6; segments: 3) (cost=0.00..438.83 rows=20780
width=68) (actual time=0.044..113.228 rows=181268 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..434.41 rows=20780 width=68) (actual time=2472.245..2605.584
rows=181070 loops=1)
-> Shared Scan (share slice:id 6:0) (cost=0.00..434.41 rows=20780 width=68)
(actual time=2472.232..2535.078 rows=181070 loops=1)
-> HashAggregate
(cost=0.00..452.28 rows=20780 width=62) (actual time=513.952..522.977 rows=6384
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
->
Redistribute Motion 3:3 (slice7; segments: 3) (cost=0.00..438.37 rows=20780
width=64) (actual time=0.041..97.226 rows=179875 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..434.20 rows=20780 width=64) (actual time=2470.839..2604.703
rows=181070 loops=1)
-> Shared Scan (share slice:id 7:0) (cost=0.00..434.20 rows=20780 width=64)
(actual time=2470.826..2535.443 rows=181070 loops=1)
-> HashAggregate
(cost=0.00..448.62 rows=6000 width=58) (actual time=475.644..482.095 rows=4043
loops=1)
Group Key:
share0_ref6.i_category, share0_ref6.i_class, share0_ref6.i_brand,
share0_ref6.i_product_name
->
Redistribute Motion 3:3 (slice8; segments: 3) (cost=0.00..437.91 rows=20780
width=60) (actual time=0.044..91.095 rows=179371 loops=1)
Hash
Key: share0_ref6.i_category, share0_ref6.i_class, share0_ref6.i_brand,
share0_ref6.i_product_name
->
Result (cost=0.00..434.00 rows=20780 width=60) (actual time=2472.201..2602.562
rows=181070 loops=1)
-> Shared Scan (share slice:id 8:0) (cost=0.00..434.00 rows=20780 width=60)
(actual time=2472.189..2535.728 rows=181070 loops=1)
-> HashAggregate
(cost=0.00..443.60 rows=6000 width=39) (actual time=451.286..451.735 rows=326
loops=1)
Group Key:
share0_ref7.i_category, share0_ref7.i_class, share0_ref7.i_brand
->
Redistribute Motion 3:3 (slice9; segments: 3) (cost=0.00..435.72 rows=20780
width=41) (actual time=0.075..98.432 rows=190111 loops=1)
Hash
Key: share0_ref7.i_category, share0_ref7.i_class, share0_ref7.i_brand
->
Result (cost=0.00..433.05 rows=20780 width=41) (actual time=2469.274..2596.752
rows=181070 loops=1)
-> Shared Scan (share slice:id 9:0) (cost=0.00..433.05 rows=20780 width=41)
(actual time=2469.261..2531.998 rows=181070 loops=1)
-> Finalize
HashAggregate (cost=0.00..437.37 rows=207 width=22) (actual time=0.453..0.508
rows=44 loops=1)
Group Key:
share0_ref8.i_category, share0_ref8.i_class
->
Redistribute Motion 3:3 (slice10; segments: 3) (cost=0.00..437.31 rows=207
width=22) (actual time=0.031..0.081 rows=114 loops=1)
Hash
Key: share0_ref8.i_category, share0_ref8.i_class
->
Streaming Partial HashAggregate (cost=0.00..437.30 rows=207 width=22) (actual
time=2783.335..2783.560 rows=113 loops=1)
Group Key: share0_ref8.i_category, share0_ref8.i_class
-> Shared Scan (share slice:id 10:0) (cost=0.00..432.20 rows=20780 width=24)
(actual time=2471.185..2536.471 rows=181070 loops=1)
-> Finalize
HashAggregate (cost=0.00..434.33 rows=4 width=14) (actual time=0.071..0.077
rows=5 loops=1)
Group Key:
share0_ref9.i_category
->
Redistribute Motion 3:3 (slice11; segments: 3) (cost=0.00..434.33 rows=4
width=14) (actual time=0.006..0.019 rows=15 loops=1)
Hash
Key: share0_ref9.i_category
->
Streaming Partial HashAggregate (cost=0.00..434.33 rows=4 width=14) (actual
time=2725.057..2725.078 rows=11 loops=1)
Group Key: share0_ref9.i_category
-> Shared Scan (share slice:id 11:0) (cost=0.00..431.80 rows=20780 width=16)
(actual time=2471.775..2539.370 rows=181070 loops=1)
-> Result
(cost=0.00..431.55 rows=1 width=60) (actual time=0.013..0.014 rows=1 loops=1)
->
Redistribute Motion 1:3 (slice12) (cost=0.00..431.55 rows=1 width=8) (actual
time=0.007..0.007 rows=1 loops=1)
->
Finalize Aggregate (cost=0.00..431.55 rows=1 width=8) (actual
time=2707.500..2707.501 rows=1 loops=1)
-> Gather Motion 3:1 (slice13; segments: 3) (cost=0.00..431.55 rows=1
width=8) (actual time=2322.799..2707.346 rows=3 loops=1)
-> Partial Aggregate (cost=0.00..431.55 rows=1 width=8) (actual
time=2613.818..2613.820 rows=1 loops=1)
-> Shared Scan (share slice:id 13:0) (cost=0.00..431.50 rows=20780
width=10) (actual time=2469.559..2537.793 rows=181070 loop
s=1)
Planning Time: 762.569 ms
(slice0) Executor memory: 4122K bytes.
(slice1) Executor memory: 11153K bytes avg x 3x(0) workers, 11186K
bytes max (seg2). Work_mem: 9702K bytes max.
(slice2) Executor memory: 50497K bytes avg x 3x(0) workers, 50543K
bytes max (seg2). Work_mem: 7081K bytes max.
(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: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice6) Executor memory: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice7) Executor memory: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice8) Executor memory: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice9) Executor memory: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice10) Executor memory: 136K bytes avg x 3x(0) workers, 136K bytes
max (seg0). Work_mem: 96K bytes max.
(slice11) Executor memory: 127K bytes avg x 3x(0) workers, 127K bytes
max (seg0). Work_mem: 24K bytes max.
(slice12) Executor memory: 121K bytes (entry db).
(slice13) Executor memory: 122K bytes avg x 3x(0) workers, 122K bytes
max (seg0).
Memory used: 128000kB
Optimizer: GPORCA
Execution Time: 10267.048 ms
(115 rows)
```
plan with the split window function:
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..225.03 rows=100 width=68) (actual
time=8575.970..8576.110 rows=100 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..225.02 rows=100
width=68) (actual time=8575.963..8576.059 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..225.00 rows=34 width=68) (actual
time=8572.731..8572.780 rows=100 loops=1)
-> Sort (cost=0.00..224.99 rows=38131 width=68) (actual
time=8572.729..8572.742 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: 77kB
Sort Method: quicksort Memory: 52kB
-> Result (cost=0.00..1.25 rows=38131 width=68)
(actual time=8566.463..8571.973 rows=500 loops=1)
Filter: ((rank() OVER (?)) <= 100)
-> WindowAgg (cost=0.00..0.00 rows=38131
width=68) (actual time=8566.457..8571.644 rows=1502 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..7001.19 rows=38131
width=60) (actual time=8566.419..8566.636 rows=1502 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: 573kB
-> Redistribute Motion 3:3 (slice2;
segments: 3) (cost=0.00..6803.77 rows=38131 width=60) (actual
time=8088.035..8559.296 rows=1502 loops=1)
Hash Key: share0_ref2.i_category
-> Result (cost=0.00..6796.61
rows=38131 width=60) (actual time=7650.464..8518.159 rows=1101 loops=1)
Filter: ((rank() OVER
(?)) <= 100)
-> WindowAgg
(cost=0.00..6793.47 rows=95327 width=68) (actual time=7653.856..8526.190
rows=172883 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..6787.76 rows=95327 width=60) (actual time=7653.805..7940.998
rows=172883 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: 48512kB
-> Sequence
(cost=0.00..6251.34 rows=95327 width=60) (actual time=2669.672..6258.307
rows=172883 loops=1)
->
Shared Scan (share slice:id 2:0) (cost=0.00..2209.68 rows=182629 width=1)
(actual time=2024.389..2024.427 rows=0 loops=1)
-> Hash Join (cost=0.00..2209.50 rows=182629 width=89) (actual
time=22.931..1721.069 rows=181070 loops=1)
Hash Cond: (store_sales.ss_item_sk = item.i_item_sk)
Extra Text: (seg1) Hash chain length 1.4 avg, 5 max, using 4330 of 8192
buckets.
-> Hash Join (cost=0.00..1675.53 rows=182629 width=43) (actual
time=8.004..1515.153 rows=181070 loops=1)
Hash Cond: (store_sales.ss_store_sk = store.s_store_sk)
Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 12 of
16384 buckets.
-> Hash Join (cost=0.00..1181.96 rows=182629 width=30) (actual
time=6.040..1344.056 rows=185482 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..509.68 rows=960135
width=22) (actual time=5.646..863.817 rows=980715 loops=1)
-> Hash (cost=433.91..433.91 rows=347 width=16) (actual
time=0.270..0.284 rows=366 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 146kB
-> Broadcast Motion 3:3 (slice3; segments: 3)
(cost=0.00..433.91 rows=347 width=16) (actual time=0.054..0.175 ro
ws=366 loops=1)
-> Seq Scan on date_dim (cost=0.00..433.81
rows=116 width=16) (actual time=9.347..13.743 rows=131 loops=1)
Filter: ((d_month_seq >= 1194) AND
(d_month_seq <= 1205))
-> Hash (cost=431.00..431.00 rows=12 width=21) (actual
time=1.866..1.869 rows=12 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 129kB
-> Seq Scan on store (cost=0.00..431.00 rows=12 width=21)
(actual time=1.843..1.855 rows=12 loops=1)
-> Hash (cost=432.00..432.00 rows=6000 width=54) (actual
time=14.732..14.734 rows=6106 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 588kB
-> Seq Scan on item (cost=0.00..432.00 rows=6000 width=54) (actual
time=6.420..10.918 rows=6106 loops=1)
->
Append (cost=0.00..4035.94 rows=95327 width=60) (actual time=521.061..4086.452
rows=172883 loops=1)
-> HashAggregate (cost=0.00..463.83 rows=20780 width=87) (actual
time=770.509..1383.728 rows=113790 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
-> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..441.25
rows=20780 width=89) (actual time=0.039..116.175 rows=177732 l
oops=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..435.46 rows=20780
width=89) (actual time=1903.949..1968.391 rows=181070 loops
=1)
-> HashAggregate (cost=0.00..458.70 rows=20780 width=70) (actual
time=492.523..815.136 rows=36087 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
-> Redistribute Motion 3:3 (slice5; segments: 3) (cost=0.00..439.29
rows=20780 width=72) (actual time=0.057..97.012 rows=179338 lo
ops=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..434.61 rows=20780 width=72) (actual
time=1903.203..2043.166 rows=181070 loops=1)
-> Shared Scan (share slice:id 5:0) (cost=0.00..434.61
rows=20780 width=72) (actual time=1903.195..1967.052 rows=181070
loops=1)
-> HashAggregate (cost=0.00..455.48 rows=20780 width=66) (actual
time=529.268..650.626 rows=12622 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
-> Redistribute Motion 3:3 (slice6; segments: 3) (cost=0.00..438.83
rows=20780 width=68) (actual time=0.064..104.718 rows=181268 l
oops=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..434.41 rows=20780 width=68) (actual
time=1903.759..2037.430 rows=181070 loops=1)
-> Shared Scan (share slice:id 6:0) (cost=0.00..434.41
rows=20780 width=68) (actual time=1903.752..1967.926 rows=181070
loops=1)
-> HashAggregate (cost=0.00..452.28 rows=20780 width=62) (actual
time=518.527..527.316 rows=6384 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
-> Redistribute Motion 3:3 (slice7; segments: 3) (cost=0.00..438.37
rows=20780 width=64) (actual time=0.054..104.862 rows=179875 l
oops=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..434.20 rows=20780 width=64) (actual
time=1904.051..2035.170 rows=181070 loops=1)
-> Shared Scan (share slice:id 7:0) (cost=0.00..434.20
rows=20780 width=64) (actual time=1904.036..1967.079 rows=181070
loops=1)
-> HashAggregate (cost=0.00..448.62 rows=6000 width=58) (actual
time=473.596..478.248 rows=4043 loops=1)
Group Key: share0_ref6.i_category, share0_ref6.i_class,
share0_ref6.i_brand, share0_ref6.i_product_name
-> Redistribute Motion 3:3 (slice8; segments: 3) (cost=0.00..437.91
rows=20780 width=60) (actual time=0.055..94.226 rows=179371 lo
ops=1)
Hash Key: share0_ref6.i_category, share0_ref6.i_class,
share0_ref6.i_brand, share0_ref6.i_product_name
-> Result (cost=0.00..434.00 rows=20780 width=60) (actual
time=1903.872..2032.489 rows=181070 loops=1)
-> Shared Scan (share slice:id 8:0) (cost=0.00..434.00
rows=20780 width=60) (actual time=1903.843..1966.706 rows=181070
loops=1)
-> HashAggregate (cost=0.00..443.60 rows=6000 width=39) (actual
time=451.425..451.819 rows=326 loops=1)
Group Key: share0_ref7.i_category, share0_ref7.i_class, share0_ref7.i_brand
-> Redistribute Motion 3:3 (slice9; segments: 3) (cost=0.00..435.72
rows=20780 width=41) (actual time=0.051..99.656 rows=190111 lo
ops=1)
Hash Key: share0_ref7.i_category, share0_ref7.i_class,
share0_ref7.i_brand
-> Result (cost=0.00..433.05 rows=20780 width=41) (actual
time=1903.696..2030.135 rows=181070 loops=1)
-> Shared Scan (share slice:id 9:0) (cost=0.00..433.05
rows=20780 width=41) (actual time=1903.684..1965.483 rows=181070
loops=1)
-> Finalize HashAggregate (cost=0.00..437.37 rows=207 width=22) (actual
time=0.373..0.423 rows=44 loops=1)
Group Key: share0_ref8.i_category, share0_ref8.i_class
-> Redistribute Motion 3:3 (slice10; segments: 3) (cost=0.00..437.31
rows=207 width=22) (actual time=0.015..0.065 rows=114 loops=1
)
Hash Key: share0_ref8.i_category, share0_ref8.i_class
-> Streaming Partial HashAggregate (cost=0.00..437.30 rows=207
width=22) (actual time=2391.181..2391.403 rows=113 loops=1)
Group Key: share0_ref8.i_category, share0_ref8.i_class
-> Shared Scan (share slice:id 10:0) (cost=0.00..432.20
rows=20780 width=24) (actual time=1901.628..1966.655 rows=18107
0 loops=1)
-> Finalize HashAggregate (cost=0.00..434.33 rows=4 width=14) (actual
time=0.063..0.068 rows=5 loops=1)
Group Key: share0_ref9.i_category
-> Redistribute Motion 3:3 (slice11; segments: 3) (cost=0.00..434.33
rows=4 width=14) (actual time=0.004..0.014 rows=15 loops=1)
Hash Key: share0_ref9.i_category
-> Streaming Partial HashAggregate (cost=0.00..434.33 rows=4
width=14) (actual time=2353.708..2353.731 rows=11 loops=1)
Group Key: share0_ref9.i_category
-> Shared Scan (share slice:id 11:0) (cost=0.00..431.80
rows=20780 width=16) (actual time=1901.829..1966.270 rows=18107
0 loops=1)
-> Result (cost=0.00..431.55 rows=1 width=60) (actual time=0.006..0.007
rows=1 loops=1)
-> Redistribute Motion 1:3 (slice12) (cost=0.00..431.55 rows=1 width=8)
(actual time=0.002..0.003 rows=1 loops=1)
-> Finalize Aggregate (cost=0.00..431.55 rows=1 width=8) (actual
time=2370.830..2370.831 rows=1 loops=1)
-> Gather Motion 3:1 (slice13; segments: 3)
(cost=0.00..431.55 rows=1 width=8) (actual time=2130.668..2370.771 rows=3
loops=1)
-> Partial Aggregate (cost=0.00..431.55 rows=1 width=8)
(actual time=2247.415..2247.418 rows=1 loops=1)
-> Shared Scan (share slice:id 13:0)
(cost=0.00..431.50 rows=20780 width=10) (actual time=1903.287..1965.59
6 rows=181070 loops=1)
Planning Time: 676.409 ms
(slice0) Executor memory: 4155K bytes.
(slice1) Executor memory: 238K bytes avg x 3x(0) workers, 293K bytes
max (seg0). Work_mem: 172K bytes max.
(slice2) Executor memory: 49625K bytes avg x 3x(0) workers, 50588K
bytes max (seg0). Work_mem: 9642K bytes max.
(slice3) Executor memory: 15830K bytes avg x 3x(0) workers, 15830K
bytes max (seg0).
(slice4) Executor memory: 88K bytes avg x 3x(0) workers, 88K bytes max
(seg0).
(slice5) Executor memory: 91K bytes avg x 3x(0) workers, 91K bytes max
(seg0).
(slice6) Executor memory: 87K bytes avg x 3x(0) workers, 87K bytes max
(seg0).
(slice7) Executor memory: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice8) Executor memory: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice9) Executor memory: 116K bytes avg x 3x(0) workers, 116K bytes
max (seg0).
(slice10) Executor memory: 136K bytes avg x 3x(0) workers, 136K bytes
max (seg0). Work_mem: 96K bytes max.
(slice11) Executor memory: 127K bytes avg x 3x(0) workers, 127K bytes
max (seg0). Work_mem: 24K bytes max.
(slice12) Executor memory: 20K bytes (entry db).
(slice13) Executor memory: 122K bytes avg x 3x(0) workers, 122K bytes
max (seg0).
Memory used: 128000kB
Optimizer: GPORCA
Execution Time: 8651.960 ms
(123 rows)
```
--
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: commits-unsubscr...@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@cloudberry.apache.org
For additional commands, e-mail: commits-h...@cloudberry.apache.org
