| SET optimizer_trace_fallback = on; |
| -- start_ignore |
| drop table if exists t1; |
| drop table if exists t2; |
| drop table if exists t3; |
| drop table if exists pt1; |
| drop table if exists pt2; |
| -- end_ignore |
| create table t1(v1 int, v2 int); |
| create table t2(v3 int, v4 int); |
| create table t3(v5 int, v6 int); |
| insert into t1 values(generate_series(1, 100), generate_series(1, 100)); |
| insert into t2 values(generate_series(1, 100), generate_series(1, 100)); |
| insert into t3 values(generate_series(1, 100), generate_series(1, 100)); |
| CREATE TABLE pt1 ( |
| v1 INT, |
| v2 INT |
| ) PARTITION BY RANGE (v1); |
| CREATE TABLE pt1_p1 PARTITION OF pt1 FOR VALUES FROM (0) TO (20); |
| CREATE TABLE pt1_p2 PARTITION OF pt1 FOR VALUES FROM (20) TO (40); |
| CREATE TABLE pt1_p3 PARTITION OF pt1 FOR VALUES FROM (40) TO (60); |
| CREATE TABLE pt1_p4 PARTITION OF pt1 FOR VALUES FROM (60) TO (400); |
| CREATE TABLE pt2 ( |
| v3 INT, |
| v4 INT |
| ) PARTITION BY RANGE (v3); |
| CREATE TABLE pt2_p1 PARTITION OF pt2 FOR VALUES FROM (0) TO (20); |
| CREATE TABLE pt2_p2 PARTITION OF pt2 FOR VALUES FROM (20) TO (40); |
| CREATE TABLE pt2_p3 PARTITION OF pt2 FOR VALUES FROM (40) TO (60); |
| CREATE TABLE pt2_p4 PARTITION OF pt2 FOR VALUES FROM (60) TO (400); |
| insert into pt1 values(generate_series(1, 100), generate_series(1, 100)); |
| insert into pt2 values(generate_series(1, 100), generate_series(1, 100)); |
| analyze t1; |
| analyze t2; |
| analyze t3; |
| analyze pt1; |
| analyze pt2; |
| -- dedup the subquery with projection |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10); |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (14 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2 where v3 < 10) and v1 in (select v3 from t2); -- change the order |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (14 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2 where v3 < 10); |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (14 rows) |
| |
| select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10); |
| v1 | v2 |
| ----+---- |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 7 | 7 |
| 8 | 8 |
| 1 | 1 |
| 5 | 5 |
| 6 | 6 |
| 9 | 9 |
| (9 rows) |
| |
| select * from t1 where v1 in (select v3 from t2 where v3 < 10); |
| v1 | v2 |
| ----+---- |
| 1 | 1 |
| 5 | 5 |
| 6 | 6 |
| 9 | 9 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 7 | 7 |
| 8 | 8 |
| (9 rows) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from pt2 where v3 < 10); |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=10 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..862.00 rows=4 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=4 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=4 width=4) |
| Output: pt2.v3 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=4 width=4) |
| Output: pt2.v3 |
| Number of partitions to scan: 1 (out of 4) |
| Filter: (pt2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (18 rows) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2 where v3 < 10); |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=10 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..862.00 rows=4 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=4 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=4 width=4) |
| Output: pt2.v3 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=4 width=4) |
| Output: pt2.v3 |
| Number of partitions to scan: 1 (out of 4) |
| Filter: (pt2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (18 rows) |
| |
| select * from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from pt2 where v3 < 10); |
| v1 | v2 |
| ----+---- |
| 1 | 1 |
| 5 | 5 |
| 6 | 6 |
| 9 | 9 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 7 | 7 |
| 8 | 8 |
| (9 rows) |
| |
| select * from pt1 where v1 in (select v3 from pt2 where v3 < 10); |
| v1 | v2 |
| ----+---- |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 7 | 7 |
| 8 | 8 |
| 5 | 5 |
| 6 | 6 |
| 9 | 9 |
| 1 | 1 |
| (9 rows) |
| |
| explain verbose with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1 where v3 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324954.52 rows=1 width=8) |
| Output: (count(*)) |
| -> Sequence (cost=0.00..1324954.52 rows=1 width=8) |
| Output: (count(*)) |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=1) |
| Output: share0_ref1.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Redistribute Motion 1:3 (slice2) (cost=0.00..1324523.52 rows=1 width=8) |
| Output: (count(*)) |
| -> Finalize Aggregate (cost=0.00..1324523.52 rows=1 width=8) |
| Output: count(*) |
| -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1324523.52 rows=1 width=8) |
| Output: (PARTIAL count(*)) |
| -> Partial Aggregate (cost=0.00..1324523.52 rows=1 width=8) |
| Output: PARTIAL count(*) |
| -> Hash Semi Join (cost=0.00..1324523.52 rows=304 width=1) |
| Hash Cond: (t1.v1 = share0_ref2.v3) |
| -> Nested Loop (cost=0.00..1324091.93 rows=3334 width=4) |
| Output: t1.v1 |
| Join Filter: true |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Materialize (cost=0.00..431.00 rows=100 width=1) |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=100 width=1) |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=1) |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: share0_ref2.v3 |
| -> Result (cost=0.00..431.00 rows=4 width=4) |
| Output: share0_ref2.v3 |
| Filter: (share0_ref2.v3 < 10) |
| -> Shared Scan (share slice:id 3:0) (cost=0.00..431.00 rows=34 width=4) |
| Output: share0_ref2.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (35 rows) |
| |
| explain verbose with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1 where v3 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..1324523.52 rows=1 width=8) |
| Output: count(*) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324523.52 rows=1 width=8) |
| Output: (PARTIAL count(*)) |
| -> Partial Aggregate (cost=0.00..1324523.51 rows=1 width=8) |
| Output: PARTIAL count(*) |
| -> Hash Semi Join (cost=0.00..1324523.51 rows=304 width=1) |
| Hash Cond: (t1.v1 = t2_1.v3) |
| -> Nested Loop (cost=0.00..1324091.93 rows=3334 width=4) |
| Output: t1.v1 |
| Join Filter: true |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Materialize (cost=0.00..431.00 rows=100 width=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=100 width=1) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=1) |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2_1.v3 |
| Filter: (t2_1.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (23 rows) |
| |
| with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1 where v3 < 10); |
| count |
| ------- |
| 900 |
| (1 row) |
| |
| with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1 where v3 < 10); |
| count |
| ------- |
| 900 |
| (1 row) |
| |
| set optimizer_cte_inlining to on; |
| set optimizer_cte_inlining_bound to 2; |
| explain verbose with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1 where v3 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..1324523.52 rows=1 width=8) |
| Output: count(*) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324523.52 rows=1 width=8) |
| Output: (PARTIAL count(*)) |
| -> Partial Aggregate (cost=0.00..1324523.52 rows=1 width=8) |
| Output: PARTIAL count(*) |
| -> Hash Semi Join (cost=0.00..1324523.52 rows=304 width=1) |
| Hash Cond: (t1.v1 = t2_1.v3) |
| -> Nested Loop (cost=0.00..1324091.93 rows=3334 width=4) |
| Output: t1.v1 |
| Join Filter: true |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Materialize (cost=0.00..431.00 rows=100 width=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=100 width=1) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=1) |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2_1.v3 |
| Filter: (t2_1.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on', optimizer_cte_inlining_bound = '2' |
| Optimizer: GPORCA |
| (23 rows) |
| |
| with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1 where v3 < 10); |
| count |
| ------- |
| 900 |
| (1 row) |
| |
| reset optimizer_cte_inlining; |
| reset optimizer_cte_inlining_bound; |
| explain verbose select sum(v1) from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..862.01 rows=1 width=8) |
| Output: sum(t1.v1) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=1 width=8) |
| Output: (PARTIAL sum(t1.v1)) |
| -> Partial Aggregate (cost=0.00..862.01 rows=1 width=8) |
| Output: PARTIAL sum(t1.v1) |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=4) |
| Output: t1.v1 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (18 rows) |
| |
| explain verbose select sum(v1) from t1 where v1 in (select v3 from t2 where v3 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..862.01 rows=1 width=8) |
| Output: sum(t1.v1) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=1 width=8) |
| Output: (PARTIAL sum(t1.v1)) |
| -> Partial Aggregate (cost=0.00..862.01 rows=1 width=8) |
| Output: PARTIAL sum(t1.v1) |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=4) |
| Output: t1.v1 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (18 rows) |
| |
| select sum(v1) from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10); |
| sum |
| ----- |
| 45 |
| (1 row) |
| |
| select sum(v1) from t1 where v1 in (select v3 from t2 where v3 < 10); |
| sum |
| ----- |
| 45 |
| (1 row) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10) order by v1; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| Merge Key: t1.v1 |
| -> Sort (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Sort Key: t1.v1 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (18 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2 where v3 < 10) order by v1; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| Merge Key: t1.v1 |
| -> Sort (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Sort Key: t1.v1 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (18 rows) |
| |
| select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10) order by v1; |
| v1 | v2 |
| ----+---- |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| (9 rows) |
| |
| select * from t1 where v1 in (select v3 from t2 where v3 < 10) order by v1; |
| v1 | v2 |
| ----+---- |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| (9 rows) |
| |
| explain verbose select sum(v1) from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10) group by v2; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=10 width=8) |
| Output: (sum(t1.v1)) |
| -> GroupAggregate (cost=0.00..862.01 rows=4 width=8) |
| Output: sum(t1.v1) |
| Group Key: t1.v2 |
| -> Sort (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Sort Key: t1.v2 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Key: t1.v2 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (23 rows) |
| |
| explain verbose select sum(v1) from t1 where v1 in (select v3 from t2 where v3 < 10) group by v2; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.01 rows=10 width=8) |
| Output: (sum(t1.v1)) |
| -> GroupAggregate (cost=0.00..862.01 rows=4 width=8) |
| Output: sum(t1.v1) |
| Group Key: t1.v2 |
| -> Sort (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Sort Key: t1.v2 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Key: t1.v2 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (23 rows) |
| |
| select sum(v1) from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where v3 < 10) group by v2; |
| sum |
| ----- |
| 1 |
| 5 |
| 6 |
| 9 |
| 2 |
| 3 |
| 4 |
| 7 |
| 8 |
| (9 rows) |
| |
| select sum(v1) from t1 where v1 in (select v3 from t2 where v3 < 10) group by v2; |
| sum |
| ----- |
| 2 |
| 3 |
| 4 |
| 7 |
| 8 |
| 1 |
| 5 |
| 6 |
| 9 |
| (9 rows) |
| |
| -- dedup the same subqueryany |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2); |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.02 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (13 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2); |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.02 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (13 rows) |
| |
| select count(*) from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| select count(*) from t1 where v1 in (select v3 from t2); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from pt2); |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.02 rows=100 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=34 width=4) |
| Output: pt2.v3 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=34 width=4) |
| Output: pt2.v3 |
| Number of partitions to scan: 4 (out of 4) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (17 rows) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2); |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.02 rows=100 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=34 width=4) |
| Output: pt2.v3 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=34 width=4) |
| Output: pt2.v3 |
| Number of partitions to scan: 4 (out of 4) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (17 rows) |
| |
| select count(*) from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from pt2); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| select count(*) from pt1 where v1 in (select v3 from pt2); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| explain verbose with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324954.53 rows=1 width=8) |
| Output: (count(*)) |
| -> Sequence (cost=0.00..1324954.53 rows=1 width=8) |
| Output: (count(*)) |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=1) |
| Output: share0_ref1.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Redistribute Motion 1:3 (slice2) (cost=0.00..1324523.53 rows=1 width=8) |
| Output: (count(*)) |
| -> Finalize Aggregate (cost=0.00..1324523.53 rows=1 width=8) |
| Output: count(*) |
| -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1324523.53 rows=1 width=8) |
| Output: (PARTIAL count(*)) |
| -> Partial Aggregate (cost=0.00..1324523.53 rows=1 width=8) |
| Output: PARTIAL count(*) |
| -> Hash Semi Join (cost=0.00..1324523.53 rows=3334 width=1) |
| Hash Cond: (t1.v1 = share0_ref2.v3) |
| -> Nested Loop (cost=0.00..1324091.93 rows=3334 width=4) |
| Output: t1.v1 |
| Join Filter: true |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Materialize (cost=0.00..431.00 rows=100 width=1) |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=100 width=1) |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=1) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: share0_ref2.v3 |
| -> Shared Scan (share slice:id 3:0) (cost=0.00..431.00 rows=34 width=4) |
| Output: share0_ref2.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (32 rows) |
| |
| explain verbose with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..1324523.53 rows=1 width=8) |
| Output: count(*) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324523.53 rows=1 width=8) |
| Output: (PARTIAL count(*)) |
| -> Partial Aggregate (cost=0.00..1324523.53 rows=1 width=8) |
| Output: PARTIAL count(*) |
| -> Hash Semi Join (cost=0.00..1324523.53 rows=3334 width=1) |
| Hash Cond: (t1.v1 = t2_1.v3) |
| -> Nested Loop (cost=0.00..1324091.93 rows=3334 width=4) |
| Output: t1.v1 |
| Join Filter: true |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Materialize (cost=0.00..431.00 rows=100 width=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=100 width=1) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=1) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (22 rows) |
| |
| with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1); |
| count |
| ------- |
| 10000 |
| (1 row) |
| |
| with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1); |
| count |
| ------- |
| 10000 |
| (1 row) |
| |
| set optimizer_cte_inlining to on; |
| set optimizer_cte_inlining_bound to 2; |
| explain verbose with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..1324523.53 rows=1 width=8) |
| Output: count(*) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324523.53 rows=1 width=8) |
| Output: (PARTIAL count(*)) |
| -> Partial Aggregate (cost=0.00..1324523.53 rows=1 width=8) |
| Output: PARTIAL count(*) |
| -> Hash Semi Join (cost=0.00..1324523.53 rows=3334 width=1) |
| Hash Cond: (t1.v1 = t2_1.v3) |
| -> Nested Loop (cost=0.00..1324091.93 rows=3334 width=4) |
| Output: t1.v1 |
| Join Filter: true |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Materialize (cost=0.00..431.00 rows=100 width=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=100 width=1) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=1) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on', optimizer_cte_inlining_bound = '2' |
| Optimizer: GPORCA |
| (22 rows) |
| |
| with cte1 as (select v3 from t2) select count(*) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1); |
| count |
| ------- |
| 10000 |
| (1 row) |
| |
| reset optimizer_cte_inlining; |
| reset optimizer_cte_inlining_bound; |
| -- dedup the subquery with inner join |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2,t3 where v4=v6); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: t2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Key: t2.v3 |
| -> Hash Join (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Cond: (t2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| Hash Key: t2.v4 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (29 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t3,t2 where v4=v6); -- change the join order,different index in inner join |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: t2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Key: t2.v3 |
| -> Hash Join (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Cond: (t2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| Hash Key: t2.v4 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (29 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2,t3 where v4=v6); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: t2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Key: t2.v3 |
| -> Hash Join (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Cond: (t2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| Hash Key: t2.v4 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (29 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2,t3 where v4=v6 and v4 < 10); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.01 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=862.01..862.01 rows=4 width=4) |
| Output: t2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.01 rows=4 width=4) |
| Output: t2.v3 |
| Hash Key: t2.v3 |
| -> Hash Join (cost=0.00..862.01 rows=4 width=4) |
| Output: t2.v3 |
| Hash Cond: (t2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=4 width=8) |
| Output: t2.v3, t2.v4 |
| Hash Key: t2.v4 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=8) |
| Output: t2.v3, t2.v4 |
| Filter: (t2.v4 < 10) |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| Filter: (t3.v6 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (31 rows) |
| |
| select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2,t3 where v4=v6 and v4 < 10); |
| v1 | v2 |
| ----+---- |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 9 | 9 |
| 8 | 8 |
| 6 | 6 |
| 7 | 7 |
| 1 | 1 |
| 5 | 5 |
| (9 rows) |
| |
| select * from t1 where v1 in (select v3 from t2,t3 where v4=v6 and v4 < 10); |
| v1 | v2 |
| ----+---- |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 7 | 7 |
| 8 | 8 |
| 5 | 5 |
| 6 | 6 |
| 9 | 9 |
| 1 | 1 |
| (9 rows) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from pt2,t3 where v4=v6); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| Hash Key: pt2.v3 |
| -> Hash Join (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| Hash Cond: (pt2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=34 width=8) |
| Output: pt2.v3, pt2.v4 |
| Hash Key: pt2.v4 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt2.v3, pt2.v4 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (33 rows) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from t3,pt2 where v4=v6); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| Hash Key: pt2.v3 |
| -> Hash Join (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| Hash Cond: (pt2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=34 width=8) |
| Output: pt2.v3, pt2.v4 |
| Hash Key: pt2.v4 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt2.v3, pt2.v4 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (33 rows) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2,t3 where v4=v6); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| Hash Key: pt2.v3 |
| -> Hash Join (cost=0.00..862.02 rows=34 width=4) |
| Output: pt2.v3 |
| Hash Cond: (pt2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=34 width=8) |
| Output: pt2.v3, pt2.v4 |
| Hash Key: pt2.v4 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=34 width=8) |
| Output: pt2.v3, pt2.v4 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (33 rows) |
| |
| explain verbose select * from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from pt2,t3 where v4=v6 and v4 < 10); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.01 rows=10 width=8) |
| Output: pt1.v1, pt1.v2 |
| -> Hash Semi Join (cost=0.00..1293.01 rows=4 width=8) |
| Output: pt1.v1, pt1.v2 |
| Hash Cond: (pt1.v1 = pt2.v3) |
| -> Dynamic Seq Scan on public.pt1 (cost=0.00..431.00 rows=4 width=8) |
| Output: pt1.v1, pt1.v2 |
| Number of partitions to scan: 4 (out of 4) |
| -> Hash (cost=862.01..862.01 rows=4 width=4) |
| Output: pt2.v3 |
| -> Partition Selector (selector id: $0) (cost=0.00..862.01 rows=4 width=4) |
| Output: pt2.v3 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.01 rows=4 width=4) |
| Output: pt2.v3 |
| Hash Key: pt2.v3 |
| -> Hash Join (cost=0.00..862.01 rows=4 width=4) |
| Output: pt2.v3 |
| Hash Cond: (pt2.v4 = t3.v6) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=4 width=8) |
| Output: pt2.v3, pt2.v4 |
| Hash Key: pt2.v4 |
| -> Dynamic Seq Scan on public.pt2 (cost=0.00..431.00 rows=4 width=8) |
| Output: pt2.v3, pt2.v4 |
| Number of partitions to scan: 4 (out of 4) |
| Filter: (pt2.v4 < 10) |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| Filter: (t3.v6 < 10) |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (35 rows) |
| |
| select * from pt1 where v1 in (select v3 from pt2) and v1 in (select v3 from pt2,t3 where v4=v6 and v4 < 10); |
| v1 | v2 |
| ----+---- |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 9 | 9 |
| 8 | 8 |
| 6 | 6 |
| 7 | 7 |
| 1 | 1 |
| 5 | 5 |
| (9 rows) |
| |
| select * from pt1 where v1 in (select v3 from pt2,t3 where v4=v6 and v4 < 10); |
| v1 | v2 |
| ----+---- |
| 5 | 5 |
| 6 | 6 |
| 9 | 9 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 7 | 7 |
| 8 | 8 |
| 1 | 1 |
| (9 rows) |
| |
| explain verbose with cte1 as (select v3 from t2) select * from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1,t3 where v4=v6); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2648910.08 rows=5625 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| -> Sequence (cost=0.00..2648909.74 rows=1875 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=1) |
| Output: share0_ref1.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash Semi Join (cost=0.00..2648478.71 rows=1875 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Hash Cond: ((t2_1.v4 = t3.v6) AND (t1.v1 = share0_ref2.v3)) |
| -> Nested Loop (cost=0.00..1324306.71 rows=3334 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.02 rows=100 width=8) |
| Output: t2_1.v3, t2_1.v4 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2_1.v3, t2_1.v4 |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=1324169.43..1324169.43 rows=3334 width=8) |
| Output: share0_ref2.v3, t3.v6 |
| -> Nested Loop (cost=0.00..1324169.43 rows=3334 width=8) |
| Output: share0_ref2.v3, t3.v6 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.01 rows=100 width=4) |
| Output: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=4) |
| Output: share0_ref2.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (33 rows) |
| |
| explain verbose with cte1 as (select v3 from t2) select * from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from t3,cte1 where v4=v6); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2648910.08 rows=5625 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| -> Sequence (cost=0.00..2648909.74 rows=1875 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=1) |
| Output: share0_ref1.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash Semi Join (cost=0.00..2648478.71 rows=1875 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Hash Cond: ((t2_1.v4 = t3.v6) AND (t1.v1 = share0_ref2.v3)) |
| -> Nested Loop (cost=0.00..1324306.71 rows=3334 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.02 rows=100 width=8) |
| Output: t2_1.v3, t2_1.v4 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2_1.v3, t2_1.v4 |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=1324169.43..1324169.43 rows=3334 width=8) |
| Output: t3.v6, share0_ref2.v3 |
| -> Nested Loop (cost=0.00..1324169.43 rows=3334 width=8) |
| Output: t3.v6, share0_ref2.v3 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.01 rows=100 width=4) |
| Output: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v6 |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=4) |
| Output: share0_ref2.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (33 rows) |
| |
| explain verbose with cte1 as (select v3 from t2) select * from t1,t2 where t1.v1 in (select v3 from cte1,t3 where v4=v6 and v4 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2648354.45 rows=512 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| -> Hash Semi Join (cost=0.00..2648354.42 rows=171 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Hash Cond: ((t2.v4 = t3.v6) AND (t1.v1 = t2_1.v3)) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..1324306.84 rows=304 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Hash Key: t1.v1 |
| -> Result (cost=0.00..1324306.82 rows=304 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Filter: (t2.v4 < 10) |
| -> Nested Loop (cost=0.00..1324306.71 rows=3334 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.02 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| -> Hash (cost=1324047.35..1324047.35 rows=304 width=8) |
| Output: t2_1.v3, t3.v6 |
| -> Nested Loop (cost=0.00..1324047.35 rows=304 width=8) |
| Output: t2_1.v3, t3.v6 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=10 width=4) |
| Output: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| Filter: (t3.v6 < 10) |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (34 rows) |
| |
| explain verbose with cte1 as (select v3 from t2) select * from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1,t3 where v4=v6 and v4 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2648784.74 rows=512 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| -> Sequence (cost=0.00..2648784.71 rows=171 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=1) |
| Output: share0_ref1.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash Semi Join (cost=0.00..2648353.70 rows=171 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Hash Cond: ((t2_1.v4 = t3.v6) AND (t1.v1 = share0_ref2.v3)) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..1324306.84 rows=304 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Hash Key: t1.v1 |
| -> Result (cost=0.00..1324306.82 rows=304 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Filter: (t2_1.v4 < 10) |
| -> Nested Loop (cost=0.00..1324306.71 rows=3334 width=16) |
| Output: t1.v1, t1.v2, t2_1.v3, t2_1.v4 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.02 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2_1.v3, t2_1.v4 |
| -> Hash (cost=1324046.63..1324046.63 rows=304 width=8) |
| Output: share0_ref2.v3, t3.v6 |
| -> Nested Loop (cost=0.00..1324046.63 rows=304 width=8) |
| Output: share0_ref2.v3, t3.v6 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=10 width=4) |
| Output: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| Filter: (t3.v6 < 10) |
| -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=34 width=4) |
| Output: share0_ref2.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (40 rows) |
| |
| with cte1 as (select v3 from t2) select sum(v1) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1,t3 where v4=v6 and v4 < 10); |
| sum |
| ------- |
| 45450 |
| (1 row) |
| |
| with cte1 as (select v3 from t2) select sum(v1) from t1,t2 where t1.v1 in (select v3 from cte1,t3 where v4=v6 and v4 < 10); |
| sum |
| ------- |
| 45450 |
| (1 row) |
| |
| set optimizer_cte_inlining to on; |
| set optimizer_cte_inlining_bound to 2; |
| explain verbose with cte1 as (select v3 from t2) select * from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1,t3 where v4=v6 and v4 < 10); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2648354.45 rows=512 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| -> Hash Semi Join (cost=0.00..2648354.42 rows=171 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Hash Cond: ((t2.v4 = t3.v6) AND (t1.v1 = t2_1.v3)) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..1324306.84 rows=304 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Hash Key: t1.v1 |
| -> Result (cost=0.00..1324306.82 rows=304 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Filter: (t2.v4 < 10) |
| -> Nested Loop (cost=0.00..1324306.71 rows=3334 width=16) |
| Output: t1.v1, t1.v2, t2.v3, t2.v4 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.02 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=8) |
| Output: t2.v3, t2.v4 |
| -> Hash (cost=1324047.35..1324047.35 rows=304 width=8) |
| Output: t2_1.v3, t3.v6 |
| -> Nested Loop (cost=0.00..1324047.35 rows=304 width=8) |
| Output: t2_1.v3, t3.v6 |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (cost=0.00..431.00 rows=10 width=4) |
| Output: t3.v6 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=4 width=4) |
| Output: t3.v6 |
| Filter: (t3.v6 < 10) |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on', optimizer_cte_inlining_bound = '2' |
| Optimizer: GPORCA |
| (34 rows) |
| |
| with cte1 as (select v3 from t2) select sum(v1) from t1,t2 where t1.v1 in (select v3 from cte1) and t1.v1 in (select v3 from cte1,t3 where v4=v6 and v4 < 10); |
| sum |
| ------- |
| 45450 |
| (1 row) |
| |
| reset optimizer_cte_inlining; |
| reset optimizer_cte_inlining_bound; |
| -- dedup the subquery with semi join(still be a scalar in the pre-process) |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where exists (SELECT 1 FROM t3 WHERE v5 = v3)); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Cond: (t2.v3 = t3.v5) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| Filter: (NOT (t2.v3 IS NULL)) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: (1), t3.v5 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: 1, t3.v5 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (21 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2 where exists (SELECT 1 FROM t3 WHERE v5 = v3)); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=4) |
| Output: t2.v3 |
| Hash Cond: (t2.v3 = t3.v5) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| Filter: (NOT (t2.v3 IS NULL)) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: (1), t3.v5 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: 1, t3.v5 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (21 rows) |
| |
| select count(*) from t1 where v1 in (select v3 from t2) and v1 in (select v3 from t2 where exists (SELECT 1 FROM t3 WHERE v5 = v3)); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| select count(*) from t1 where v1 in (select v3 from t2 where exists (SELECT 1 FROM t3 WHERE v5 = v3)); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| -- inner join different key |
| explain verbose select v1 from t1 where v1 in (select v3 from t2) and v1 in (select v5 from t2,t3 where v5 = v3); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=4) |
| Output: t1.v1 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=4) |
| Output: t1.v1 |
| Hash Cond: (t1.v1 = t3.v5) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Hash (cost=862.01..862.01 rows=34 width=4) |
| Output: t3.v5 |
| -> Hash Join (cost=0.00..862.01 rows=34 width=4) |
| Output: t3.v5 |
| Hash Cond: (t2.v3 = t3.v5) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v5 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v5 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (20 rows) |
| |
| explain verbose select v1 from t1 where v1 in (select v5 from t2,t3 where v5 = v3); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=4) |
| Output: t1.v1 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=4) |
| Output: t1.v1 |
| Hash Cond: (t1.v1 = t3.v5) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Hash (cost=862.01..862.01 rows=34 width=4) |
| Output: t3.v5 |
| -> Hash Join (cost=0.00..862.01 rows=34 width=4) |
| Output: t3.v5 |
| Hash Cond: (t2.v3 = t3.v5) |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t3.v5 |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=4) |
| Output: t3.v5 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (20 rows) |
| |
| explain verbose select v1 from t1 where v1 in (select v3 from t2) and v1 in (select v6 from t2,t3 where v5 = v3); -- no dedup, because v6 is not the join key |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1724.04 rows=100 width=4) |
| Output: t1.v1 |
| -> Hash Semi Join (cost=0.00..1724.04 rows=34 width=4) |
| Output: t1.v1 |
| Hash Cond: (t1.v1 = t2_1.v3) |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=4) |
| Output: t1.v1 |
| Hash Cond: (t1.v1 = t3.v6) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t1.v1 |
| -> Hash (cost=862.02..862.02 rows=34 width=4) |
| Output: t3.v6 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=4) |
| Output: t3.v6 |
| Hash Key: t3.v6 |
| -> Hash Join (cost=0.00..862.02 rows=34 width=4) |
| Output: t3.v6 |
| Hash Cond: (t3.v5 = t2.v3) |
| -> Seq Scan on public.t3 (cost=0.00..431.00 rows=34 width=8) |
| Output: t3.v5, t3.v6 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (30 rows) |
| |
| select count(v1) from t1 where v1 in (select v3 from t2) and v1 in (select v5 from t2,t3 where v5 = v3); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| select count(v1) from t1 where v1 in (select v5 from t2,t3 where v5 = v3); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| -- can't dedup |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v1 in (select v4 from t2 where v3 < 10); -- different outpt |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.02 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.02 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2_1.v3) |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v4) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v4 |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v4 |
| Hash Key: t2.v4 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v4 |
| Filter: (t2.v3 < 10) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (24 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v2 in (select v3 from t2 where v3 < 10); -- different scalar ident |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.02 rows=10 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.02 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2_1.v3) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Key: t1.v1 |
| -> Hash Semi Join (cost=0.00..862.01 rows=4 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v2 = t2.v3) |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Key: t1.v2 |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=4 width=4) |
| Output: t2.v3 |
| Filter: (t2.v3 < 10) |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (27 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v2 in (select v3 from t2 group by v3); -- group by |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.04 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v2 = t2_1.v3) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Key: t1.v2 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash (cost=431.01..431.01 rows=34 width=4) |
| Output: t2_1.v3 |
| -> GroupAggregate (cost=0.00..431.01 rows=34 width=4) |
| Output: t2_1.v3 |
| Group Key: t2_1.v3 |
| -> Sort (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Sort Key: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (29 rows) |
| |
| explain verbose select * from t1 where v1 in (select v3 from t2) and v2 in (select v3 from t2 order by v3); -- order by/limit, actully this case can be the subset |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.03 rows=100 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash Semi Join (cost=0.00..1293.03 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v2 = t2_1.v3) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.02 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Key: t1.v2 |
| -> Hash Semi Join (cost=0.00..862.02 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| Hash Cond: (t1.v1 = t2.v3) |
| -> Seq Scan on public.t1 (cost=0.00..431.00 rows=34 width=8) |
| Output: t1.v1, t1.v2 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Seq Scan on public.t2 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2.v3 |
| -> Hash (cost=431.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| -> Seq Scan on public.t2 t2_1 (cost=0.00..431.00 rows=34 width=4) |
| Output: t2_1.v3 |
| Settings: enable_parallel = 'off', optimizer = 'on' |
| Optimizer: GPORCA |
| (23 rows) |
| |
| reset optimizer_trace_fallback; |
| -- start_ignore |
| drop table if exists t1; |
| drop table if exists t2; |
| drop table if exists t3; |
| drop table if exists pt1; |
| drop table if exists pt2; |
| -- end_ignore |
