Michael Paquier <michael.paquier@gmail.com> writes:
> Attached is an improved version of this patch with better names for
> the new functions and variables. I added more comments at the same
> time.
After studying this a bit more, I've gotten disillusioned with the
idea of precalculating the top_parent_relid for an AppendRelInfo.
1. It's too complicated to maintain. Your patch misses out doing
something when expand_inherited_rtentry() creates a child rel of
a rel that's already somebody else's child, and much harder to fix,
it misses out doing something when pull_up_simple_subquery() merges
a child query's append_rel_list with the parent query's. That
action could well result in some of the pulled-up child AppendRelInfos
now being children of pre-existing parent AppendRelInfos.
These things could probably be fixed, but:
2. There's no evidence that we actually have a performance problem we
need to fix in this area. Multilevel parentage is pretty rare, else
we'd have noticed problems here before. By the time we fix #1, we could
easily waste more cycles maintaining the data structure than we save.
3. Keeping the topmost parent relid only helps in some places anyhow.
In particular, in generate_join_implied_equalities_broken, we really
have to apply all the translation steps down from the top rel. (It
took me several hours of fooling around to generate a test case for
this ... but the attached patch includes a new regression test file
that exercises that code, and it shows a failure both with HEAD and
with your patch.)
4. Also, in generate_implied_equalities_for_column and
check_partial_indexes, it seems prudent to me to exclude all appendrel
parents of the child we're considering, not only the topmost. This is
probably moot at the moment but it might not be so forever, in particular
if we ever get around to fixing the problem that sub-SELECTs containing
WHERE clauses can't be pulled up as appendrels. (That'd result in WHERE
clauses associated with intermediate-level appendrels, and I think that
might lead us to generate bogus join paths in the same way as the current
problem does.)
Accordingly, I propose a patch more like the attached. This doesn't
try to change the data structures, but it does take the viewpoint that
all current callers of find_childrel_appendrelinfo() need to be fixed
to explicitly consider multiple levels of parent appendrels.
regards, tom lane
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index b7aff37..e5dd58e 100644
*** a/src/backend/optimizer/path/equivclass.c
--- b/src/backend/optimizer/path/equivclass.c
*************** static List *generate_join_implied_equal
*** 48,54 ****
Relids nominal_join_relids,
Relids outer_relids,
Relids nominal_inner_relids,
! AppendRelInfo *inner_appinfo);
static Oid select_equality_operator(EquivalenceClass *ec,
Oid lefttype, Oid righttype);
static RestrictInfo *create_join_clause(PlannerInfo *root,
--- 48,54 ----
Relids nominal_join_relids,
Relids outer_relids,
Relids nominal_inner_relids,
! RelOptInfo *inner_rel);
static Oid select_equality_operator(EquivalenceClass *ec,
Oid lefttype, Oid righttype);
static RestrictInfo *create_join_clause(PlannerInfo *root,
*************** generate_join_implied_equalities(Planner
*** 1000,1021 ****
Relids inner_relids = inner_rel->relids;
Relids nominal_inner_relids;
Relids nominal_join_relids;
- AppendRelInfo *inner_appinfo;
ListCell *lc;
/* If inner rel is a child, extra setup work is needed */
if (inner_rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
{
! /* Lookup parent->child translation data */
! inner_appinfo = find_childrel_appendrelinfo(root, inner_rel);
! /* Construct relids for the parent rel */
! nominal_inner_relids = bms_make_singleton(inner_appinfo->parent_relid);
/* ECs will be marked with the parent's relid, not the child's */
nominal_join_relids = bms_union(outer_relids, nominal_inner_relids);
}
else
{
- inner_appinfo = NULL;
nominal_inner_relids = inner_relids;
nominal_join_relids = join_relids;
}
--- 1000,1017 ----
Relids inner_relids = inner_rel->relids;
Relids nominal_inner_relids;
Relids nominal_join_relids;
ListCell *lc;
/* If inner rel is a child, extra setup work is needed */
if (inner_rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
{
! /* Fetch relid set for the topmost parent rel */
! nominal_inner_relids = find_childrel_top_parent(root, inner_rel)->relids;
/* ECs will be marked with the parent's relid, not the child's */
nominal_join_relids = bms_union(outer_relids, nominal_inner_relids);
}
else
{
nominal_inner_relids = inner_relids;
nominal_join_relids = join_relids;
}
*************** generate_join_implied_equalities(Planner
*** 1051,1057 ****
nominal_join_relids,
outer_relids,
nominal_inner_relids,
! inner_appinfo);
result = list_concat(result, sublist);
}
--- 1047,1053 ----
nominal_join_relids,
outer_relids,
nominal_inner_relids,
! inner_rel);
result = list_concat(result, sublist);
}
*************** generate_join_implied_equalities_broken(
*** 1244,1250 ****
Relids nominal_join_relids,
Relids outer_relids,
Relids nominal_inner_relids,
! AppendRelInfo *inner_appinfo)
{
List *result = NIL;
ListCell *lc;
--- 1240,1246 ----
Relids nominal_join_relids,
Relids outer_relids,
Relids nominal_inner_relids,
! RelOptInfo *inner_rel)
{
List *result = NIL;
ListCell *lc;
*************** generate_join_implied_equalities_broken(
*** 1266,1275 ****
* RestrictInfos that are not listed in ec_derives, but there shouldn't be
* any duplication, and it's a sufficiently narrow corner case that we
* shouldn't sweat too much over it anyway.
*/
! if (inner_appinfo)
! result = (List *) adjust_appendrel_attrs(root, (Node *) result,
! inner_appinfo);
return result;
}
--- 1262,1277 ----
* RestrictInfos that are not listed in ec_derives, but there shouldn't be
* any duplication, and it's a sufficiently narrow corner case that we
* shouldn't sweat too much over it anyway.
+ *
+ * Since inner_rel might be an indirect descendant of the baserel
+ * mentioned in the ec_sources clauses, we have to be prepared to apply
+ * multiple levels of Var translation.
*/
! if (inner_rel->reloptkind == RELOPT_OTHER_MEMBER_REL &&
! result != NIL)
! result = (List *) adjust_appendrel_attrs_multilevel(root,
! (Node *) result,
! inner_rel);
return result;
}
*************** generate_implied_equalities_for_column(P
*** 2071,2084 ****
{
List *result = NIL;
bool is_child_rel = (rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
! Index parent_relid;
ListCell *lc1;
! /* If it's a child rel, we'll need to know what its parent is */
if (is_child_rel)
! parent_relid = find_childrel_appendrelinfo(root, rel)->parent_relid;
else
! parent_relid = 0; /* not used, but keep compiler quiet */
foreach(lc1, root->eq_classes)
{
--- 2073,2086 ----
{
List *result = NIL;
bool is_child_rel = (rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
! Relids parent_relids;
ListCell *lc1;
! /* If it's a child rel, we'll need to know what its parent(s) are */
if (is_child_rel)
! parent_relids = find_childrel_parents(root, rel);
else
! parent_relids = NULL; /* not used, but keep compiler quiet */
foreach(lc1, root->eq_classes)
{
*************** generate_implied_equalities_for_column(P
*** 2148,2157 ****
/*
* Also, if this is a child rel, avoid generating a useless join
! * to its parent rel.
*/
if (is_child_rel &&
! bms_is_member(parent_relid, other_em->em_relids))
continue;
eq_op = select_equality_operator(cur_ec,
--- 2150,2159 ----
/*
* Also, if this is a child rel, avoid generating a useless join
! * to its parent rel(s).
*/
if (is_child_rel &&
! bms_overlap(parent_relids, other_em->em_relids))
continue;
eq_op = select_equality_operator(cur_ec,
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 42dcb11..9c22d31 100644
*** a/src/backend/optimizer/path/indxpath.c
--- b/src/backend/optimizer/path/indxpath.c
*************** check_partial_indexes(PlannerInfo *root,
*** 2586,2601 ****
* Add on any equivalence-derivable join clauses. Computing the correct
* relid sets for generate_join_implied_equalities is slightly tricky
* because the rel could be a child rel rather than a true baserel, and in
! * that case we must remove its parent's relid from all_baserels.
*/
if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
- {
- /* Lookup parent->child translation data */
- AppendRelInfo *appinfo = find_childrel_appendrelinfo(root, rel);
-
otherrels = bms_difference(root->all_baserels,
! bms_make_singleton(appinfo->parent_relid));
! }
else
otherrels = bms_difference(root->all_baserels, rel->relids);
--- 2586,2596 ----
* Add on any equivalence-derivable join clauses. Computing the correct
* relid sets for generate_join_implied_equalities is slightly tricky
* because the rel could be a child rel rather than a true baserel, and in
! * that case we must remove its parents' relid(s) from all_baserels.
*/
if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
otherrels = bms_difference(root->all_baserels,
! find_childrel_parents(root, rel));
else
otherrels = bms_difference(root->all_baserels, rel->relids);
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 0410fdd..58d5333 100644
*** a/src/backend/optimizer/prep/prepunion.c
--- b/src/backend/optimizer/prep/prepunion.c
*************** adjust_inherited_tlist(List *tlist, Appe
*** 1979,1981 ****
--- 1979,2002 ----
return new_tlist;
}
+
+ /*
+ * adjust_appendrel_attrs_multilevel
+ * Apply Var translations from a toplevel appendrel parent down to a child.
+ *
+ * In some cases we need to translate expressions referencing a baserel
+ * to reference an appendrel child that's multiple levels removed from it.
+ */
+ Node *
+ adjust_appendrel_attrs_multilevel(PlannerInfo *root, Node *node,
+ RelOptInfo *child_rel)
+ {
+ AppendRelInfo *appinfo = find_childrel_appendrelinfo(root, child_rel);
+ RelOptInfo *parent_rel = find_base_rel(root, appinfo->parent_relid);
+
+ /* If parent is also a child, first recurse to apply its translations */
+ if (parent_rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
+ node = adjust_appendrel_attrs_multilevel(root, node, parent_rel);
+ /* Now translate for this child */
+ return adjust_appendrel_attrs(root, node, appinfo);
+ }
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index c938c27..2594827 100644
*** a/src/backend/optimizer/util/relnode.c
--- b/src/backend/optimizer/util/relnode.c
*************** build_empty_join_rel(PlannerInfo *root)
*** 713,719 ****
* Get the AppendRelInfo associated with an appendrel child rel.
*
* This search could be eliminated by storing a link in child RelOptInfos,
! * but for now it doesn't seem performance-critical.
*/
AppendRelInfo *
find_childrel_appendrelinfo(PlannerInfo *root, RelOptInfo *rel)
--- 713,720 ----
* Get the AppendRelInfo associated with an appendrel child rel.
*
* This search could be eliminated by storing a link in child RelOptInfos,
! * but for now it doesn't seem performance-critical. (Also, it might be
! * difficult to maintain such a link during mutation of the append_rel_list.)
*/
AppendRelInfo *
find_childrel_appendrelinfo(PlannerInfo *root, RelOptInfo *rel)
*************** find_childrel_appendrelinfo(PlannerInfo
*** 738,743 ****
--- 739,796 ----
/*
+ * find_childrel_top_parent
+ * Fetch the topmost appendrel parent rel of an appendrel child rel.
+ *
+ * Since appendrels can be nested, a child could have multiple levels of
+ * appendrel ancestors. This function locates the topmost ancestor,
+ * which will be a regular baserel not an otherrel.
+ */
+ RelOptInfo *
+ find_childrel_top_parent(PlannerInfo *root, RelOptInfo *rel)
+ {
+ do
+ {
+ AppendRelInfo *appinfo = find_childrel_appendrelinfo(root, rel);
+ Index prelid = appinfo->parent_relid;
+
+ /* traverse up to the parent rel, loop if it's also a child rel */
+ rel = find_base_rel(root, prelid);
+ } while (rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
+
+ return rel;
+ }
+
+
+ /*
+ * find_childrel_parents
+ * Compute the set of parent relids of an appendrel child rel.
+ *
+ * Since appendrels can be nested, a child could have multiple levels of
+ * appendrel ancestors. This function computes a Relids set of all the
+ * parent relation IDs.
+ */
+ Relids
+ find_childrel_parents(PlannerInfo *root, RelOptInfo *rel)
+ {
+ Relids result = NULL;
+
+ do
+ {
+ AppendRelInfo *appinfo = find_childrel_appendrelinfo(root, rel);
+ Index prelid = appinfo->parent_relid;
+
+ result = bms_add_member(result, prelid);
+
+ /* traverse up to the parent rel, loop if it's also a child rel */
+ rel = find_base_rel(root, prelid);
+ } while (rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
+
+ return result;
+ }
+
+
+ /*
* get_baserel_parampathinfo
* Get the ParamPathInfo for a parameterized path for a base relation,
* constructing one if we don't have one already.
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index a0bcc82..26b17f5 100644
*** a/src/include/optimizer/pathnode.h
--- b/src/include/optimizer/pathnode.h
*************** extern RelOptInfo *build_join_rel(Planne
*** 145,150 ****
--- 145,152 ----
extern RelOptInfo *build_empty_join_rel(PlannerInfo *root);
extern AppendRelInfo *find_childrel_appendrelinfo(PlannerInfo *root,
RelOptInfo *rel);
+ extern RelOptInfo *find_childrel_top_parent(PlannerInfo *root, RelOptInfo *rel);
+ extern Relids find_childrel_parents(PlannerInfo *root, RelOptInfo *rel);
extern ParamPathInfo *get_baserel_parampathinfo(PlannerInfo *root,
RelOptInfo *baserel,
Relids required_outer);
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index f5fc7e8..1891f4d 100644
*** a/src/include/optimizer/prep.h
--- b/src/include/optimizer/prep.h
*************** extern void expand_inherited_tables(Plan
*** 58,61 ****
--- 58,64 ----
extern Node *adjust_appendrel_attrs(PlannerInfo *root, Node *node,
AppendRelInfo *appinfo);
+ extern Node *adjust_appendrel_attrs_multilevel(PlannerInfo *root, Node *node,
+ RelOptInfo *child_rel);
+
#endif /* PREP_H */
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index ...b882242 .
*** a/src/test/regress/expected/equivclass.out
--- b/src/test/regress/expected/equivclass.out
***************
*** 0 ****
--- 1,296 ----
+ --
+ -- Tests for the planner's "equivalence class" mechanism
+ --
+ -- One thing that's not tested well during normal querying is the logic
+ -- for handling "broken" ECs. This is because an EC can only become broken
+ -- if its underlying btree operator family doesn't include a complete set
+ -- of cross-type equality operators. There are not (and should not be)
+ -- any such families built into Postgres; so we have to hack things up
+ -- to create one. We do this by making two alias types that are really
+ -- int8 (so we need no new C code) and adding only some operators for them
+ -- into the standard integer_ops opfamily.
+ create type int8alias1;
+ create function int8alias1in(cstring) returns int8alias1
+ strict immutable language internal as 'int8in';
+ NOTICE: return type int8alias1 is only a shell
+ create function int8alias1out(int8alias1) returns cstring
+ strict immutable language internal as 'int8out';
+ NOTICE: argument type int8alias1 is only a shell
+ create type int8alias1 (
+ input = int8alias1in,
+ output = int8alias1out,
+ like = int8
+ );
+ create type int8alias2;
+ create function int8alias2in(cstring) returns int8alias2
+ strict immutable language internal as 'int8in';
+ NOTICE: return type int8alias2 is only a shell
+ create function int8alias2out(int8alias2) returns cstring
+ strict immutable language internal as 'int8out';
+ NOTICE: argument type int8alias2 is only a shell
+ create type int8alias2 (
+ input = int8alias2in,
+ output = int8alias2out,
+ like = int8
+ );
+ create cast (int8 as int8alias1) without function;
+ create cast (int8 as int8alias2) without function;
+ create cast (int8alias1 as int8) without function;
+ create cast (int8alias2 as int8) without function;
+ create function int8alias1eq(int8alias1, int8alias1) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias1eq,
+ leftarg = int8alias1, rightarg = int8alias1,
+ commutator = =,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8alias1, int8alias1);
+ create function int8alias2eq(int8alias2, int8alias2) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias2eq,
+ leftarg = int8alias2, rightarg = int8alias2,
+ commutator = =,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8alias2, int8alias2);
+ create function int8alias1eq(int8, int8alias1) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias1eq,
+ leftarg = int8, rightarg = int8alias1,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8, int8alias1);
+ create function int8alias1eq(int8alias1, int8alias2) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias1eq,
+ leftarg = int8alias1, rightarg = int8alias2,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8alias1, int8alias2);
+ create table ec0 (ff int8 primary key, f1 int8, f2 int8);
+ create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);
+ create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);
+ -- we didn't provide enough infrastructure for hashjoin and mergejoin plans
+ set enable_hashjoin = off;
+ set enable_mergejoin = off;
+ --
+ -- Note that for cases where there's a missing operator, we don't care so
+ -- much whether the plan is ideal as that we don't fail or generate an
+ -- outright incorrect plan.
+ --
+ explain (costs off)
+ select * from ec0 where ff = f1 and f1 = '42'::int8;
+ QUERY PLAN
+ ----------------------------------
+ Index Scan using ec0_pkey on ec0
+ Index Cond: (ff = 42::bigint)
+ Filter: (f1 = 42::bigint)
+ (3 rows)
+
+ explain (costs off)
+ select * from ec0 where ff = f1 and f1 = '42'::int8alias1;
+ QUERY PLAN
+ ---------------------------------------
+ Index Scan using ec0_pkey on ec0
+ Index Cond: (ff = '42'::int8alias1)
+ Filter: (f1 = '42'::int8alias1)
+ (3 rows)
+
+ explain (costs off)
+ select * from ec1 where ff = f1 and f1 = '42'::int8alias1;
+ QUERY PLAN
+ ---------------------------------------
+ Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = '42'::int8alias1)
+ Filter: (f1 = '42'::int8alias1)
+ (3 rows)
+
+ explain (costs off)
+ select * from ec1 where ff = f1 and f1 = '42'::int8alias2;
+ QUERY PLAN
+ ---------------------------------------------------
+ Seq Scan on ec1
+ Filter: ((ff = f1) AND (f1 = '42'::int8alias2))
+ (2 rows)
+
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and ff = '42'::int8;
+ QUERY PLAN
+ ---------------------------------------------------------------
+ Nested Loop
+ Join Filter: (ec1.ff = ec2.x1)
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: ((ff = 42::bigint) AND (ff = 42::bigint))
+ -> Seq Scan on ec2
+ (5 rows)
+
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1;
+ QUERY PLAN
+ ---------------------------------------------
+ Nested Loop
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = '42'::int8alias1)
+ -> Seq Scan on ec2
+ Filter: (x1 = '42'::int8alias1)
+ (5 rows)
+
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and '42'::int8 = x1;
+ QUERY PLAN
+ ----------------------------------------
+ Nested Loop
+ Join Filter: (ec1.ff = ec2.x1)
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = 42::bigint)
+ -> Seq Scan on ec2
+ Filter: (42::bigint = x1)
+ (6 rows)
+
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1;
+ QUERY PLAN
+ ---------------------------------------------
+ Nested Loop
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = '42'::int8alias1)
+ -> Seq Scan on ec2
+ Filter: (x1 = '42'::int8alias1)
+ (5 rows)
+
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;
+ QUERY PLAN
+ -----------------------------------------
+ Nested Loop
+ -> Seq Scan on ec2
+ Filter: (x1 = '42'::int8alias2)
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = ec2.x1)
+ (5 rows)
+
+ create unique index ec1_expr1 on ec1((ff + 1));
+ create unique index ec1_expr2 on ec1((ff + 2 + 1));
+ create unique index ec1_expr3 on ec1((ff + 3 + 1));
+ create unique index ec1_expr4 on ec1((ff + 4));
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+ QUERY PLAN
+ -----------------------------------------------------
+ Nested Loop
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = 42::bigint)
+ -> Append
+ -> Index Scan using ec1_expr2 on ec1 ec1_1
+ Index Cond: (((ff + 2) + 1) = ec1.f1)
+ -> Index Scan using ec1_expr3 on ec1 ec1_2
+ Index Cond: (((ff + 3) + 1) = ec1.f1)
+ -> Index Scan using ec1_expr4 on ec1 ec1_3
+ Index Cond: ((ff + 4) = ec1.f1)
+ (10 rows)
+
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1;
+ QUERY PLAN
+ ---------------------------------------------------------------
+ Nested Loop
+ Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: ((ff = 42::bigint) AND (ff = 42::bigint))
+ Filter: (ff = f1)
+ -> Append
+ -> Index Scan using ec1_expr2 on ec1 ec1_1
+ Index Cond: (((ff + 2) + 1) = 42::bigint)
+ -> Index Scan using ec1_expr3 on ec1 ec1_2
+ Index Cond: (((ff + 3) + 1) = 42::bigint)
+ -> Index Scan using ec1_expr4 on ec1 ec1_3
+ Index Cond: ((ff + 4) = 42::bigint)
+ (12 rows)
+
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss2
+ where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
+ QUERY PLAN
+ ---------------------------------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = 42::bigint)
+ -> Append
+ -> Index Scan using ec1_expr2 on ec1 ec1_1
+ Index Cond: (((ff + 2) + 1) = ec1.f1)
+ -> Index Scan using ec1_expr3 on ec1 ec1_2
+ Index Cond: (((ff + 3) + 1) = ec1.f1)
+ -> Index Scan using ec1_expr4 on ec1 ec1_3
+ Index Cond: ((ff + 4) = ec1.f1)
+ -> Append
+ -> Index Scan using ec1_expr2 on ec1 ec1_4
+ Index Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1)))
+ -> Index Scan using ec1_expr3 on ec1 ec1_5
+ Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1)))
+ -> Index Scan using ec1_expr4 on ec1 ec1_6
+ Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1)))
+ (18 rows)
+
+ drop index ec1_expr3;
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+ QUERY PLAN
+ -----------------------------------------------------
+ Nested Loop
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = 42::bigint)
+ -> Append
+ -> Index Scan using ec1_expr2 on ec1 ec1_1
+ Index Cond: (((ff + 2) + 1) = ec1.f1)
+ -> Seq Scan on ec1 ec1_2
+ Filter: (((ff + 3) + 1) = ec1.f1)
+ -> Index Scan using ec1_expr4 on ec1 ec1_3
+ Index Cond: ((ff + 4) = ec1.f1)
+ (10 rows)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index ab6c4e2..9902dbe 100644
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
*************** test: event_trigger
*** 98,104 ****
# ----------
# Another group of parallel tests
# ----------
! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data
windowxmlmap functional_deps advisory_lock json jsonb indirect_toast
# ----------
# Another group of parallel tests
# NB: temp.sql does a reconnect which transiently uses 2 connections,
--- 98,104 ----
# ----------
# Another group of parallel tests
# ----------
! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data
windowxmlmap functional_deps advisory_lock json jsonb indirect_toast equivclass
# ----------
# Another group of parallel tests
# NB: temp.sql does a reconnect which transiently uses 2 connections,
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 5ed2bf0..2902a05 100644
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
*************** test: advisory_lock
*** 126,131 ****
--- 126,132 ----
test: json
test: jsonb
test: indirect_toast
+ test: equivclass
test: plancache
test: limit
test: plpgsql
diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql
index ...fc775a5 .
*** a/src/test/regress/sql/equivclass.sql
--- b/src/test/regress/sql/equivclass.sql
***************
*** 0 ****
--- 1,172 ----
+ --
+ -- Tests for the planner's "equivalence class" mechanism
+ --
+
+ -- One thing that's not tested well during normal querying is the logic
+ -- for handling "broken" ECs. This is because an EC can only become broken
+ -- if its underlying btree operator family doesn't include a complete set
+ -- of cross-type equality operators. There are not (and should not be)
+ -- any such families built into Postgres; so we have to hack things up
+ -- to create one. We do this by making two alias types that are really
+ -- int8 (so we need no new C code) and adding only some operators for them
+ -- into the standard integer_ops opfamily.
+
+ create type int8alias1;
+ create function int8alias1in(cstring) returns int8alias1
+ strict immutable language internal as 'int8in';
+ create function int8alias1out(int8alias1) returns cstring
+ strict immutable language internal as 'int8out';
+ create type int8alias1 (
+ input = int8alias1in,
+ output = int8alias1out,
+ like = int8
+ );
+
+ create type int8alias2;
+ create function int8alias2in(cstring) returns int8alias2
+ strict immutable language internal as 'int8in';
+ create function int8alias2out(int8alias2) returns cstring
+ strict immutable language internal as 'int8out';
+ create type int8alias2 (
+ input = int8alias2in,
+ output = int8alias2out,
+ like = int8
+ );
+
+ create cast (int8 as int8alias1) without function;
+ create cast (int8 as int8alias2) without function;
+ create cast (int8alias1 as int8) without function;
+ create cast (int8alias2 as int8) without function;
+
+ create function int8alias1eq(int8alias1, int8alias1) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias1eq,
+ leftarg = int8alias1, rightarg = int8alias1,
+ commutator = =,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8alias1, int8alias1);
+
+ create function int8alias2eq(int8alias2, int8alias2) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias2eq,
+ leftarg = int8alias2, rightarg = int8alias2,
+ commutator = =,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8alias2, int8alias2);
+
+ create function int8alias1eq(int8, int8alias1) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias1eq,
+ leftarg = int8, rightarg = int8alias1,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8, int8alias1);
+
+ create function int8alias1eq(int8alias1, int8alias2) returns bool
+ strict immutable language internal as 'int8eq';
+ create operator = (
+ procedure = int8alias1eq,
+ leftarg = int8alias1, rightarg = int8alias2,
+ restrict = eqsel, join = eqjoinsel,
+ merges
+ );
+ alter operator family integer_ops using btree add
+ operator 3 = (int8alias1, int8alias2);
+
+ create table ec0 (ff int8 primary key, f1 int8, f2 int8);
+ create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);
+ create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);
+
+ -- we didn't provide enough infrastructure for hashjoin and mergejoin plans
+ set enable_hashjoin = off;
+ set enable_mergejoin = off;
+
+ --
+ -- Note that for cases where there's a missing operator, we don't care so
+ -- much whether the plan is ideal as that we don't fail or generate an
+ -- outright incorrect plan.
+ --
+
+ explain (costs off)
+ select * from ec0 where ff = f1 and f1 = '42'::int8;
+ explain (costs off)
+ select * from ec0 where ff = f1 and f1 = '42'::int8alias1;
+ explain (costs off)
+ select * from ec1 where ff = f1 and f1 = '42'::int8alias1;
+ explain (costs off)
+ select * from ec1 where ff = f1 and f1 = '42'::int8alias2;
+
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and ff = '42'::int8;
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1;
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and '42'::int8 = x1;
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1;
+ explain (costs off)
+ select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;
+
+ create unique index ec1_expr1 on ec1((ff + 1));
+ create unique index ec1_expr2 on ec1((ff + 2 + 1));
+ create unique index ec1_expr3 on ec1((ff + 3 + 1));
+ create unique index ec1_expr4 on ec1((ff + 4));
+
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1;
+
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss2
+ where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
+
+ drop index ec1_expr3;
+
+ explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
