From 1ce7db976f10cf5b2d515ffd726fac8936e55b4a Mon Sep 17 00:00:00 2001 From: amit Date: Tue, 25 Jun 2019 10:18:43 +0900 Subject: [PATCH 1/2] Fix partitionwise join code to handle FULL OUTER JOIN correctly --- src/backend/optimizer/path/joinrels.c | 108 +++++++++++---- src/backend/optimizer/util/plancat.c | 20 +-- src/backend/optimizer/util/relnode.c | 92 +++++++----- src/include/nodes/pathnodes.h | 36 +++-- src/test/regress/expected/partition_join.out | 200 +++++++++++++++++++++++++++ src/test/regress/sql/partition_join.sql | 29 ++++ 6 files changed, 408 insertions(+), 77 deletions(-) diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 43c3b7ea48..0b9e61a5cd 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -45,8 +45,9 @@ static void try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, static SpecialJoinInfo *build_child_join_sjinfo(PlannerInfo *root, SpecialJoinInfo *parent_sjinfo, Relids left_relids, Relids right_relids); -static int match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, +static int match_join_arg_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op); +static List *extract_coalesce_args(Expr *expr); /* @@ -1557,8 +1558,10 @@ build_child_join_sjinfo(PlannerInfo *root, SpecialJoinInfo *parent_sjinfo, } /* - * Returns true if there exists an equi-join condition for each pair of - * partition keys from given relations being joined. + * have_partkey_equi_join + * Returns true if there exist equi-join conditions involving pairs + * of matching partition keys of the relations being joined for all + * partition keys */ bool have_partkey_equi_join(RelOptInfo *joinrel, @@ -1631,10 +1634,10 @@ have_partkey_equi_join(RelOptInfo *joinrel, * Only clauses referencing the partition keys are useful for * partitionwise join. */ - ipk1 = match_expr_to_partition_keys(expr1, rel1, strict_op); + ipk1 = match_join_arg_to_partition_keys(expr1, rel1, strict_op); if (ipk1 < 0) continue; - ipk2 = match_expr_to_partition_keys(expr2, rel2, strict_op); + ipk2 = match_join_arg_to_partition_keys(expr2, rel2, strict_op); if (ipk2 < 0) continue; @@ -1674,13 +1677,19 @@ have_partkey_equi_join(RelOptInfo *joinrel, } /* - * Find the partition key from the given relation matching the given - * expression. If found, return the index of the partition key, else return -1. + * match_join_arg_to_partition_keys + * Tries to match a join clause argument expression to one of the nullable + * or non-nullable partition keys and if a match is found, returns the + * matched key's ordinal position; -1 is returned if the expression + * doesn't match any of the keys or if strict_op being false prevents + * nullable keys to be matched */ static int -match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op) +match_join_arg_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op) { int cnt; + int matched = -1; + List *nullable_exprs; /* This function should be called only for partitioned relations. */ Assert(rel->part_scheme); @@ -1689,34 +1698,85 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op) while (IsA(expr, RelabelType)) expr = (Expr *) (castNode(RelabelType, expr))->arg; + /* + * Extract the arguments from possibly nested COALESCE expressions. Each + * of these arguments could be null when joining, so these expressions are + * called as such and are to be matched only with the nullable partition + * keys. + */ + if (IsA(expr, CoalesceExpr)) + nullable_exprs = extract_coalesce_args(expr); + else + /* + * expr may or may not be nullable but add to the list anyway to + * simplify the coding below. + */ + nullable_exprs = list_make1(expr); + for (cnt = 0; cnt < rel->part_scheme->partnatts; cnt++) { - ListCell *lc; - Assert(rel->partexprs); - foreach(lc, rel->partexprs[cnt]) + + /* Is the expression one of the non-nullable partition keys? */ + if (list_member(rel->partexprs[cnt], expr)) { - if (equal(lfirst(lc), expr)) - return cnt; + matched = cnt; + break; } + /* + * Nope, so check if it is one of the nullable keys. Allowing + * nullable keys won't work if the join operator is not strict, + * because null partition keys may then join with rows from other + * partitions. XXX - would that ever be true if the operator is + * already determined to be mergejoin- and hashjoin-able? + */ if (!strict_op) continue; - /* - * If it's a strict equi-join a NULL partition key on one side will - * not join a NULL partition key on the other side. So, rows with NULL - * partition key from a partition on one side can not join with those - * from a non-matching partition on the other side. So, search the - * nullable partition keys as well. - */ + /* OK to match with nullable keys. */ Assert(rel->nullable_partexprs); - foreach(lc, rel->nullable_partexprs[cnt]) + if (list_intersection(rel->nullable_partexprs[cnt], + nullable_exprs) != NIL) { - if (equal(lfirst(lc), expr)) - return cnt; + matched = cnt; + break; } } - return -1; + Assert(list_length(nullable_exprs) >= 1); + list_free(nullable_exprs); + + return matched; +} + +/* + * extract_coalesce_args + * Extract all arguments from arbitrarily nested CoalesceExpr's + * + * Note: caller should free the List structure when done using it. + */ +static List * +extract_coalesce_args(Expr *expr) +{ + List *coalesce_args = NIL; + + while (expr && IsA(expr, CoalesceExpr)) + { + CoalesceExpr *cexpr = (CoalesceExpr *) expr; + ListCell *lc; + + expr = NULL; + foreach(lc, cexpr->args) + { + if (IsA(lfirst(lc), CoalesceExpr)) + expr = lfirst(lc); + else + coalesce_args = lappend(coalesce_args, lfirst(lc)); + } + + Assert(expr == NULL || IsA(expr, CoalesceExpr)); + } + + return coalesce_args; } diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 40f497660d..e58bace542 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -2254,9 +2254,8 @@ find_partition_scheme(PlannerInfo *root, Relation relation) /* * set_baserel_partition_key_exprs * - * Builds partition key expressions for the given base relation and sets them - * in given RelOptInfo. Any single column partition keys are converted to Var - * nodes. All Var nodes are restamped with the relid of given relation. + * Builds partition key expressions for the given base relation and sets + * rel->partexprs. */ static void set_baserel_partition_key_exprs(Relation relation, @@ -2304,16 +2303,19 @@ set_baserel_partition_key_exprs(Relation relation, lc = lnext(lc); } + /* Base relations have a single expression per key. */ partexprs[cnt] = list_make1(partexpr); } + /* + * For base relations, we assume that the partition keys are non-nullable, + * although they are nullable in principle; list and hash partitioned + * tables may contain nulls in the partition key(s), for example. + * Assuming non-nullability is okay for the considerations of partition + * pruning, because pruning is never performed with non-strict operators. + */ rel->partexprs = partexprs; - /* - * A base relation can not have nullable partition key expressions. We - * still allocate array of empty expressions lists to keep partition key - * expression handling code simple. See build_joinrel_partition_info() and - * match_expr_to_partition_keys(). - */ + /* Assigning NIL for each key means there are no nullable keys. */ rel->nullable_partexprs = (List **) palloc0(sizeof(List *) * partnatts); } diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 6054bd2b53..80de20f13d 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -58,6 +58,9 @@ static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel); static void build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, RelOptInfo *inner_rel, List *restrictlist, JoinType jointype); +static void set_joinrel_partition_key_exprs(RelOptInfo *joinrel, + RelOptInfo *outer_rel, RelOptInfo *inner_rel, + JoinType jointype); static void build_child_join_reltarget(PlannerInfo *root, RelOptInfo *parentrel, RelOptInfo *childrel, @@ -1591,18 +1594,18 @@ find_param_path_info(RelOptInfo *rel, Relids required_outer) /* * build_joinrel_partition_info - * If the two relations have same partitioning scheme, their join may be - * partitioned and will follow the same partitioning scheme as the joining - * relations. Set the partition scheme and partition key expressions in - * the join relation. + * Checks if the two relations being joined can use partitionwise join + * and if yes, initialize partitioning information of the resulting + * partitioned relation + * + * This will set part_scheme and partition key expressions (partexprs and + * nullable_partexprs) if required. */ static void build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, RelOptInfo *inner_rel, List *restrictlist, JoinType jointype) { - int partnatts; - int cnt; PartitionScheme part_scheme; /* Nothing to do if partitionwise join technique is disabled. */ @@ -1669,11 +1672,8 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, */ joinrel->part_scheme = part_scheme; joinrel->boundinfo = outer_rel->boundinfo; - partnatts = joinrel->part_scheme->partnatts; - joinrel->partexprs = (List **) palloc0(sizeof(List *) * partnatts); - joinrel->nullable_partexprs = - (List **) palloc0(sizeof(List *) * partnatts); joinrel->nparts = outer_rel->nparts; + set_joinrel_partition_key_exprs(joinrel, outer_rel, inner_rel, jointype); joinrel->part_rels = (RelOptInfo **) palloc0(sizeof(RelOptInfo *) * joinrel->nparts); @@ -1683,32 +1683,31 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, Assert(outer_rel->consider_partitionwise_join); Assert(inner_rel->consider_partitionwise_join); joinrel->consider_partitionwise_join = true; +} + +/* + * set_joinrel_partition_key_exprs + * Initialize partition key expressions + */ +static void +set_joinrel_partition_key_exprs(RelOptInfo *joinrel, + RelOptInfo *outer_rel, RelOptInfo *inner_rel, + JoinType jointype) +{ + int partnatts; + int cnt; + + Assert(joinrel->part_scheme != NULL); + + partnatts = joinrel->part_scheme->partnatts; + joinrel->partexprs = (List **) palloc0(sizeof(List *) * partnatts); + joinrel->nullable_partexprs = + (List **) palloc0(sizeof(List *) * partnatts); /* - * Construct partition keys for the join. - * - * An INNER join between two partitioned relations can be regarded as - * partitioned by either key expression. For example, A INNER JOIN B ON - * A.a = B.b can be regarded as partitioned on A.a or on B.b; they are - * equivalent. - * - * For a SEMI or ANTI join, the result can only be regarded as being - * partitioned in the same manner as the outer side, since the inner - * columns are not retained. - * - * An OUTER join like (A LEFT JOIN B ON A.a = B.b) may produce rows with - * B.b NULL. These rows may not fit the partitioning conditions imposed on - * B.b. Hence, strictly speaking, the join is not partitioned by B.b and - * thus partition keys of an OUTER join should include partition key - * expressions from the OUTER side only. However, because all - * commonly-used comparison operators are strict, the presence of nulls on - * the outer side doesn't cause any problem; they can't match anything at - * future join levels anyway. Therefore, we track two sets of - * expressions: those that authentically partition the relation - * (partexprs) and those that partition the relation with the exception - * that extra nulls may be present (nullable_partexprs). When the - * comparison operator is strict, the latter is just as good as the - * former. + * Join type determines which partition keys are assumed by the resulting + * join relation. Note that these keys are to be considered when checking + * if any further joins involving this joinrel may be partitioned. */ for (cnt = 0; cnt < partnatts; cnt++) { @@ -1726,18 +1725,37 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, switch (jointype) { + /* + * Join relation resulting from an INNER join may be regarded as + * partitioned by either of inner and outer relation keys. For + * example, A INNER JOIN B ON A.a = B.b can be regarded as + * partitioned on either A.a or B.b. + */ case JOIN_INNER: partexpr = list_concat(outer_expr, inner_expr); nullable_partexpr = list_concat(outer_null_expr, inner_null_expr); break; + /* + * Join relation resulting from a SEMI or ANTI join may be + * regarded as partitioned on the outer relation keys, since the + * inner columns are omitted from the output. + */ case JOIN_SEMI: case JOIN_ANTI: partexpr = outer_expr; nullable_partexpr = outer_null_expr; break; + /* + * Join relation resulting from a LEFT OUTER JOIN likewise may be + * regarded as partitioned on the (non-nullable) outer relation + * keys. The nullability of inner relation keys prevents them to + * be considered partition keys of the join relation in all cases, + * but they are okay as partition keys for further joins that + * involve strict join operators. + */ case JOIN_LEFT: partexpr = outer_expr; nullable_partexpr = list_concat(inner_expr, @@ -1746,6 +1764,12 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, inner_null_expr); break; + /* + * For FULL OUTER JOINs, both relations are nullable, so the + * resulting join relation may be regarded as partitioned on + * either of inner and outer relation keys, but only for joins + * that involve strict join operators. + */ case JOIN_FULL: nullable_partexpr = list_concat(outer_expr, inner_expr); diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 441e64eca9..3648fc8d3c 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -582,16 +582,32 @@ typedef struct PartitionSchemeData *PartitionScheme; * this relation that are partitioned tables * themselves, in hierarchical order * - * Note: A base relation always has only one set of partition keys, but a join - * relation may have as many sets of partition keys as the number of relations - * being joined. partexprs and nullable_partexprs are arrays containing - * part_scheme->partnatts elements each. Each of these elements is a list of - * partition key expressions. For a base relation each list in partexprs - * contains only one expression and nullable_partexprs is not populated. For a - * join relation, partexprs and nullable_partexprs contain partition key - * expressions from non-nullable and nullable relations resp. Lists at any - * given position in those arrays together contain as many elements as the - * number of joining relations. + * Notes on partition key expressions (partexprs and nullable_partexprs): + * + * Partition key expressions will be used to spot references to the partition + * keys of the relation in the expressions of a given query so as to apply + * various partitioning-based optimizations to certain query constructs. For + * example, pruning unnecessary partitions of a table using baserestrictinfo + * clauses that contain partition keys, converting a join between two + * partitioned relations into a series of joins between pairs of their + * constituent partitions if the joined rows follow the same partitioning + * as the relations being joined. + * + * The partexprs and nullable_partexprs arrays each contain + * part_scheme->partnatts elements. Each of the elements is a list of + * partition key expressions. For partitioned *base* relations, there is one + * expression in every list, whereas for partitioned *join* relations, there + * can be as many as the number of component relations. + * + * nullable_partexprs are populated only in partitioned *join* relationss, + * that is, if any of their component relations are nullable due to OUTER JOIN + * considerations. It contains only the expressions of the nullable component + * relations, while those of the non-nullable relations are present in the + * partexprs. For the considerations of partitionwise join, nullable partition + * keys can be considered to partition the underlying relation in the same + * manner as the non-nullable partition keys do, as long as the join operator + * is stable, because those null-valued keys can't be joined further, thus + * preserving the partitioning. *---------- */ typedef enum RelOptKind diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 1296edcdae..885f754f10 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -2003,3 +2003,203 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = Filter: (b = 0) (16 rows) +-- N-way join consisting of 2 or more full joins +DROP TABLE prt1_n_p2; +CREATE TABLE prt1_n_p2 PARTITION OF prt1_n FOR VALUES FROM ('0250') TO ('0500') PARTITION BY RANGE (c); +CREATE TABLE prt1_n_p2_1 PARTITION OF prt1_n_p2 FOR VALUES FROM ('0250') TO ('0350'); +CREATE TABLE prt1_n_p2_2 PARTITION OF prt1_n_p2 FOR VALUES FROM ('0350') TO ('0500'); +INSERT INTO prt1_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(250, 499, 2) i; +ANALYZE prt1_n; +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; + QUERY PLAN +------------------------------------------------------------------------------------------------------ + Limit + -> Sort + Sort Key: (COALESCE(COALESCE(COALESCE(t1.c, t2.c), t3.c), t4.c)) + -> Append + -> Hash Full Join + Hash Cond: ((COALESCE(COALESCE(t1.c, t2.c), t3.c))::text = (t4.c)::text) + -> Hash Full Join + Hash Cond: ((COALESCE(t1.c, t2.c))::text = (t3.c)::text) + -> Hash Full Join + Hash Cond: ((t1.c)::text = (t2.c)::text) + -> Seq Scan on prt1_n_p1 t1 + -> Hash + -> Seq Scan on prt1_n_p1 t2 + -> Hash + -> Seq Scan on prt1_n_p1 t3 + -> Hash + -> Seq Scan on prt1_n_p1 t4 + -> Hash Full Join + Hash Cond: ((COALESCE(COALESCE(t1_1.c, t2_1.c), t3_1.c))::text = (t4_1.c)::text) + -> Hash Full Join + Hash Cond: ((COALESCE(t1_1.c, t2_1.c))::text = (t3_1.c)::text) + -> Hash Full Join + Hash Cond: ((t1_1.c)::text = (t2_1.c)::text) + -> Seq Scan on prt1_n_p2_1 t1_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t2_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t3_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t4_1 + -> Hash Full Join + Hash Cond: ((COALESCE(COALESCE(t1_2.c, t2_2.c), t3_2.c))::text = (t4_2.c)::text) + -> Hash Full Join + Hash Cond: ((COALESCE(t1_2.c, t2_2.c))::text = (t3_2.c)::text) + -> Hash Full Join + Hash Cond: ((t1_2.c)::text = (t2_2.c)::text) + -> Seq Scan on prt1_n_p2_2 t1_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t2_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t3_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t4_2 +(43 rows) + +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; + c | a | b | a | b | a | b | a | b +------+---+---+---+---+---+---+---+--- + 0000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 + 0002 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 + 0004 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 + 0006 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 + 0008 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 +(5 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; + QUERY PLAN +------------------------------------------------------------------------------------ + Limit + -> Sort + Sort Key: (COALESCE(COALESCE(t1.c, t3.c), t4.c)) + -> Append + -> Hash Full Join + Hash Cond: ((COALESCE(t1.c, t3.c))::text = (t4.c)::text) + -> Hash Full Join + Hash Cond: ((t1.c)::text = (t3.c)::text) + -> Hash Left Join + Hash Cond: ((t1.c)::text = (t2.c)::text) + -> Seq Scan on prt1_n_p1 t1 + -> Hash + -> Seq Scan on prt1_n_p1 t2 + -> Hash + -> Seq Scan on prt1_n_p1 t3 + -> Hash + -> Seq Scan on prt1_n_p1 t4 + -> Hash Full Join + Hash Cond: ((COALESCE(t1_1.c, t3_1.c))::text = (t4_1.c)::text) + -> Hash Full Join + Hash Cond: ((t1_1.c)::text = (t3_1.c)::text) + -> Hash Left Join + Hash Cond: ((t1_1.c)::text = (t2_1.c)::text) + -> Seq Scan on prt1_n_p2_1 t1_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t2_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t3_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t4_1 + -> Hash Full Join + Hash Cond: ((COALESCE(t1_2.c, t3_2.c))::text = (t4_2.c)::text) + -> Hash Full Join + Hash Cond: ((t1_2.c)::text = (t3_2.c)::text) + -> Hash Left Join + Hash Cond: ((t1_2.c)::text = (t2_2.c)::text) + -> Seq Scan on prt1_n_p2_2 t1_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t2_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t3_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t4_2 +(43 rows) + +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; + c | a | b | a | b | a | b | a | b +------+---+---+---+---+---+---+---+--- + 0000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 + 0002 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 + 0004 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 + 0006 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 + 0008 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 +(5 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; + QUERY PLAN +------------------------------------------------------------------------------------ + Limit + -> Sort + Sort Key: (COALESCE(COALESCE(t1.c, t3.c), t4.c)) + -> Append + -> Hash Full Join + Hash Cond: ((COALESCE(t1.c, t3.c))::text = (t4.c)::text) + -> Hash Full Join + Hash Cond: ((t1.c)::text = (t3.c)::text) + -> Hash Join + Hash Cond: ((t1.c)::text = (t2.c)::text) + -> Seq Scan on prt1_n_p1 t1 + -> Hash + -> Seq Scan on prt1_n_p1 t2 + -> Hash + -> Seq Scan on prt1_n_p1 t3 + -> Hash + -> Seq Scan on prt1_n_p1 t4 + -> Hash Full Join + Hash Cond: ((COALESCE(t1_1.c, t3_1.c))::text = (t4_1.c)::text) + -> Hash Full Join + Hash Cond: ((t1_1.c)::text = (t3_1.c)::text) + -> Hash Join + Hash Cond: ((t1_1.c)::text = (t2_1.c)::text) + -> Seq Scan on prt1_n_p2_1 t1_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t2_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t3_1 + -> Hash + -> Seq Scan on prt1_n_p2_1 t4_1 + -> Hash Full Join + Hash Cond: ((COALESCE(t1_2.c, t3_2.c))::text = (t4_2.c)::text) + -> Hash Full Join + Hash Cond: ((t1_2.c)::text = (t3_2.c)::text) + -> Hash Join + Hash Cond: ((t1_2.c)::text = (t2_2.c)::text) + -> Seq Scan on prt1_n_p2_2 t1_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t2_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t3_2 + -> Hash + -> Seq Scan on prt1_n_p2_2 t4_2 +(43 rows) + +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; + c | a | b | a | b | a | b | a | b +------+---+---+---+---+---+---+---+--- + 0000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 + 0002 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 + 0004 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 + 0006 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 + 0008 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 +(5 rows) + +SET enable_hashjoin TO off; +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +ERROR: could not find pathkey item to sort +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +ERROR: could not find pathkey item to sort +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +ERROR: could not find pathkey item to sort +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +ERROR: could not find pathkey item to sort +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +ERROR: could not find pathkey item to sort +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +ERROR: could not find pathkey item to sort diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index db9a6b4a96..97ec983cec 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -270,6 +270,7 @@ EXPLAIN (COSTS OFF) SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; + -- test default partition behavior for range ALTER TABLE prt1 DETACH PARTITION prt1_p3; ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT; @@ -435,3 +436,31 @@ ANALYZE prt2; EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; + +-- N-way join consisting of 2 or more full joins +DROP TABLE prt1_n_p2; +CREATE TABLE prt1_n_p2 PARTITION OF prt1_n FOR VALUES FROM ('0250') TO ('0500') PARTITION BY RANGE (c); +CREATE TABLE prt1_n_p2_1 PARTITION OF prt1_n_p2 FOR VALUES FROM ('0250') TO ('0350'); +CREATE TABLE prt1_n_p2_2 PARTITION OF prt1_n_p2 FOR VALUES FROM ('0350') TO ('0500'); +INSERT INTO prt1_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(250, 499, 2) i; +ANALYZE prt1_n; + +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +SET enable_hashjoin TO off; +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +SELECT * FROM prt1_n t1 FULL JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +SELECT * FROM prt1_n t1 LEFT JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; +SELECT * FROM prt1_n t1 INNER JOIN prt1_n t2 USING (c) FULL JOIN prt1_n t3 USING (c) FULL JOIN prt1_n t4 USING (c) ORDER BY c LIMIT 5; -- 2.11.0