commit 347b5fb29ed9448a85bb6ea067718ca0a34c86a6 Author: Tom Lane Date: Wed Nov 16 15:52:51 2022 -0500 Detect duplicated pushed-down conditions using RestrictInfo ID numbers. create_nestloop_path needs to identify which candidates for join restriction quals were already enforced in the parameterized inner path. Currently we do that by relying on join_clause_is_movable_into to give consistent answers, but that is not working very well with variant clauses generated to satisfy outer join identity 3. We may have a clause that (correctly) shows the outer-side Var as nulled by a previous outer join, which makes it dependent on the nestloop outer side having included that join, so that it appears to not be pushable into a parameterized path that uses the un-nulled version of that Var. Nonetheless, the cloned clause *is* redundant and we don't want to check it again. This patch offers a somewhat brute-force solution, which is to assign serial numbers to RestrictInfo nodes, then check for redundancy using serial number match rather than trusting join_clause_is_movable_into. The variant-clause problem can be solved by allowing clauses to share a serial number when we know that they are equivalent. Both the outer-join variant generator and equivclass.c need to be in on that trick in order to handle all cases that were handled well before. It'd be nicer if we could continue to trust join_clause_is_movable_into for this, but on the other hand this mechanism does provide a much more concrete, harder-to-break way of verifying that we already enforced (some version of) a qual. Any failure mode would almost certainly be in the safe direction of enforcing a qual redundantly, which is not a claim that the existing method can make. This patch results in two changes to the core regression test outputs: * One query in join.sql changes to a different join order. Examining the cost estimates that are normally not shown, the new order is estimated as very slightly faster, so this seems like an improvement. I'm not quite sure why the old code did not find this join order. * Some of the queries in partition_join.sql revert equivalence-clause ordering back to what it was before a5fc46414. That's probably a consequence of investigating parameterized paths in a different order than before. Anyway, it's visibly harmless. diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 349e183372..d4f8b7893d 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -35,7 +35,8 @@ static EquivalenceMember *add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids, Relids nullable_relids, - bool is_child, Oid datatype); + EquivalenceMember *parent, + Oid datatype); static bool is_exprlist_member(Expr *node, List *exprs); static void generate_base_implied_equalities_const(PlannerInfo *root, EquivalenceClass *ec); @@ -400,7 +401,7 @@ process_equivalence(PlannerInfo *root, { /* Case 3: add item2 to ec1 */ em2 = add_eq_member(ec1, item2, item2_relids, item2_nullable_relids, - false, item2_type); + NULL, item2_type); ec1->ec_sources = lappend(ec1->ec_sources, restrictinfo); ec1->ec_below_outer_join |= below_outer_join; ec1->ec_min_security = Min(ec1->ec_min_security, @@ -418,7 +419,7 @@ process_equivalence(PlannerInfo *root, { /* Case 3: add item1 to ec2 */ em1 = add_eq_member(ec2, item1, item1_relids, item1_nullable_relids, - false, item1_type); + NULL, item1_type); ec2->ec_sources = lappend(ec2->ec_sources, restrictinfo); ec2->ec_below_outer_join |= below_outer_join; ec2->ec_min_security = Min(ec2->ec_min_security, @@ -452,9 +453,9 @@ process_equivalence(PlannerInfo *root, ec->ec_max_security = restrictinfo->security_level; ec->ec_merged = NULL; em1 = add_eq_member(ec, item1, item1_relids, item1_nullable_relids, - false, item1_type); + NULL, item1_type); em2 = add_eq_member(ec, item2, item2_relids, item2_nullable_relids, - false, item2_type); + NULL, item2_type); root->eq_classes = lappend(root->eq_classes, ec); @@ -544,7 +545,7 @@ canonicalize_ec_expression(Expr *expr, Oid req_type, Oid req_collation) */ static EquivalenceMember * add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids, - Relids nullable_relids, bool is_child, Oid datatype) + Relids nullable_relids, EquivalenceMember *parent, Oid datatype) { EquivalenceMember *em = makeNode(EquivalenceMember); @@ -552,8 +553,9 @@ add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids, em->em_relids = relids; em->em_nullable_relids = nullable_relids; em->em_is_const = false; - em->em_is_child = is_child; + em->em_is_child = (parent != NULL); em->em_datatype = datatype; + em->em_parent = parent; if (bms_is_empty(relids)) { @@ -565,12 +567,12 @@ add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids, * get_eclass_for_sort_expr() has to work harder. We put the tests * there not here to save cycles in the equivalence case. */ - Assert(!is_child); + Assert(!parent); em->em_is_const = true; ec->ec_has_const = true; /* it can't affect ec_relids */ } - else if (!is_child) /* child members don't add to ec_relids */ + else if (!parent) /* child members don't add to ec_relids */ { ec->ec_relids = bms_add_members(ec->ec_relids, relids); } @@ -723,7 +725,7 @@ get_eclass_for_sort_expr(PlannerInfo *root, nullable_relids = bms_intersect(nullable_relids, expr_relids); newem = add_eq_member(newec, copyObject(expr), expr_relids, - nullable_relids, false, opcintype); + nullable_relids, NULL, opcintype); /* * add_eq_member doesn't check for volatile functions, set-returning @@ -1821,6 +1823,7 @@ create_join_clause(PlannerInfo *root, EquivalenceClass *parent_ec) { RestrictInfo *rinfo; + RestrictInfo *parent_rinfo = NULL; ListCell *lc; MemoryContext oldcontext; @@ -1865,6 +1868,20 @@ create_join_clause(PlannerInfo *root, */ oldcontext = MemoryContextSwitchTo(root->planner_cxt); + /* + * If either EM is a child, recursively create the corresponding + * parent-to-parent clause, so that we can duplicate its rinfo_serial. + */ + if (leftem->em_is_child || rightem->em_is_child) + { + EquivalenceMember *leftp = leftem->em_parent ? leftem->em_parent : leftem; + EquivalenceMember *rightp = rightem->em_parent ? rightem->em_parent : rightem; + + parent_rinfo = create_join_clause(root, ec, opno, + leftp, rightp, + parent_ec); + } + rinfo = build_implied_join_equality(root, opno, ec->ec_collation, @@ -1876,6 +1893,10 @@ create_join_clause(PlannerInfo *root, rightem->em_nullable_relids), ec->ec_min_security); + /* If it's a child clause, copy the parent's rinfo_serial */ + if (parent_rinfo) + rinfo->rinfo_serial = parent_rinfo->rinfo_serial; + /* Mark the clause as redundant, or not */ rinfo->parent_ec = parent_ec; @@ -2686,7 +2707,7 @@ add_child_rel_equivalences(PlannerInfo *root, (void) add_eq_member(cur_ec, child_expr, new_relids, new_nullable_relids, - true, cur_em->em_datatype); + cur_em, cur_em->em_datatype); /* Record this EC index for the child rel */ child_rel->eclass_indexes = bms_add_member(child_rel->eclass_indexes, i); @@ -2827,7 +2848,7 @@ add_child_join_rel_equivalences(PlannerInfo *root, (void) add_eq_member(cur_ec, child_expr, new_relids, new_nullable_relids, - true, cur_em->em_datatype); + cur_em, cur_em->em_datatype); } } } diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index 516984c655..a128780857 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -1783,6 +1783,7 @@ process_postponed_left_join_quals(PlannerInfo *root) Relids joins_below; Relids joins_so_far; List *quals; + int save_last_rinfo_serial; ListCell *lc2; /* @@ -1821,6 +1822,16 @@ process_postponed_left_join_quals(PlannerInfo *root) joins_below, NULL); + /* + * Each time we produce RestrictInfo(s) from these quals, reset + * the last_rinfo_serial counter, so that the RestrictInfos for + * the "same" qual condition get identical serial numbers. (This + * relies on the fact that we're not changing the qual list in any + * way that'd affect the number of RestrictInfos built from it.) + * This'll allow us to detect duplicative qual usage later. + */ + save_last_rinfo_serial = root->last_rinfo_serial; + joins_so_far = NULL; foreach(lc2, join_info_list_orig) { @@ -1854,6 +1865,9 @@ process_postponed_left_join_quals(PlannerInfo *root) continue; } + /* Reset serial counter for this version of the quals */ + root->last_rinfo_serial = save_last_rinfo_serial; + /* * When we are looking at joins above sjinfo, we are * envisioning pushing sjinfo to above othersj, so add diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index e743a5d9fe..d52c2a3595 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -620,6 +620,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, root->multiexpr_params = NIL; root->eq_classes = NIL; root->ec_merging_done = false; + root->last_rinfo_serial = 0; root->all_result_relids = parse->resultRelation ? bms_make_singleton(parse->resultRelation) : NULL; root->leaf_result_relids = NULL; /* we'll find out leaf-ness later */ diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 68fb712472..fc56a81be8 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -990,6 +990,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, subroot->multiexpr_params = NIL; subroot->eq_classes = NIL; subroot->ec_merging_done = false; + subroot->last_rinfo_serial = 0; subroot->all_result_relids = NULL; subroot->leaf_result_relids = NULL; subroot->append_rel_list = NIL; diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c index 11c6bbaba6..e18d64b6dc 100644 --- a/src/backend/optimizer/util/appendinfo.c +++ b/src/backend/optimizer/util/appendinfo.c @@ -427,7 +427,7 @@ adjust_appendrel_attrs_mutator(Node *node, RestrictInfo *oldinfo = (RestrictInfo *) node; RestrictInfo *newinfo = makeNode(RestrictInfo); - /* Copy all flat-copiable fields */ + /* Copy all flat-copiable fields, notably including rinfo_serial */ memcpy(newinfo, oldinfo, sizeof(RestrictInfo)); /* Recursively fix the clause itself */ diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index bf35d1989c..c77399ca92 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -2442,12 +2442,12 @@ create_nestloop_path(PlannerInfo *root, * restrict_clauses that are due to be moved into the inner path. We have * to do this now, rather than postpone the work till createplan time, * because the restrict_clauses list can affect the size and cost - * estimates for this path. + * estimates for this path. We detect such clauses by checking for serial + * number match to clauses already enforced in the inner path. */ if (bms_overlap(inner_req_outer, outer_path->parent->relids)) { - Relids inner_and_outer = bms_union(inner_path->parent->relids, - inner_req_outer); + Bitmapset *enforced_serials = get_param_path_clause_serials(inner_path); List *jclauses = NIL; ListCell *lc; @@ -2455,9 +2455,7 @@ create_nestloop_path(PlannerInfo *root, { RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc); - if (!join_clause_is_movable_into(rinfo, - inner_path->parent->relids, - inner_and_outer)) + if (!bms_is_member(rinfo->rinfo_serial, enforced_serials)) jclauses = lappend(jclauses, rinfo); } restrict_clauses = jclauses; @@ -4268,6 +4266,7 @@ do { \ new_ppi->ppi_rows = old_ppi->ppi_rows; new_ppi->ppi_clauses = old_ppi->ppi_clauses; ADJUST_CHILD_ATTRS(new_ppi->ppi_clauses); + new_ppi->ppi_serials = bms_copy(old_ppi->ppi_serials); rel->ppilist = lappend(rel->ppilist, new_ppi); MemoryContextSwitchTo(oldcontext); diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 84e5e8db7b..38540e6331 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -1458,6 +1458,7 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel, ParamPathInfo *ppi; Relids joinrelids; List *pclauses; + Bitmapset *pserials; double rows; ListCell *lc; @@ -1500,6 +1501,15 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel, required_outer, baserel)); + /* Compute set of serial numbers of the enforced clauses */ + pserials = NULL; + foreach(lc, pclauses) + { + RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc); + + pserials = bms_add_member(pserials, rinfo->rinfo_serial); + } + /* Estimate the number of rows returned by the parameterized scan */ rows = get_parameterized_baserel_size(root, baserel, pclauses); @@ -1508,6 +1518,7 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel, ppi->ppi_req_outer = required_outer; ppi->ppi_rows = rows; ppi->ppi_clauses = pclauses; + ppi->ppi_serials = pserials; baserel->ppilist = lappend(baserel->ppilist, ppi); return ppi; @@ -1733,6 +1744,7 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo *joinrel, ppi->ppi_req_outer = required_outer; ppi->ppi_rows = rows; ppi->ppi_clauses = NIL; + ppi->ppi_serials = NULL; joinrel->ppilist = lappend(joinrel->ppilist, ppi); return ppi; @@ -1771,6 +1783,7 @@ get_appendrel_parampathinfo(RelOptInfo *appendrel, Relids required_outer) ppi->ppi_req_outer = required_outer; ppi->ppi_rows = 0; ppi->ppi_clauses = NIL; + ppi->ppi_serials = NULL; appendrel->ppilist = lappend(appendrel->ppilist, ppi); return ppi; @@ -1796,6 +1809,100 @@ find_param_path_info(RelOptInfo *rel, Relids required_outer) return NULL; } +/* + * get_param_path_clause_serials + * Given a parameterized Path, return the set of pushed-down clauses + * (identified by rinfo_serial numbers) enforced within the Path. + */ +Bitmapset * +get_param_path_clause_serials(Path *path) +{ + if (path->param_info == NULL) + return NULL; /* not parameterized */ + if (IsA(path, NestPath) || + IsA(path, MergePath) || + IsA(path, HashPath)) + { + /* + * For a join path, combine clauses enforced within either input path + * with those enforced as joinrestrictinfo in this path. Note that + * joinrestrictinfo may include some non-pushed-down clauses, but for + * current purposes it's okay if we include those in the result. (To + * be more careful, we could check for clause_relids overlapping the + * path parameterization, but it's not worth the cycles for now.) + */ + JoinPath *jpath = (JoinPath *) path; + Bitmapset *pserials; + ListCell *lc; + + pserials = NULL; + pserials = bms_add_members(pserials, + get_param_path_clause_serials(jpath->outerjoinpath)); + pserials = bms_add_members(pserials, + get_param_path_clause_serials(jpath->innerjoinpath)); + foreach(lc, jpath->joinrestrictinfo) + { + RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc); + + pserials = bms_add_member(pserials, rinfo->rinfo_serial); + } + return pserials; + } + else if (IsA(path, AppendPath)) + { + /* + * For an appendrel, take the intersection of the sets of clauses + * enforced in each input path. + */ + AppendPath *apath = (AppendPath *) path; + Bitmapset *pserials; + ListCell *lc; + + pserials = NULL; + foreach(lc, apath->subpaths) + { + Path *subpath = (Path *) lfirst(lc); + Bitmapset *subserials; + + subserials = get_param_path_clause_serials(subpath); + if (lc == list_head(apath->subpaths)) + pserials = bms_copy(subserials); + else + pserials = bms_int_members(pserials, subserials); + } + return pserials; + } + else if (IsA(path, MergeAppendPath)) + { + /* Same as AppendPath case */ + MergeAppendPath *apath = (MergeAppendPath *) path; + Bitmapset *pserials; + ListCell *lc; + + pserials = NULL; + foreach(lc, apath->subpaths) + { + Path *subpath = (Path *) lfirst(lc); + Bitmapset *subserials; + + subserials = get_param_path_clause_serials(subpath); + if (lc == list_head(apath->subpaths)) + pserials = bms_copy(subserials); + else + pserials = bms_int_members(pserials, subserials); + } + return pserials; + } + else + { + /* + * Otherwise, it's a baserel path and we can use the + * previously-computed set of serial numbers. + */ + return path->param_info->ppi_serials; + } +} + /* * build_joinrel_partition_info * Checks if the two relations being joined can use partitionwise join diff --git a/src/backend/optimizer/util/restrictinfo.c b/src/backend/optimizer/util/restrictinfo.c index 327c3ba563..bcbee8f943 100644 --- a/src/backend/optimizer/util/restrictinfo.c +++ b/src/backend/optimizer/util/restrictinfo.c @@ -208,6 +208,11 @@ make_restrictinfo_internal(PlannerInfo *root, restrictinfo->num_base_rels = bms_num_members(baserels); bms_free(baserels); + /* + * Label this RestrictInfo with a fresh serial number. + */ + restrictinfo->rinfo_serial = ++(root->last_rinfo_serial); + /* * Fill in all the cacheable fields with "not yet set" markers. None of * these will be computed until/unless needed. Note in particular that we @@ -371,7 +376,7 @@ commute_restrictinfo(RestrictInfo *rinfo, Oid comm_op) * ... and adjust those we need to change. Note in particular that we can * preserve any cached selectivity or cost estimates, since those ought to * be the same for the new clause. Likewise we can keep the source's - * parent_ec. + * parent_ec. It's also important that we keep the same rinfo_serial. */ result->clause = (Expr *) newclause; result->left_relids = rinfo->right_relids; diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 2ddd245992..c8538bbd67 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -339,6 +339,9 @@ struct PlannerInfo /* list of SpecialJoinInfos */ List *join_info_list; + /* counter for assigning RestrictInfo serial numbers */ + int last_rinfo_serial; + /* * all_result_relids is empty for SELECT, otherwise it contains at least * parse->resultRelation. For UPDATE/DELETE/MERGE across an inheritance @@ -1354,6 +1357,8 @@ typedef struct EquivalenceMember bool em_is_const; /* expression is pseudoconstant? */ bool em_is_child; /* derived version for a child relation? */ Oid em_datatype; /* the "nominal type" used by the opfamily */ + /* if em_is_child is true, this links to corresponding EM for top parent */ + struct EquivalenceMember *em_parent pg_node_attr(read_write_ignore); } EquivalenceMember; /* @@ -1459,7 +1464,13 @@ typedef struct PathTarget * Note: ppi_clauses is only used in ParamPathInfos for base relation paths; * in join cases it's NIL because the set of relevant clauses varies depending * on how the join is formed. The relevant clauses will appear in each - * parameterized join path's joinrestrictinfo list, instead. + * parameterized join path's joinrestrictinfo list, instead. ParamPathInfos + * for append relations don't bother with this, either. + * + * ppi_serials is the set of rinfo_serial numbers for quals that are enforced + * by this path. As with ppi_clauses, it's only maintained for baserels. + * (We could construct it on-the-fly from ppi_clauses, but it seems better + * to materialize a copy.) */ typedef struct ParamPathInfo { @@ -1470,6 +1481,7 @@ typedef struct ParamPathInfo Relids ppi_req_outer; /* rels supplying parameters used by path */ Cardinality ppi_rows; /* estimated number of result tuples */ List *ppi_clauses; /* join clauses available from outer rels */ + Bitmapset *ppi_serials; /* set of rinfo_serial for enforced quals */ } ParamPathInfo; @@ -2499,6 +2511,25 @@ typedef struct RestrictInfo */ Expr *orclause pg_node_attr(equal_ignore); + /*---------- + * Serial number of this RestrictInfo. This is unique within the current + * PlannerInfo context, with a few critical exceptions: + * 1. When we generate multiple clones of the same qual condition to + * cope with outer join identity 3, all the clones get the same serial + * number. This reflects that we only want to apply one of them in any + * given plan. + * 2. If we manufacture a commuted version of a qual to use as an index + * condition, it copies the original's rinfo_serial, since it is in + * practice the same condition. + * 3. RestrictInfos made for a child relation copy their parent's + * rinfo_serial. Likewise, when an EquivalenceClass makes a derived + * equality clause for a child relation, it copies the rinfo_serial of + * the matching equality clause for the parent. This allows detection + * of redundant pushed-down equality clauses. + *---------- + */ + int rinfo_serial; + /* * Generating EquivalenceClass. This field is NULL unless clause is * potentially redundant. diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 197234d44c..3440455a2e 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -333,6 +333,7 @@ extern ParamPathInfo *get_appendrel_parampathinfo(RelOptInfo *appendrel, Relids required_outer); extern ParamPathInfo *find_param_path_info(RelOptInfo *rel, Relids required_outer); +extern Bitmapset *get_param_path_clause_serials(Path *path); extern RelOptInfo *build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, RelOptInfo *inner_rel, RelOptInfo *parent_joinrel, List *restrictlist, diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 9358371072..00f4c58238 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2335,17 +2335,17 @@ select a.f1, b.f1, t.thousand, t.tenthous from (select sum(f1)+1 as f1 from int4_tbl i4a) a, (select sum(f1) as f1 from int4_tbl i4b) b where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------- Nested Loop - -> Aggregate - -> Seq Scan on int4_tbl i4b -> Nested Loop Join Filter: ((sum(i4b.f1)) = ((sum(i4a.f1) + 1))) -> Aggregate -> Seq Scan on int4_tbl i4a - -> Index Only Scan using tenk1_thous_tenthous on tenk1 t - Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999))) + -> Aggregate + -> Seq Scan on int4_tbl i4b + -> Index Only Scan using tenk1_thous_tenthous on tenk1 t + Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999))) (9 rows) select a.f1, b.f1, t.thousand, t.tenthous from diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index b20facc19f..bb5b7c47a4 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -304,7 +304,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) -> Seq Scan on prt2_p2 t2_2 Filter: (a = 0) -> Nested Loop Semi Join - Join Filter: (t2_3.b = t1_3.a) + Join Filter: (t1_3.a = t2_3.b) -> Seq Scan on prt1_p3 t1_3 Filter: (b = 0) -> Materialize @@ -601,7 +601,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t Sort Key: t1.a -> Append -> Nested Loop - Join Filter: (((t3_1.a + t3_1.b) / 2) = t1_1.a) + Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2)) -> Hash Join Hash Cond: (t2_1.b = t1_1.a) -> Seq Scan on prt2_p1 t2_1 @@ -611,7 +611,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3_1 Index Cond: (((a + b) / 2) = t2_1.b) -> Nested Loop - Join Filter: (((t3_2.a + t3_2.b) / 2) = t1_2.a) + Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2)) -> Hash Join Hash Cond: (t2_2.b = t1_2.a) -> Seq Scan on prt2_p2 t2_2 @@ -621,7 +621,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_2 Index Cond: (((a + b) / 2) = t2_2.b) -> Nested Loop - Join Filter: (((t3_3.a + t3_3.b) / 2) = t1_3.a) + Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2)) -> Hash Join Hash Cond: (t2_3.b = t1_3.a) -> Seq Scan on prt2_p3 t2_3 @@ -926,7 +926,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER Sort Key: t1.a -> Append -> Nested Loop - Join Filter: (t1_5.b = t1_2.a) + Join Filter: (t1_2.a = t1_5.b) -> HashAggregate Group Key: t1_5.b -> Hash Join @@ -939,7 +939,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER Index Cond: (a = ((t2_1.a + t2_1.b) / 2)) Filter: (b = 0) -> Nested Loop - Join Filter: (t1_6.b = t1_3.a) + Join Filter: (t1_3.a = t1_6.b) -> HashAggregate Group Key: t1_6.b -> Hash Join @@ -952,7 +952,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER Index Cond: (a = ((t2_2.a + t2_2.b) / 2)) Filter: (b = 0) -> Nested Loop - Join Filter: (t1_7.b = t1_4.a) + Join Filter: (t1_4.a = t1_7.b) -> HashAggregate Group Key: t1_7.b -> Nested Loop