| -- This test is to verify the order of reusing idle gangs. |
| -- |
| -- For example: |
| -- In the same session, |
| -- query 1 has 3 slices and it creates gang B, gang C and gang D. |
| -- query 2 has 2 slices, we hope it reuses gang B and gang C instead of other |
| -- cases like gang D and gang C. |
| -- |
| -- In this way, the two queries can have the same send-receive port pair. It's |
| -- useful in platform like Azure. Because Azure limits the number of different |
| -- send-receive port pairs (AKA flow) in a certain time period. |
| -- To verify the order we show the gang id in EXPLAIN ANALYZE output when |
| -- gp_log_gang is 'debug', turn on this output. |
| set gp_log_gang to 'debug'; |
| set gp_cached_segworkers_threshold to 10; |
| set gp_vmem_idle_resource_timeout to '60s'; |
| set optimizer_enable_motion_broadcast to off; |
| set optimizer_force_multistage_agg to on; |
| create table test_gang_reuse_t1 (c1 int, c2 int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| analyze test_gang_reuse_t1; |
| -- this query will create 3 reader gangs with ids C, D and E, we expect they |
| -- will always be reused in the same order |
| explain analyze select count(*) from test_gang_reuse_t1 a |
| join test_gang_reuse_t1 b using (c2) |
| join test_gang_reuse_t1 c using (c2) |
| ; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate (cost=3.20..3.21 rows=1 width=8) (actual time=5.633..5.633 rows=1 loops=1) |
| -> Gather Motion 3:1 (slice1; gang0; segments: 3) (cost=2.08..3.19 rows=3 width=0) (actual time=5.627..5.627 rows=0 loops=1) |
| -> Hash Join (cost=2.08..3.15 rows=1 width=0) (never executed) |
| Hash Cond: (a.c2 = c.c2) |
| -> Hash Join (cost=1.04..2.09 rows=1 width=8) (never executed) |
| Hash Cond: (a.c2 = b.c2) |
| -> Redistribute Motion 3:3 (slice2; gang3; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: a.c2 |
| -> Seq Scan on test_gang_reuse_t1 a (cost=0.00..1.01 rows=1 width=4) (never executed) |
| -> Hash (cost=1.03..1.03 rows=1 width=4) (never executed) |
| -> Redistribute Motion 3:3 (slice3; gang6; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: b.c2 |
| -> Seq Scan on test_gang_reuse_t1 b (cost=0.00..1.01 rows=1 width=4) (never executed) |
| -> Hash (cost=1.03..1.03 rows=1 width=4) (never executed) |
| Buckets: 262144 Batches: 1 Memory Usage: 2048kB |
| -> Redistribute Motion 3:3 (slice4; gang9; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: c.c2 |
| -> Seq Scan on test_gang_reuse_t1 c (cost=0.00..1.01 rows=1 width=4) (never executed) |
| Planning Time: 0.797 ms |
| (slice0) Executor memory: 59K bytes. |
| (slice1) Executor memory: 2077K bytes avg x 3 workers, 2077K bytes max (seg0). Work_mem: 2048K bytes max. |
| (slice2) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| (slice3) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| (slice4) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| Memory used: 128000kB |
| Optimizer: Postgres query optimizer |
| Execution Time: 54.019 ms |
| (27 rows) |
| |
| -- so in this query the gangs C and D should be used |
| explain analyze select count(*) from test_gang_reuse_t1 a |
| join test_gang_reuse_t1 b using (c2) |
| ; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate (cost=2.14..2.15 rows=1 width=8) (actual time=7.442..7.442 rows=1 loops=1) |
| -> Gather Motion 3:1 (slice1; gang0; segments: 3) (cost=1.04..2.13 rows=3 width=0) (actual time=7.437..7.437 rows=0 loops=1) |
| -> Hash Join (cost=1.04..2.09 rows=1 width=0) (never executed) |
| Hash Cond: (a.c2 = b.c2) |
| -> Redistribute Motion 3:3 (slice2; gang3; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: a.c2 |
| -> Seq Scan on test_gang_reuse_t1 a (cost=0.00..1.01 rows=1 width=4) (never executed) |
| -> Hash (cost=1.03..1.03 rows=1 width=4) (never executed) |
| Buckets: 524288 Batches: 1 Memory Usage: 4096kB |
| -> Redistribute Motion 3:3 (slice3; gang6; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: b.c2 |
| -> Seq Scan on test_gang_reuse_t1 b (cost=0.00..1.01 rows=1 width=4) (never executed) |
| Planning Time: 0.154 ms |
| (slice0) Executor memory: 41K bytes. |
| (slice1) Executor memory: 4114K bytes avg x 3 workers, 4114K bytes max (seg0). Work_mem: 4096K bytes max. |
| (slice2) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| (slice3) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| Memory used: 128000kB |
| Optimizer: Postgres query optimizer |
| Execution Time: 8.931 ms |
| (20 rows) |
| |
| -- so in this query the gangs C, D and E should be used |
| explain analyze select count(*) from test_gang_reuse_t1 a |
| join test_gang_reuse_t1 b using (c2) |
| join test_gang_reuse_t1 c using (c2) |
| ; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate (cost=3.20..3.21 rows=1 width=8) (actual time=2.385..2.385 rows=1 loops=1) |
| -> Gather Motion 3:1 (slice1; gang0; segments: 3) (cost=2.08..3.19 rows=3 width=0) (actual time=2.382..2.382 rows=0 loops=1) |
| -> Hash Join (cost=2.08..3.15 rows=1 width=0) (never executed) |
| Hash Cond: (a.c2 = c.c2) |
| -> Hash Join (cost=1.04..2.09 rows=1 width=8) (never executed) |
| Hash Cond: (a.c2 = b.c2) |
| -> Redistribute Motion 3:3 (slice2; gang3; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: a.c2 |
| -> Seq Scan on test_gang_reuse_t1 a (cost=0.00..1.01 rows=1 width=4) (never executed) |
| -> Hash (cost=1.03..1.03 rows=1 width=4) (never executed) |
| -> Redistribute Motion 3:3 (slice3; gang6; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: b.c2 |
| -> Seq Scan on test_gang_reuse_t1 b (cost=0.00..1.01 rows=1 width=4) (never executed) |
| -> Hash (cost=1.03..1.03 rows=1 width=4) (never executed) |
| Buckets: 262144 Batches: 1 Memory Usage: 2048kB |
| -> Redistribute Motion 3:3 (slice4; gang9; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: c.c2 |
| -> Seq Scan on test_gang_reuse_t1 c (cost=0.00..1.01 rows=1 width=4) (never executed) |
| Planning Time: 0.265 ms |
| (slice0) Executor memory: 59K bytes. |
| (slice1) Executor memory: 2077K bytes avg x 3 workers, 2077K bytes max (seg0). Work_mem: 2048K bytes max. |
| (slice2) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| (slice3) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| (slice4) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| Memory used: 128000kB |
| Optimizer: Postgres query optimizer |
| Execution Time: 3.601 ms |
| (27 rows) |
| |
| -- so in this query the gangs C and D should be used |
| explain analyze select count(*) from test_gang_reuse_t1 a |
| join test_gang_reuse_t1 b using (c2) |
| ; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate (cost=2.14..2.15 rows=1 width=8) (actual time=3.471..3.471 rows=1 loops=1) |
| -> Gather Motion 3:1 (slice1; gang0; segments: 3) (cost=1.04..2.13 rows=3 width=0) (actual time=3.467..3.467 rows=0 loops=1) |
| -> Hash Join (cost=1.04..2.09 rows=1 width=0) (never executed) |
| Hash Cond: (a.c2 = b.c2) |
| -> Redistribute Motion 3:3 (slice2; gang3; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: a.c2 |
| -> Seq Scan on test_gang_reuse_t1 a (cost=0.00..1.01 rows=1 width=4) (never executed) |
| -> Hash (cost=1.03..1.03 rows=1 width=4) (never executed) |
| Buckets: 524288 Batches: 1 Memory Usage: 4096kB |
| -> Redistribute Motion 3:3 (slice3; gang6; segments: 3) (cost=0.00..1.03 rows=1 width=4) (never executed) |
| Hash Key: b.c2 |
| -> Seq Scan on test_gang_reuse_t1 b (cost=0.00..1.01 rows=1 width=4) (never executed) |
| Planning Time: 0.149 ms |
| (slice0) Executor memory: 41K bytes. |
| (slice1) Executor memory: 4114K bytes avg x 3 workers, 4114K bytes max (seg0). Work_mem: 4096K bytes max. |
| (slice2) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| (slice3) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| Memory used: 128000kB |
| Optimizer: Postgres query optimizer |
| Execution Time: 4.530 ms |
| (20 rows) |
| |
| reset optimizer_force_multistage_agg; |
