cloudberry
cloudberry copied to clipboard
apache
ORCA: Support two step window function
Fixes #ISSUE_Number
What does this PR do?
Type of Change
- [ ] Bug fix (non-breaking change)
- [ ] New feature (non-breaking change)
- [ ] Breaking change (fix or feature with breaking changes)
- [ ] Documentation update
Breaking Changes
Test Plan
- [ ] Unit tests added/updated
- [ ] Integration tests added/updated
- [ ] Passed
make installcheck - [ ] Passed
make -C src/test installcheck-cbdb-parallel
Impact
Performance:
User-facing changes:
Dependencies:
Checklist
- [ ] Followed contribution guide
- [ ] Added/updated documentation
- [ ] Reviewed code for security implications
- [ ] Requested review from cloudberry committers
Additional Context
CI Skip Instructions
Nice feature! Have we tested how much improvement there is in performance?
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
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.
These functions require access to the entire window frame to compute their results: • PERCENT_RANK() • CUME_DIST() • NTILE(n)
Where is the code to check those function ?
These functions require access to the entire window frame to compute their results: • PERCENT_RANK() • CUME_DIST() • NTILE(n)
Where is the code to check those function ?
// Input:
// +--CLogicalSelect (Global(one-step))
// |--CLogicalSequenceProject
// | |-- ANY(*)
// | +--CScalarProjectList -> those functions in the scalar
// +--CScalarCmp
Please add some test cases. for example PERCENT_RANK() plan fallback case. And other support window functions
Please add some test cases. for example
PERCENT_RANK()plan fallback case. And other support window functions
@yjhjstz updated