avamingli commented on PR #1261:
URL: https://github.com/apache/cloudberry/pull/1261#issuecomment-3128056412
TPS-DS q12, details:
```sql
-- q12 old
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=10436.303..10436.303 rows=100 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=10436.303..10436.303 rows=100 loops=1)
Merge Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(web_sales.ws_ext_sales_price)) * '100'::numeric) /
sum((sum(web_sales.ws_ext_sales_price))) OVER (?)))
-> Limit (actual time=10436.303..10436.303 rows=100 loops=1)
-> Sort (actual time=10436.303..10436.303 rows=100 loops=1)
Sort Key: item.i_category, item.i_class,
item.i_item_id, item.i_item_desc, ((((sum(web_sales.ws_ext_sales_price)) *
'100'::numeric) / sum((sum(web_sales.ws_ext_sales_price))) OVER (?)))
Sort Method: top-N heapsort Memory: 209kB
-> WindowAgg (actual time=10420.303..10428.303
rows=3460 loops=1)
Partition By: item.i_class
-> Sort (actual time=10420.303..10424.303
rows=3460 loops=1)
Sort Key: item.i_class
Sort Method: quicksort Memory: 2607kB
-> Redistribute Motion 3:3 (slice2;
segments: 3) (actual time=10288.299..10420.303 rows=3460 loops=1)
Hash Key: item.i_class
-> GroupAggregate (actual
time=10332.300..10408.302 rows=3152 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=10332.300..10344.301 rows=34669 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: 29848kB
-> Redistribute Motion
3:3 (slice3; segments: 3) (actual time=84.002..10116.294 rows=34669 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=60.002..10020.291 rows=34690 loops=1)
Hash Cond:
(web_sales.ws_item_sk = item.i_item_sk)
Extra Text:
(seg0) Hash chain length 1.0 avg, 3 max, using 6099 of 262144 buckets.
-> Hash Join
(actual time=20.001..9580.278 rows=115483 loops=1)
Hash
Cond: (web_sales.ws_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 web_sales (actual time=8.000..7048.205 rows=12081030 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=4.000..8.000 rows=13 loops=1)
Filter: ((d_date >= '1998-02-03'::date) AND (d_date <= '1998-03-05
00:00:00'::timestamp without time zone))
-> Hash
(actual time=36.001..36.001 rows=6173 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 3134kB
-> Seq
Scan on item (actual time=12.000..28.001 rows=6173 loops=1)
Filter: ((i_category)::text = ANY ('{Sports,Men,Jewelry}'::text[]))
Planning Time: 2.614 ms
(slice0) Executor memory: 189K bytes.
(slice1) Executor memory: 988K bytes avg x 3x(0) workers, 1111K bytes
max (seg2). Work_mem: 783K bytes max.
(slice2) Executor memory: 9205K bytes avg x 3x(0) workers, 9404K bytes
max (seg2). Work_mem: 7814K bytes max.
(slice3) Executor memory: 41241K bytes avg x 3x(0) workers, 41241K
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: 10439.077 ms
(46 rows)
-- q12 new
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=4588.139..4588.139 rows=100 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=4588.139..4588.139 rows=100 loops=1)
Merge Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(web_sales.ws_ext_sales_price)) * '100'::numeric) /
sum((sum(web_sales.ws_ext_sales_price))) OVER (?)))
-> Limit (actual time=4584.139..4584.139 rows=100 loops=1)
-> Sort (actual time=4584.139..4584.139 rows=100 loops=1)
Sort Key: item.i_category, item.i_class,
item.i_item_id, item.i_item_desc, ((((sum(web_sales.ws_ext_sales_price)) *
'100'::numeric) / sum((sum(web_sales.ws_ext_sales_price))) OVER (?)))
Sort Method: top-N heapsort Memory: 211kB
-> WindowAgg (actual time=4572.138..4580.138 rows=3460
loops=1)
Partition By: item.i_class
-> Sort (actual time=4568.138..4572.138
rows=3460 loops=1)
Sort Key: item.i_class
Sort Method: quicksort Memory: 2607kB
-> Redistribute Motion 3:3 (slice2;
segments: 3) (actual time=4456.135..4564.138 rows=3460 loops=1)
Hash Key: item.i_class
-> GroupAggregate (actual
time=4452.135..4516.136 rows=3152 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=4452.135..4464.135 rows=34669 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: 29848kB
-> Redistribute Motion
3:3 (slice3; segments: 3) (actual time=72.002..4256.129 rows=34669 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=72.002..4184.126 rows=34690 loops=1)
Hash Cond:
(web_sales.ws_item_sk = item.i_item_sk)
Extra Text:
(seg0) Hash chain length 1.0 avg, 3 max, using 6099 of 262144 buckets.
->
Redistribute Motion 12:3 (slice4; segments: 12) (actual time=32.001..4056.123
rows=115483 loops=1)
Hash
Key: web_sales.ws_item_sk
->
Hash Join (actual time=48.001..3592.109 rows=28720 loops=1)
Hash Cond: (web_sales.ws_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 web_sales (actual time=20.001..2060.062 rows=3000000
loops=1)
-> Hash (actual time=12.000..12.000 rows=31 loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 8194kB
-> Broadcast Motion 3:12 (slice5; segments: 3) (actual time=0.000..12.000
rows=31 loops=1)
-> Seq Scan on date_dim (actual time=12.000..20.001 rows=13 loops=1)
Filter: ((d_date >= '1998-02-03'::date) AND (d_date <=
'1998-03-05 00:00:00'::timestamp without time zone))
-> Hash
(actual time=40.001..40.001 rows=6173 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 3134kB
-> Seq
Scan on item (actual time=12.000..36.001 rows=6173 loops=1)
Filter: ((i_category)::text = ANY ('{Sports,Men,Jewelry}'::text[]))
Planning Time: 2.708 ms
(slice0) Executor memory: 194K bytes.
(slice1) Executor memory: 987K bytes avg x 3x(0) workers, 1111K bytes
max (seg2). Work_mem: 783K bytes max.
(slice2) Executor memory: 9292K bytes avg x 3x(0) workers, 9491K bytes
max (seg2). Work_mem: 7814K bytes max.
(slice3) Executor memory: 23420K bytes avg x 3x(0) workers, 23420K
bytes max (seg1). Work_mem: 3134K bytes max.
(slice4) Executor memory: 17895K bytes avg x 12x(0) workers, 17895K
bytes max (seg0). 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: 4613.516 ms
(49 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]
