avamingli commented on PR #1261: URL: https://github.com/apache/cloudberry/pull/1261#issuecomment-3131175694
Q49 old
```sql
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=60021.352..60021.352 rows=78 loops=1)
-> Sort (actual time=60021.352..60021.352 rows=78 loops=1)
Sort Key: ('web'::text), web.return_rank, web.currency_rank
Sort Method: quicksort Memory: 31kB
-> HashAggregate (actual time=60021.352..60021.352 rows=78 loops=1)
Group Key: ('web'::text), web.item, web.return_ratio,
web.return_rank, web.currency_rank
Batches: 1 Memory Usage: 48kB
Extra Text: hash table(s): 1; chain length 2.3 avg, 4 max;
using 78 of 128 buckets; total 3 expansions.
-> Append (actual time=15556.351..60021.352 rows=78 loops=1)
-> Subquery Scan on web (actual
time=15556.351..15560.351 rows=28 loops=1)
Filter: ((web.return_rank <= 10) OR
(web.currency_rank <= 10))
Rows Removed by Filter: 884
-> WindowAgg (actual time=15556.351..15560.351
rows=912 loops=1)
Order By: in_web.return_ratio
-> Sort (actual time=15556.351..15556.351
rows=912 loops=1)
Sort Key: in_web.return_ratio
Sort Method: quicksort Memory: 103kB
-> WindowAgg (actual
time=15552.351..15556.351 rows=912 loops=1)
Order By: in_web.currency_ratio
-> Gather Motion 3:1 (slice1;
segments: 3) (actual time=15552.351..15552.351 rows=912 loops=1)
Merge Key:
in_web.currency_ratio
-> Sort (actual
time=14908.337..14908.337 rows=323 loops=1)
Sort Key:
in_web.currency_ratio
Sort Method:
quicksort Memory: 149kB
-> Subquery Scan
on in_web (actual time=14904.337..14904.337 rows=323 loops=1)
->
GroupAggregate (actual time=14904.337..14904.337 rows=323 loops=1)
Group
Key: ws.ws_item_sk
->
Sort (actual time=14904.337..14904.337 rows=329 loops=1)
Sort Key: ws.ws_item_sk
Sort Method: quicksort Memory: 153kB
-> Hash Join (actual time=784.018..14896.336 rows=329 loops=1)
Hash Cond: ((ws.ws_order_number = wr.wr_order_number) AND (ws.ws_item_sk =
wr.wr_item_sk))
Extra Text: (seg2) Hash chain length 1.0 avg, 3 max, using 9637 of 262144
buckets.
-> Hash Join (actual time=28.001..14336.324 rows=160062 loops=1)
Hash Cond: (ws.ws_sold_date_sk = date_dim.d_date_sk)
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 31 of
524288 buckets.
-> Seq Scan on web_sales ws (actual time=24.001..13304.300
rows=4698728 loops=1)
Filter: ((ws_net_profit > '1'::numeric) AND (ws_net_paid >
'0'::numeric) AND (ws_quantity > 0))
-> Hash (actual time=0.000..0.000 rows=31 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 4098kB
-> Broadcast Motion 3:3 (slice2; segments: 3) (actual
time=0.000..0.000 rows=31 loops=1)
-> Seq Scan on date_dim (actual time=8.000..12.000
rows=13 loops=1)
Filter: ((d_year = 2000) AND (d_moy = 12))
-> Hash (actual time=760.017..760.017 rows=10091 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 2640kB
-> Seq Scan on web_returns wr (actual time=12.000..752.017
rows=10091 loops=1)
Filter: (wr_return_amt > '10000'::numeric)
-> Subquery Scan on catalog (actual
time=18048.407..18056.407 rows=29 loops=1)
Filter: ((catalog.return_rank <= 10) OR
(catalog.currency_rank <= 10))
Rows Removed by Filter: 1728
-> WindowAgg (actual time=18048.407..18056.407
rows=1757 loops=1)
Order By: in_cat.return_ratio
-> Sort (actual time=18048.407..18048.407
rows=1757 loops=1)
Sort Key: in_cat.return_ratio
Sort Method: quicksort Memory: 200kB
-> WindowAgg (actual
time=18036.406..18040.407 rows=1757 loops=1)
Order By: in_cat.currency_ratio
-> Gather Motion 3:1 (slice3;
segments: 3) (actual time=18036.406..18036.406 rows=1757 loops=1)
Merge Key:
in_cat.currency_ratio
-> Sort (actual
time=33596.758..33596.758 rows=606 loops=1)
Sort Key:
in_cat.currency_ratio
Sort Method:
quicksort Memory: 218kB
-> Subquery Scan
on in_cat (actual time=33592.758..33596.758 rows=606 loops=1)
->
GroupAggregate (actual time=33592.758..33596.758 rows=606 loops=1)
Group
Key: cs.cs_item_sk
->
Sort (actual time=33592.758..33592.758 rows=627 loops=1)
Sort Key: cs.cs_item_sk
Sort Method: quicksort Memory: 229kB
-> Hash Join (actual time=7564.171..33592.758 rows=627 loops=1)
Hash Cond: ((cs.cs_order_number = cr.cr_order_number) AND (cs.cs_item_sk =
cr.cr_item_sk))
Extra Text: (seg0) Hash chain length 1.0 avg, 4 max, using 19910 of
262144 buckets.
-> Hash Join (actual time=6028.136..31924.720 rows=316758 loops=1)
Hash Cond: (cs.cs_sold_date_sk = date_dim_1.d_date_sk)
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 31 of
524288 buckets.
-> Seq Scan on catalog_sales cs (actual time=28.001..26060.588
rows=9329378 loops=1)
Filter: ((cs_net_profit > '1'::numeric) AND (cs_net_paid >
'0'::numeric) AND (cs_quantity > 0))
-> Hash (actual time=0.000..0.000 rows=31 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 4098kB
-> Broadcast Motion 3:3 (slice4; segments: 3) (actual
time=0.000..0.000 rows=31 loops=1)
-> Seq Scan on date_dim date_dim_1 (actual
time=8.000..12.000 rows=13 loops=1)
Filter: ((d_year = 2000) AND (d_moy = 12))
-> Hash (actual time=1524.034..1524.034 rows=20673 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 3260kB
-> Seq Scan on catalog_returns cr (actual time=20.000..1512.034
rows=20673 loops=1)
Filter: (cr_return_amount > '10000'::numeric)
-> Subquery Scan on store (actual
time=26400.593..26404.593 rows=21 loops=1)
Filter: ((store.return_rank <= 10) OR
(store.currency_rank <= 10))
Rows Removed by Filter: 612
-> WindowAgg (actual time=26400.593..26404.593
rows=633 loops=1)
Order By: in_store.return_ratio
-> Sort (actual time=26400.593..26400.593
rows=633 loops=1)
Sort Key: in_store.return_ratio
Sort Method: quicksort Memory: 79kB
-> WindowAgg (actual
time=26392.593..26400.593 rows=633 loops=1)
Order By:
in_store.currency_ratio
-> Gather Motion 3:1 (slice5;
segments: 3) (actual time=26392.593..26392.593 rows=633 loops=1)
Merge Key:
in_store.currency_ratio
-> Sort (actual
time=52169.175..52169.175 rows=226 loops=1)
Sort Key:
in_store.currency_ratio
Sort Method:
quicksort Memory: 125kB
-> Subquery Scan
on in_store (actual time=52169.175..52169.175 rows=226 loops=1)
->
GroupAggregate (actual time=52169.175..52169.175 rows=226 loops=1)
Group
Key: sts.ss_item_sk
->
Sort (actual time=52169.175..52169.175 rows=231 loops=1)
Sort Key: sts.ss_item_sk
Sort Method: quicksort Memory: 129kB
-> Hash Join (actual time=3140.071..52165.175 rows=231 loops=1)
Hash Cond: ((sts.ss_ticket_number = sr.sr_ticket_number) AND
(sts.ss_item_sk = sr.sr_item_sk))
Extra Text: (seg0) Hash chain length 1.0 avg, 3 max, using 7664 of 262144
buckets.
-> Hash Join (actual time=28.001..48901.102 rows=390494 loops=1)
Hash Cond: (sts.ss_sold_date_sk = date_dim_2.d_date_sk)
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 31 of
524288 buckets.
-> Seq Scan on store_sales sts (actual time=28.001..51581.161
rows=11508189 loops=1)
Filter: ((ss_net_profit > '1'::numeric) AND (ss_net_paid >
'0'::numeric) AND (ss_quantity > 0))
-> Hash (actual time=0.000..0.000 rows=31 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 4098kB
-> Broadcast Motion 3:3 (slice6; segments: 3) (actual
time=0.000..0.000 rows=31 loops=1)
-> Seq Scan on date_dim date_dim_2 (actual
time=12.000..16.000 rows=13 loops=1)
Filter: ((d_year = 2000) AND (d_moy = 12))
-> Hash (actual time=4744.107..4744.107 rows=7997 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 2517kB
-> Seq Scan on store_returns sr (actual time=28.001..4736.107
rows=7997 loops=1)
Filter: (sr_return_amt > '10000'::numeric)
Planning Time: 8.318 ms
* (slice0) Executor memory: 560K bytes. Work_mem: 110K bytes max, 48K
bytes wanted.
(slice1) Executor memory: 38711K bytes avg x 3x(0) workers, 38711K
bytes max (seg1). Work_mem: 4098K bytes max.
(slice2) Executor memory: 9556K bytes avg x 3x(0) workers, 9556K bytes
max (seg0).
(slice3) Executor memory: 39388K bytes avg x 3x(0) workers, 39388K
bytes max (seg2). Work_mem: 4098K bytes max.
(slice4) Executor memory: 9556K bytes avg x 3x(0) workers, 9556K bytes
max (seg0).
(slice5) Executor memory: 38564K bytes avg x 3x(0) workers, 38564K
bytes max (seg1). Work_mem: 4098K bytes max.
(slice6) Executor memory: 9556K bytes avg x 3x(0) workers, 9556K bytes
max (seg0).
Memory used: 1048576kB
Memory wanted: 4304kB
Optimizer: Postgres query optimizer
Execution Time: 60039.451 ms
(136 rows)
```
Q49 new
```sql
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=28468.665..28468.665 rows=78 loops=1)
-> Sort (actual time=28468.665..28468.665 rows=78 loops=1)
Sort Key: ('web'::text), web.return_rank, web.currency_rank
Sort Method: quicksort Memory: 31kB
-> HashAggregate (actual time=28468.665..28468.665 rows=78 loops=1)
Group Key: ('web'::text), web.item, web.return_ratio,
web.return_rank, web.currency_rank
Batches: 1 Memory Usage: 48kB
Extra Text: hash table(s): 1; chain length 2.3 avg, 4 max;
using 78 of 128 buckets; total 3 expansions.
-> Append (actual time=13776.322..28468.665 rows=78 loops=1)
-> Subquery Scan on web (actual
time=13776.322..13780.322 rows=28 loops=1)
Filter: ((web.return_rank <= 10) OR
(web.currency_rank <= 10))
Rows Removed by Filter: 884
-> WindowAgg (actual time=13776.322..13780.322
rows=912 loops=1)
Order By: in_web.return_ratio
-> Sort (actual time=13776.322..13776.322
rows=912 loops=1)
Sort Key: in_web.return_ratio
Sort Method: quicksort Memory: 103kB
-> WindowAgg (actual
time=13772.322..13776.322 rows=912 loops=1)
Order By: in_web.currency_ratio
-> Gather Motion 12:1
(slice1; segments: 12) (actual time=13772.322..13772.322 rows=912 loops=1)
Merge Key:
in_web.currency_ratio
-> Sort (actual
time=13732.321..13732.321 rows=72 loops=1)
Sort Key:
in_web.currency_ratio
Sort Method:
quicksort Memory: 367kB
-> Subquery Scan
on in_web (actual time=13732.321..13732.321 rows=72 loops=1)
->
GroupAggregate (actual time=13732.321..13732.321 rows=72 loops=1)
Group
Key: ws.ws_item_sk
->
Sort (actual time=13732.321..13732.321 rows=72 loops=1)
Sort Key: ws.ws_item_sk
Sort Method: quicksort Memory: 375kB
-> Redistribute Motion 12:12 (slice2; segments: 12) (actual
time=13688.320..13732.321 rows=72 loops=1)
Hash Key: ws.ws_item_sk
Hash Module: 3
-> Parallel Hash Join (actual time=12880.301..13076.306 rows=74 loops=1)
Hash Cond: ((wr.wr_order_number = ws.ws_order_number) AND
(wr.wr_item_sk = ws.ws_item_sk))
-> Parallel Seq Scan on web_returns wr (actual time=4.000..220.005
rows=2432 loops=1)
Filter: (wr_return_amt > '10000'::numeric)
-> Parallel Hash (actual time=13484.316..13484.316 rows=40145
loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 17120kB
-> Hash Join (actual time=144.003..12856.301 rows=40145
loops=1)
Hash Cond: (ws.ws_sold_date_sk = date_dim.d_date_sk)
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max,
using 31 of 524288 buckets.
-> Parallel Seq Scan on web_sales ws (actual
time=196.005..6116.143 rows=1167817 loops=1)
Filter: ((ws_net_profit > '1'::numeric) AND
(ws_net_paid > '0'::numeric) AND (ws_quantity > 0))
-> Hash (actual time=0.000..0.000 rows=31 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 4098kB
-> Broadcast Motion 3:12 (slice3; segments: 3)
(actual time=0.000..0.000 rows=31 loops=1)
-> Seq Scan on date_dim (actual
time=4.000..4.000 rows=13 loops=1)
Filter: ((d_year = 2000) AND (d_moy =
12))
-> Subquery Scan on catalog (actual
time=6868.160..6888.161 rows=29 loops=1)
Filter: ((catalog.return_rank <= 10) OR
(catalog.currency_rank <= 10))
Rows Removed by Filter: 1728
-> WindowAgg (actual time=6868.160..6888.161
rows=1757 loops=1)
Order By: in_cat.return_ratio
-> Sort (actual time=6868.160..6868.160
rows=1757 loops=1)
Sort Key: in_cat.return_ratio
Sort Method: quicksort Memory: 200kB
-> WindowAgg (actual
time=6852.160..6864.160 rows=1757 loops=1)
Order By: in_cat.currency_ratio
-> Gather Motion 12:1
(slice4; segments: 12) (actual time=6852.160..6860.160 rows=1757 loops=1)
Merge Key:
in_cat.currency_ratio
-> Sort (actual
time=20612.482..20612.482 rows=166 loops=1)
Sort Key:
in_cat.currency_ratio
Sort Method:
quicksort Memory: 438kB
-> Subquery Scan
on in_cat (actual time=20612.482..20612.482 rows=166 loops=1)
->
GroupAggregate (actual time=20612.482..20612.482 rows=166 loops=1)
Group
Key: cs.cs_item_sk
->
Sort (actual time=20612.482..20612.482 rows=173 loops=1)
Sort Key: cs.cs_item_sk
Sort Method: quicksort Memory: 450kB
-> Redistribute Motion 12:12 (slice5; segments: 12) (actual
time=19332.452..20612.482 rows=173 loops=1)
Hash Key: cs.cs_item_sk
Hash Module: 3
-> Parallel Hash Join (actual time=19212.449..19300.451 rows=109 loops=1)
Hash Cond: ((cr.cr_order_number = cs.cs_order_number) AND
(cr.cr_item_sk = cs.cs_item_sk))
-> Parallel Seq Scan on catalog_returns cr (actual
time=8.000..372.009 rows=5336 loops=1)
Filter: (cr_return_amount > '10000'::numeric)
-> Parallel Hash (actual time=19604.458..19604.458 rows=82094
loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 25824kB
-> Hash Join (actual time=15072.353..18756.439 rows=82094
loops=1)
Hash Cond: (cs.cs_sold_date_sk = date_dim_1.d_date_sk)
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max,
using 31 of 524288 buckets.
-> Parallel Seq Scan on catalog_sales cs (actual
time=52.001..10884.255 rows=2339330 loops=1)
Filter: ((cs_net_profit > '1'::numeric) AND
(cs_net_paid > '0'::numeric) AND (cs_quantity > 0))
-> Hash (actual time=0.000..0.000 rows=31 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 4098kB
-> Broadcast Motion 3:12 (slice6; segments: 3)
(actual time=0.000..0.000 rows=31 loops=1)
-> Seq Scan on date_dim date_dim_1 (actual
time=4.000..4.000 rows=13 loops=1)
Filter: ((d_year = 2000) AND (d_moy =
12))
-> Subquery Scan on store (actual
time=7796.182..7796.182 rows=21 loops=1)
Filter: ((store.return_rank <= 10) OR
(store.currency_rank <= 10))
Rows Removed by Filter: 612
-> WindowAgg (actual time=7796.182..7796.182
rows=633 loops=1)
Order By: in_store.return_ratio
-> Sort (actual time=7796.182..7796.182
rows=633 loops=1)
Sort Key: in_store.return_ratio
Sort Method: quicksort Memory: 79kB
-> WindowAgg (actual
time=7796.182..7796.182 rows=633 loops=1)
Order By:
in_store.currency_ratio
-> Gather Motion 12:1
(slice7; segments: 12) (actual time=7796.182..7796.182 rows=633 loops=1)
Merge Key:
in_store.currency_ratio
-> Sort (actual
time=28448.665..28448.665 rows=60 loops=1)
Sort Key:
in_store.currency_ratio
Sort Method:
quicksort Memory: 346kB
-> Subquery Scan
on in_store (actual time=28448.665..28448.665 rows=60 loops=1)
->
GroupAggregate (actual time=28448.665..28448.665 rows=60 loops=1)
Group
Key: sts.ss_item_sk
->
Sort (actual time=28448.665..28448.665 rows=60 loops=1)
Sort Key: sts.ss_item_sk
Sort Method: quicksort Memory: 351kB
-> Redistribute Motion 12:12 (slice8; segments: 12) (actual
time=25280.591..28448.665 rows=60 loops=1)
Hash Key: sts.ss_item_sk
Hash Module: 3
-> Parallel Hash Join (actual time=26736.625..27640.646 rows=67 loops=1)
Hash Cond: ((sr.sr_ticket_number = sts.ss_ticket_number) AND
(sr.sr_item_sk = sts.ss_item_sk))
-> Parallel Seq Scan on store_returns sr (actual
time=12.000..880.021 rows=1819 loops=1)
Filter: (sr_return_amt > '10000'::numeric)
-> Parallel Hash (actual time=26716.624..26716.624 rows=96830
loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 29760kB
-> Hash Join (actual time=136.003..23280.544 rows=96830
loops=1)
Hash Cond: (sts.ss_sold_date_sk = date_dim_2.d_date_sk)
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max,
using 31 of 524288 buckets.
-> Parallel Seq Scan on store_sales sts (actual
time=120.003..18128.424 rows=2865268 loops=1)
Filter: ((ss_net_profit > '1'::numeric) AND
(ss_net_paid > '0'::numeric) AND (ss_quantity > 0))
-> Hash (actual time=0.000..0.000 rows=31 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 4098kB
-> Broadcast Motion 3:12 (slice9; segments: 3)
(actual time=0.000..0.000 rows=31 loops=1)
-> Seq Scan on date_dim date_dim_2 (actual
time=8.000..8.000 rows=13 loops=1)
Filter: ((d_year = 2000) AND (d_moy =
12))
Planning Time: 8.620 ms
* (slice0) Executor memory: 673K bytes. Work_mem: 110K bytes max, 48K
bytes wanted.
(slice1) Executor memory: 234K bytes avg x 12x(0) workers, 275K bytes
max (seg2). Work_mem: 7K bytes max.
(slice2) Executor memory: 31909K bytes avg x 12x(0) workers, 31909K
bytes max (seg1). Work_mem: 4098K bytes max.
(slice3) Executor memory: 9557K bytes avg x 3x(0) workers, 9557K bytes
max (seg0).
(slice4) Executor memory: 279K bytes avg x 12x(0) workers, 280K bytes
max (seg2). Work_mem: 11K bytes max.
(slice5) Executor memory: 31913K bytes avg x 12x(0) workers, 31913K
bytes max (seg2). Work_mem: 4098K bytes max.
(slice6) Executor memory: 9557K bytes avg x 3x(0) workers, 9557K bytes
max (seg0).
(slice7) Executor memory: 273K bytes avg x 12x(0) workers, 274K bytes
max (seg1). Work_mem: 5K bytes max.
(slice8) Executor memory: 31890K bytes avg x 12x(0) workers, 31890K
bytes max (seg1). Work_mem: 4098K bytes max.
(slice9) Executor memory: 9557K bytes avg x 3x(0) workers, 9557K bytes
max (seg0).
Memory used: 1048576kB
Memory wanted: 4604kB
Optimizer: Postgres query optimizer
Execution Time: 28603.506 ms
(145 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: [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]
