avamingli commented on PR #1261:
URL: https://github.com/apache/cloudberry/pull/1261#issuecomment-3128065377
TPS-DS Q20
```sql
-- q20 old
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=21484.455..21484.455 rows=100 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=21484.455..21484.455 rows=100 loops=1)
Merge Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(catalog_sales.cs_ext_sales_price)) * '100'::numeric)
/ sum((sum(catalog_sales.cs_ext_sales_price))) OVER (?)))
-> Limit (actual time=21484.455..21484.455 rows=100 loops=1)
-> Sort (actual time=21484.455..21484.455 rows=100 loops=1)
Sort Key: item.i_category, item.i_class,
item.i_item_id, item.i_item_desc, ((((sum(catalog_sales.cs_ext_sales_price)) *
'100'::numeric) / sum((sum(catalog_sales.cs_ext_sales_price))) OVER (?)))
Sort Method: top-N heapsort Memory: 221kB
-> WindowAgg (actual time=21452.455..21472.455
rows=3865 loops=1)
Partition By: item.i_class
-> Sort (actual time=21452.455..21452.455
rows=3865 loops=1)
Sort Key: item.i_class
Sort Method: quicksort Memory: 2629kB
-> Redistribute Motion 3:3 (slice2;
segments: 3) (actual time=21096.447..21448.455 rows=3865 loops=1)
Hash Key: item.i_class
-> GroupAggregate (actual
time=21268.451..21440.455 rows=3161 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=21268.451..21292.451 rows=71690 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: 61365kB
-> Redistribute Motion
3:3 (slice3; segments: 3) (actual time=3824.081..20680.438 rows=71690 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=3816.081..20460.434 rows=72694 loops=1)
Hash Cond:
(catalog_sales.cs_item_sk = item.i_item_sk)
Extra Text:
(seg1) Hash chain length 1.0 avg, 3 max, using 6167 of 262144 buckets.
-> Hash Join
(actual time=3732.079..20264.430 rows=236599 loops=1)
Hash
Cond: (catalog_sales.cs_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 catalog_sales (actual time=12.000..14928.316 rows=24158497 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=12 loops=1)
Filter: ((d_date >= '1999-05-15'::date) AND (d_date <= '1999-06-14
00:00:00'::timestamp without time zone))
-> Hash
(actual time=80.002..80.002 rows=6252 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 3138kB
-> Seq
Scan on item (actual time=24.001..64.001 rows=6252 loops=1)
Filter: ((i_category)::text = ANY ('{Books,Music,Home}'::text[]))
Planning Time: 4.175 ms
(slice0) Executor memory: 189K bytes.
(slice1) Executor memory: 1044K bytes avg x 3x(0) workers, 1204K bytes
max (seg0). Work_mem: 879K bytes max.
(slice2) Executor memory: 18851K bytes avg x 3x(0) workers, 19092K
bytes max (seg1). Work_mem: 15966K bytes max.
(slice3) Executor memory: 41230K bytes avg x 3x(0) workers, 41240K
bytes max (seg0). 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: 21487.075 ms
(46 rows)
-- q20 new
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=8704.184..8704.184 rows=100 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=8704.184..8704.184 rows=100 loops=1)
Merge Key: item.i_category, item.i_class, item.i_item_id,
item.i_item_desc, ((((sum(catalog_sales.cs_ext_sales_price)) * '100'::numeric)
/ sum((sum(catalog_sales.cs_ext_sales_price))) OVER (?)))
-> Limit (actual time=8700.184..8700.184 rows=100 loops=1)
-> Sort (actual time=8700.184..8700.184 rows=100 loops=1)
Sort Key: item.i_category, item.i_class,
item.i_item_id, item.i_item_desc, ((((sum(catalog_sales.cs_ext_sales_price)) *
'100'::numeric) / sum((sum(catalog_sales.cs_ext_sales_price))) OVER (?)))
Sort Method: top-N heapsort Memory: 222kB
-> WindowAgg (actual time=8684.184..8696.184 rows=3865
loops=1)
Partition By: item.i_class
-> Sort (actual time=8680.184..8680.184
rows=3865 loops=1)
Sort Key: item.i_class
Sort Method: quicksort Memory: 2629kB
-> Redistribute Motion 3:3 (slice2;
segments: 3) (actual time=8420.178..8680.184 rows=3865 loops=1)
Hash Key: item.i_class
-> GroupAggregate (actual
time=8428.179..8564.181 rows=3161 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=8428.179..8432.179 rows=71690 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: 61365kB
-> Redistribute Motion
3:3 (slice3; segments: 3) (actual time=4128.087..8024.170 rows=71690 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=4116.087..7780.165 rows=72694 loops=1)
Hash Cond:
(catalog_sales.cs_item_sk = item.i_item_sk)
Extra Text:
(seg1) Hash chain length 1.0 avg, 3 max, using 6167 of 262144 buckets.
->
Redistribute Motion 12:3 (slice4; segments: 12) (actual
time=4068.086..7580.161 rows=236599 loops=1)
Hash
Key: catalog_sales.cs_item_sk
->
Hash Join (actual time=4112.087..5376.114 rows=48209 loops=1)
Hash Cond: (catalog_sales.cs_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 catalog_sales (actual time=28.001..3356.071
rows=6000000 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=12 loops=1)
Filter: ((d_date >= '1999-05-15'::date) AND (d_date <=
'1999-06-14 00:00:00'::timestamp without time zone))
-> Hash
(actual time=48.001..48.001 rows=6252 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 3138kB
-> Seq
Scan on item (actual time=12.000..32.001 rows=6252 loops=1)
Filter: ((i_category)::text = ANY ('{Books,Music,Home}'::text[]))
Planning Time: 2.593 ms
(slice0) Executor memory: 194K bytes.
(slice1) Executor memory: 1044K bytes avg x 3x(0) workers, 1204K bytes
max (seg0). Work_mem: 879K bytes max.
(slice2) Executor memory: 18938K bytes avg x 3x(0) workers, 19179K
bytes max (seg1). Work_mem: 15966K bytes max.
(slice3) Executor memory: 23409K bytes avg x 3x(0) workers, 23420K
bytes max (seg1). Work_mem: 3138K bytes max.
(slice4) Executor memory: 17894K bytes avg x 12x(0) workers, 17894K
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: 8723.736 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]
