diff options
Diffstat (limited to 'src/test/regress/expected/partition_join.out')
| -rw-r--r-- | src/test/regress/expected/partition_join.out | 88 |
1 files changed, 88 insertions, 0 deletions
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 53591a4f2d5..108f9ecb445 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -62,6 +62,45 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 450 | 0450 | 450 | 0450 (4 rows) +-- inner join with partially-redundant join clauses +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b; + QUERY PLAN +--------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Merge Join + Merge Cond: (t1_1.a = t2_1.a) + -> Index Scan using iprt1_p1_a on prt1_p1 t1_1 + -> Sort + Sort Key: t2_1.b + -> Seq Scan on prt2_p1 t2_1 + Filter: (a = b) + -> Hash Join + Hash Cond: (t1_2.a = t2_2.a) + -> Seq Scan on prt1_p2 t1_2 + -> Hash + -> Seq Scan on prt2_p2 t2_2 + Filter: (a = b) + -> Hash Join + Hash Cond: (t1_3.a = t2_3.a) + -> Seq Scan on prt1_p3 t1_3 + -> Hash + -> Seq Scan on prt2_p3 t2_3 + Filter: (a = b) +(22 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b; + a | c | b | c +----+------+----+------ + 0 | 0000 | 0 | 0000 + 6 | 0006 | 6 | 0006 + 12 | 0012 | 12 | 0012 + 18 | 0018 | 18 | 0018 + 24 | 0024 | 24 | 0024 +(5 rows) + -- left outer join, 3-way EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM prt1 t1 @@ -1803,6 +1842,55 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1 450 | 0002 | 450 | 0002 (4 rows) +-- inner join with partially-redundant join clauses +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.a AND t1.a = t2.b AND t1.c = t2.c ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------------------------------ + Sort + Sort Key: t1.a + -> Append + -> Hash Join + Hash Cond: ((t1_1.a = t2_1.a) AND ((t1_1.c)::text = (t2_1.c)::text)) + -> Seq Scan on prt1_l_p1 t1_1 + -> Hash + -> Seq Scan on prt2_l_p1 t2_1 + Filter: (a = b) + -> Hash Join + Hash Cond: ((t1_2.a = t2_2.a) AND ((t1_2.c)::text = (t2_2.c)::text)) + -> Seq Scan on prt1_l_p2_p1 t1_2 + -> Hash + -> Seq Scan on prt2_l_p2_p1 t2_2 + Filter: (a = b) + -> Hash Join + Hash Cond: ((t1_3.a = t2_3.a) AND ((t1_3.c)::text = (t2_3.c)::text)) + -> Seq Scan on prt1_l_p2_p2 t1_3 + -> Hash + -> Seq Scan on prt2_l_p2_p2 t2_3 + Filter: (a = b) + -> Hash Join + Hash Cond: ((t1_5.a = t2_5.a) AND ((t1_5.c)::text = (t2_5.c)::text)) + -> Append + -> Seq Scan on prt1_l_p3_p1 t1_5 + -> Seq Scan on prt1_l_p3_p2 t1_6 + -> Hash + -> Append + -> Seq Scan on prt2_l_p3_p1 t2_5 + Filter: (a = b) + -> Seq Scan on prt2_l_p3_p2 t2_6 + Filter: (a = b) +(32 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.a AND t1.a = t2.b AND t1.c = t2.c ORDER BY t1.a, t2.b; + a | c | b | c +----+------+----+------ + 0 | 0000 | 0 | 0000 + 6 | 0002 | 6 | 0002 + 12 | 0000 | 12 | 0000 + 18 | 0002 | 18 | 0002 + 24 | 0000 | 24 | 0000 +(5 rows) + -- left join EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b; |