avamingli commented on PR #1261:
URL: https://github.com/apache/cloudberry/pull/1261#issuecomment-3135181647
TPC-DS Q98
```sql
Q98 old
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual time=40068.731..40084.732
rows=9145 loops=1)
Merge Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(store_sales.ss_ext_sales_price)) * '100'::numeric) /
sum((sum(store_sales.ss_ext_sales_price))) OVER (?)))
-> Sort (actual time=40064.731..40064.731 rows=3223 loops=1)
Sort Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(store_sales.ss_ext_sales_price)) * '100'::numeric) /
sum((sum(store_sales.ss_ext_sales_price))) OVER (?)))
Sort Method: quicksort Memory: 2781kB
-> WindowAgg (actual time=40036.731..40048.731 rows=3223 loops=1)
Partition By: item.i_class
-> Sort (actual time=40036.731..40036.731 rows=3223 loops=1)
Sort Key: item.i_class
Sort Method: quicksort Memory: 2556kB
-> Redistribute Motion 3:3 (slice2; segments: 3)
(actual time=39732.725..40032.731 rows=3223 loops=1)
Hash Key: item.i_class
-> GroupAggregate (actual
time=39716.725..40024.731 rows=3121 loops=1)
Group Key: item.i_item_id,
item.i_item_desc, item.i_category, item.i_class, item.i_current_price
-> Sort (actual time=39716.725..39764.726
rows=135884 loops=1)
Sort Key: item.i_item_id,
item.i_item_desc, item.i_category, item.i_class, item.i_current_price
Sort Method: quicksort Memory:
108627kB
-> Redistribute Motion 3:3 (slice3;
segments: 3) (actual time=112.002..38672.706 rows=135884 loops=1)
Hash Key: item.i_category,
item.i_class, item.i_item_id, item.i_item_desc, item.i_current_price
-> Hash Join (actual
time=80.001..38264.698 rows=135710 loops=1)
Hash Cond:
(store_sales.ss_item_sk = item.i_item_sk)
Extra Text: (seg1) Hash
chain length 1.0 avg, 2 max, using 6147 of 262144 buckets.
-> Hash Join (actual
time=36.001..37884.691 rows=452779 loops=1)
Hash Cond:
(store_sales.ss_sold_date_sk = date_dim.d_date_sk)
Extra Text: (seg1)
Hash chain length 1.0 avg, 1 max, using 31 of 1048576 buckets.
-> Seq Scan on
store_sales (actual time=20.000..28016.511 rows=48321834 loops=1)
-> Hash (actual
time=0.000..0.000 rows=31 loops=1)
Buckets:
1048576 Batches: 1 Memory Usage: 8194kB
-> Broadcast
Motion 3:3 (slice4; segments: 3) (actual time=0.000..0.000 rows=31 loops=1)
-> Seq
Scan on date_dim (actual time=8.000..12.000 rows=14 loops=1)
Filter: ((d_date >= '1998-03-26'::date) AND (d_date <= '1998-04-25
00:00:00'::timestamp without time zone))
-> Hash (actual
time=40.001..40.001 rows=6218 loops=1)
Buckets: 262144
Batches: 1 Memory Usage: 3133kB
-> Seq Scan on
item (actual time=16.000..40.001 rows=6218 loops=1)
Filter:
((i_category)::text = ANY ('{Home,Women,Men}'::text[]))
Planning Time: 3.490 ms
(slice0) Executor memory: 176K bytes.
(slice1) Executor memory: 1543K bytes avg x 3x(0) workers, 1649K bytes
max (seg0). Work_mem: 814K bytes max.
(slice2) Executor memory: 33250K bytes avg x 3x(0) workers, 35754K
bytes max (seg2). Work_mem: 29469K bytes max.
(slice3) Executor memory: 41218K bytes avg x 3x(0) workers, 41228K
bytes max (seg1). Work_mem: 8194K bytes max.
(slice4) Executor memory: 6413K bytes avg x 3x(0) workers, 6413K bytes
max (seg0).
Memory used: 1048576kB
Optimizer: Postgres query optimizer
Execution Time: 40114.212 ms
(44 rows)
Q98 new
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual time=17012.314..17028.314
rows=9145 loops=1)
Merge Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(store_sales.ss_ext_sales_price)) * '100'::numeric) /
sum((sum(store_sales.ss_ext_sales_price))) OVER (?)))
-> Sort (actual time=17008.314..17008.314 rows=3223 loops=1)
Sort Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(store_sales.ss_ext_sales_price)) * '100'::numeric) /
sum((sum(store_sales.ss_ext_sales_price))) OVER (?)))
Sort Method: quicksort Memory: 2781kB
-> WindowAgg (actual time=16980.313..16988.313 rows=3223 loops=1)
Partition By: item.i_class
-> Sort (actual time=16980.313..16980.313 rows=3223 loops=1)
Sort Key: item.i_class
Sort Method: quicksort Memory: 2556kB
-> Redistribute Motion 3:3 (slice2; segments: 3)
(actual time=16520.305..16980.313 rows=3223 loops=1)
Hash Key: item.i_class
-> GroupAggregate (actual
time=16528.305..16744.309 rows=3121 loops=1)
Group Key: item.i_item_id,
item.i_item_desc, item.i_category, item.i_class, item.i_current_price
-> Sort (actual time=16528.305..16564.305
rows=135884 loops=1)
Sort Key: item.i_item_id,
item.i_item_desc, item.i_category, item.i_class, item.i_current_price
Sort Method: quicksort Memory:
108627kB
-> Redistribute Motion 3:3 (slice3;
segments: 3) (actual time=104.002..15684.289 rows=135884 loops=1)
Hash Key: item.i_category,
item.i_class, item.i_item_id, item.i_item_desc, item.i_current_price
-> Hash Join (actual
time=208.004..15252.281 rows=135710 loops=1)
Hash Cond:
(store_sales.ss_item_sk = item.i_item_sk)
Extra Text: (seg1) Hash
chain length 1.0 avg, 2 max, using 6147 of 262144 buckets.
-> Redistribute Motion
12:3 (slice4; segments: 12) (actual time=0.000..14548.268 rows=452779 loops=1)
Hash Key:
store_sales.ss_item_sk
-> Hash Join
(actual time=20.000..12688.234 rows=113983 loops=1)
Hash Cond:
(store_sales.ss_sold_date_sk = date_dim.d_date_sk)
Extra Text:
(seg1) Hash chain length 1.0 avg, 1 max, using 31 of 1048576 buckets.
-> Parallel
Seq Scan on store_sales (actual time=36.001..8408.155 rows=12100000 loops=1)
-> Hash
(actual time=0.000..0.000 rows=31 loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 8194kB
->
Broadcast Motion 3:12 (slice5; segments: 3) (actual time=0.000..0.000 rows=31
loops=1)
-> Seq Scan on date_dim (actual time=8.000..12.000 rows=14 loops=1)
Filter: ((d_date >= '1998-03-26'::date) AND (d_date <= '1998-04-25
00:00:00'::timestamp without time zone))
-> Hash (actual
time=208.004..208.004 rows=6218 loops=1)
Buckets: 262144
Batches: 1 Memory Usage: 3133kB
-> Seq Scan on
item (actual time=16.000..112.002 rows=6218 loops=1)
Filter:
((i_category)::text = ANY ('{Home,Women,Men}'::text[]))
Planning Time: 2.730 ms
(slice0) Executor memory: 181K bytes.
(slice1) Executor memory: 1543K bytes avg x 3x(0) workers, 1649K bytes
max (seg0). Work_mem: 814K bytes max.
(slice2) Executor memory: 33250K bytes avg x 3x(0) workers, 35754K
bytes max (seg2). Work_mem: 29469K bytes max.
(slice3) Executor memory: 23409K bytes avg x 3x(0) workers, 23420K
bytes max (seg0). Work_mem: 3133K bytes max.
(slice4) Executor memory: 17881K bytes avg x 12x(0) workers, 17882K
bytes max (seg2). Work_mem: 8194K bytes max.
(slice5) Executor memory: 6413K bytes avg x 3x(0) workers, 6413K bytes
max (seg0).
Memory used: 1048576kB
Optimizer: Postgres query optimizer
Execution Time: 17052.781 ms
(47 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]
