avamingli commented on PR #1261: URL: https://github.com/apache/cloudberry/pull/1261#issuecomment-3131163125
Q44
```sql
q44 old
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=20508.439..20508.439 rows=0 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=20508.439..20508.439 rows=0 loops=1)
Merge Key: v11.rnk
-> Limit (actual time=20496.439..20496.439 rows=0 loops=1)
-> Sort (actual time=20496.439..20496.439 rows=0 loops=1)
Sort Key: v11.rnk
Sort Method: quicksort Memory: 75kB
-> Hash Join (actual time=20496.439..20496.439 rows=0
loops=1)
Hash Cond: (i2.i_item_sk = v21.item_sk)
-> Seq Scan on item i2 (never executed)
-> Hash (actual time=20476.439..20476.439 rows=0
loops=1)
Buckets: 1048576 Batches: 1 Memory Usage:
8192kB
-> Redistribute Motion 3:3 (slice2;
segments: 3) (actual time=20476.439..20476.439 rows=0 loops=1)
Hash Key: v21.item_sk
-> Hash Join (actual
time=20472.439..20472.439 rows=0 loops=1)
Hash Cond: (i1.i_item_sk =
v11.item_sk)
-> Seq Scan on item i1 (never
executed)
-> Hash (actual
time=20440.438..20440.438 rows=0 loops=1)
Buckets: 1048576
Batches: 1 Memory Usage: 8192kB
-> Redistribute Motion
1:3 (slice3; segments: 1) (actual time=20440.438..20440.438 rows=0 loops=1)
Hash Key:
v11.item_sk
-> Hash Join
(actual time=20448.438..20448.438 rows=0 loops=1)
Hash Cond:
(v11.rnk = v21.rnk)
-> Subquery
Scan on v11 (actual time=20448.438..20448.438 rows=0 loops=1)
Filter:
(v11.rnk < 11)
->
WindowAgg (actual time=20448.438..20448.438 rows=0 loops=1)
Order By: v1.rank_col
-> Gather Motion 3:1 (slice4; segments: 3) (actual time=20448.438..20448.438
rows=0 loops=1)
Merge Key: v1.rank_col
-> Sort (actual time=17764.381..17764.381 rows=0 loops=1)
Sort Key: v1.rank_col
Sort Method: quicksort Memory: 75kB
-> Subquery Scan on v1 (actual time=17764.381..17764.381 rows=0
loops=1)
-> GroupAggregate (actual time=17764.381..17764.381 rows=0
loops=1)
Group Key: ss1.ss_item_sk
Filter: (avg(ss1.ss_net_profit) > (0.9 * $0))
InitPlan 1 (returns $0) (slice5)
-> Gather Motion 12:1 (slice6; segments: 12) (actual
time=6348.136..0.000 rows=0 loops=1)
-> GroupAggregate (actual
time=6336.136..6336.136 rows=0 loops=1)
Group Key: store_sales.ss_store_sk
-> Redistribute Motion 12:12 (slice7;
segments: 12) (actual time=6336.136..6336.136 rows=0 loops=1)
Hash Key: store_sales.ss_store_sk
Hash Module: 3
-> Parallel Seq Scan on store_sales
(actual time=6332.136..6332.136 rows=0 loops=1)
Filter: ((ss_cdemo_sk IS NULL)
AND (ss_store_sk = 5))
-> Sort (actual time=17764.381..17764.381 rows=0 loops=1)
Sort Key: ss1.ss_item_sk
Sort Method: quicksort Memory: 75kB
-> Seq Scan on store_sales ss1 (actual
time=17764.381..17764.381 rows=0 loops=1)
Filter: (ss_store_sk = 5)
-> Hash
(never executed)
->
Subquery Scan on v21 (never executed)
Filter: (v21.rnk < 11)
-> WindowAgg (never executed)
Order By: v2.rank_col
-> Gather Motion 3:1 (slice8; segments: 3) (never executed)
Merge Key: v2.rank_col
-> Sort (actual time=20720.444..20720.444 rows=0 loops=1)
Sort Key: v2.rank_col DESC
Sort Method: quicksort Memory: 75kB
-> Subquery Scan on v2 (actual time=20720.444..20720.444
rows=0 loops=1)
-> GroupAggregate (actual time=20720.444..20720.444
rows=0 loops=1)
Group Key: ss1_1.ss_item_sk
Filter: (avg(ss1_1.ss_net_profit) > (0.9 * $1))
InitPlan 2 (returns $1) (slice9)
-> Gather Motion 12:1 (slice10; segments: 12)
(actual time=6680.143..0.000 rows=0 loops=1)
-> GroupAggregate (actual
time=6676.143..6676.143 rows=0 loops=1)
Group Key: store_sales_1.ss_store_sk
-> Redistribute Motion 12:12
(slice11; segments: 12) (actual time=6676.143..6676.143 rows=0 loops=1)
Hash Key:
store_sales_1.ss_store_sk
Hash Module: 3
-> Parallel Seq Scan on
store_sales store_sales_1 (actual time=4988.107..4988.107 rows=0 loops=1)
Filter: ((ss_cdemo_sk IS
NULL) AND (ss_store_sk = 5))
-> Sort (actual time=20720.444..20720.444 rows=0
loops=1)
Sort Key: ss1_1.ss_item_sk
Sort Method: quicksort Memory: 75kB
-> Seq Scan on store_sales ss1_1 (actual
time=20720.444..20720.444 rows=0 loops=1)
Filter: (ss_store_sk = 5)
Planning Time: 6.328 ms
(slice0) Executor memory: 390K bytes.
(slice1) Executor memory: 8225K bytes avg x 3x(0) workers, 8225K bytes
max (seg0). Work_mem: 8192K bytes max.
(slice2) Executor memory: 8221K bytes avg x 3x(0) workers, 8221K bytes
max (seg0). Work_mem: 8192K bytes max.
(slice3) Executor memory: 137K bytes (seg1).
(slice4) Executor memory: 9622K bytes avg x 3x(0) workers, 9622K bytes
max (seg1).
(slice5) Executor memory: 271K bytes.
_ (slice6) Workers: Workers: 12 not dispatched;.
Executor memory: 81K bytes avg x 12x(0) workers, 123K bytes max (seg0).
_ (slice7) Workers: Workers: 12 not dispatched;.
Executor memory: 9642K bytes avg x 12x(0) workers, 9643K bytes max (seg0).
(slice8) Executor memory: 9622K bytes avg x 3x(0) workers, 9622K bytes
max (seg1).
(slice9) Executor memory: 330K bytes.
_ (slice10) Workers: Workers: 12 not dispatched;.
Executor memory: 107K bytes avg x 12x(0) workers, 123K bytes max (seg0).
_ (slice11) Workers: Workers: 12 not dispatched;.
Executor memory: 9642K bytes avg x 12x(0) workers, 9643K bytes max (seg0).
Memory used: 1048576kB
Optimizer: Postgres query optimizer
Execution Time: 33816.747 ms
(98 rows)
-- q44 new
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=8072.172..8072.172 rows=0 loops=1)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=8072.172..8072.172 rows=0 loops=1)
Merge Key: v11.rnk
-> Limit (actual time=8072.172..8072.172 rows=0 loops=1)
-> Sort (actual time=8072.172..8072.172 rows=0 loops=1)
Sort Key: v11.rnk
Sort Method: quicksort Memory: 75kB
-> Hash Join (actual time=8072.172..8072.172 rows=0
loops=1)
Hash Cond: (v21.item_sk = i2.i_item_sk)
-> Redistribute Motion 3:3 (slice2; segments:
3) (actual time=8036.171..8036.171 rows=0 loops=1)
Hash Key: v21.item_sk
-> Hash Join (actual
time=8060.172..8060.172 rows=0 loops=1)
Hash Cond: (v11.item_sk =
i1.i_item_sk)
-> Redistribute Motion 1:3 (slice3;
segments: 1) (actual time=7996.171..7996.171 rows=0 loops=1)
Hash Key: v11.item_sk
-> Parallel Hash Join (actual
time=8048.172..8048.172 rows=0 loops=1)
Hash Cond: (v11.rnk =
v21.rnk)
-> Subquery Scan on v11
(actual time=8048.172..8048.172 rows=0 loops=1)
Filter: (v11.rnk <
11)
-> WindowAgg
(actual time=8048.172..8048.172 rows=0 loops=1)
Order By:
v1.rank_col
-> Gather
Motion 12:1 (slice4; segments: 12) (actual time=8048.172..8048.172 rows=0
loops=1)
Merge
Key: v1.rank_col
->
Sort (actual time=8044.172..8044.172 rows=0 loops=1)
Sort Key: v1.rank_col
Sort Method: quicksort Memory: 300kB
-> Subquery Scan on v1 (actual time=8036.171..8036.171 rows=0 loops=1)
-> GroupAggregate (actual time=8036.171..8036.171 rows=0 loops=1)
Group Key: ss1.ss_item_sk
Filter: (avg(ss1.ss_net_profit) > (0.9 * $0))
InitPlan 1 (returns $0) (slice6)
-> Gather Motion 12:1 (slice7; segments: 12) (actual
time=6816.145..0.000 rows=0 loops=1)
-> GroupAggregate (actual time=6808.145..6808.145 rows=0
loops=1)
Group Key: store_sales.ss_store_sk
-> Redistribute Motion 12:12 (slice8; segments: 12)
(actual time=6808.145..6808.145 rows=0 loops=1)
Hash Key: store_sales.ss_store_sk
Hash Module: 3
-> Parallel Seq Scan on store_sales (actual
time=5988.128..5988.128 rows=0 loops=1)
Filter: ((ss_cdemo_sk IS NULL) AND
(ss_store_sk = 5))
-> Sort (actual time=8036.171..8036.171 rows=0 loops=1)
Sort Key: ss1.ss_item_sk
Sort Method: quicksort Memory: 300kB
-> Redistribute Motion 12:12 (slice5; segments: 12) (actual
time=8036.171..8036.171 rows=0 loops=1)
Hash Key: ss1.ss_item_sk
Hash Module: 3
-> Parallel Seq Scan on store_sales ss1 (actual
time=7916.169..7916.169 rows=0 loops=1)
Filter: (ss_store_sk = 5)
-> Parallel Hash (never
executed)
-> Subquery Scan
on v21 (never executed)
Filter:
(v21.rnk < 11)
-> WindowAgg
(never executed)
Order
By: v2.rank_col
->
Gather Motion 12:1 (slice9; segments: 12) (never executed)
Merge Key: v2.rank_col
-> Sort (actual time=7912.169..7912.169 rows=0 loops=1)
Sort Key: v2.rank_col DESC
Sort Method: quicksort Memory: 300kB
-> Subquery Scan on v2 (actual time=7912.169..7912.169 rows=0 loops=1)
-> GroupAggregate (actual time=7912.169..7912.169 rows=0 loops=1)
Group Key: ss1_1.ss_item_sk
Filter: (avg(ss1_1.ss_net_profit) > (0.9 * $1))
InitPlan 2 (returns $1) (slice11)
-> Gather Motion 12:1 (slice12; segments: 12) (actual
time=7488.160..0.000 rows=0 loops=1)
-> GroupAggregate (actual time=7484.160..7484.160
rows=0 loops=1)
Group Key: store_sales_1.ss_store_sk
-> Redistribute Motion 12:12 (slice13;
segments: 12) (actual time=7484.160..7484.160 rows=0 loops=1)
Hash Key: store_sales_1.ss_store_sk
Hash Module: 3
-> Parallel Seq Scan on store_sales
store_sales_1 (actual time=6832.146..6832.146 rows=0 loops=1)
Filter: ((ss_cdemo_sk IS NULL) AND
(ss_store_sk = 5))
-> Sort (actual time=7912.169..7912.169 rows=0 loops=1)
Sort Key: ss1_1.ss_item_sk
Sort Method: quicksort Memory: 300kB
-> Redistribute Motion 12:12 (slice10; segments: 12)
(actual time=7912.169..7912.169 rows=0 loops=1)
Hash Key: ss1_1.ss_item_sk
Hash Module: 3
-> Parallel Seq Scan on store_sales ss1_1 (actual
time=7428.158..7428.158 rows=0 loops=1)
Filter: (ss_store_sk = 5)
-> Hash (actual time=44.001..44.001
rows=20772 loops=1)
Buckets: 524288 Batches: 1
Memory Usage: 5266kB
-> Seq Scan on item i1 (actual
time=4.000..20.000 rows=20772 loops=1)
-> Hash (actual time=60.001..60.001 rows=20772
loops=1)
Buckets: 524288 Batches: 1 Memory Usage:
5266kB
-> Seq Scan on item i2 (actual
time=4.000..40.001 rows=20772 loops=1)
Planning Time: 4.783 ms
(slice0) Executor memory: 403K bytes.
(slice1) Executor memory: 11895K bytes avg x 3x(0) workers, 11895K
bytes max (seg2). Work_mem: 5266K bytes max.
(slice2) Executor memory: 11890K bytes avg x 3x(0) workers, 11891K
bytes max (seg2). Work_mem: 5266K bytes max.
(slice3) Executor memory: 138K bytes (seg1).
(slice4) Executor memory: 268K bytes avg x 12x(0) workers, 268K bytes
max (seg0).
(slice5) Executor memory: 9639K bytes avg x 12x(0) workers, 9639K
bytes max (seg1).
(slice6) Executor memory: 277K bytes.
_ (slice7) Workers: Workers: 12 not dispatched;.
Executor memory: 92K bytes avg x 12x(0) workers, 124K bytes max (seg0).
_ (slice8) Workers: Workers: 12 not dispatched;.
Executor memory: 9643K bytes avg x 12x(0) workers, 9643K bytes max (seg0).
(slice9) Executor memory: 268K bytes avg x 12x(0) workers, 268K bytes
max (seg0).
(slice10) Executor memory: 9639K bytes avg x 12x(0) workers, 9639K
bytes max (seg1).
(slice11) Executor memory: 338K bytes.
_ (slice12) Workers: Workers: 12 not dispatched;.
Executor memory: 108K bytes avg x 12x(0) workers, 124K bytes max (seg0).
_ (slice13) Workers: Workers: 12 not dispatched;.
Executor memory: 9643K bytes avg x 12x(0) workers, 9643K bytes max (seg0).
Memory used: 1048576kB
Optimizer: Postgres query optimizer
Execution Time: 22515.025 ms
(106 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]
