From 07afbca2e7e8b1699ea2dc7b581d59c99287fff8 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 1 Oct 2014 19:30:27 -0400 Subject: [PATCH] Fix some more problems with nested append relations. As of commit a87c72915 (which later got backpatched as far as 9.1), we're explicitly supporting the notion that append relations can be nested; this can occur when UNION ALL constructs are nested, or when a UNION ALL contains a table with inheritance children. Bug #11457 from Nelson Page, as well as an earlier report from Elvis Pranskevichus, showed that there were still nasty bugs associated with such cases: in particular the EquivalenceClass mechanism could try to generate "join" clauses connecting an appendrel child to some grandparent appendrel, which would result in assertion failures or bogus plans. Upon investigation I concluded that all current callers of find_childrel_appendrelinfo() need to be fixed to explicitly consider multiple levels of parent appendrels. The most complex fix was in processing of "broken" EquivalenceClasses, which are ECs for which we have been unable to generate all the derived equality clauses we would like to because of missing cross-type equality operators in the underlying btree operator family. That code path is more or less entirely untested by the regression tests to date, because no standard opfamilies have such holes in them. So I wrote a new regression test script to try to exercise it a bit, which turned out to be quite a worthwhile activity as it exposed existing bugs in all supported branches. The present patch is essentially the same as far back as 9.2, which is where parameterized paths were introduced. In 9.0 and 9.1, we only need to back-patch a small fragment of commit 5b7b5518d, which fixes failure to propagate out the original WHERE clauses when a broken EC contains constant members. (The regression test case results show that these older branches are noticeably stupider than 9.2+ in terms of the quality of the plans generated; but we don't really care about plan quality in such cases, only that the plan not be outright wrong. A more invasive fix in the older branches would not be a good idea anyway from a plan-stability standpoint.) --- src/backend/optimizer/path/equivclass.c | 38 +-- src/backend/optimizer/path/indxpath.c | 9 +- src/backend/optimizer/prep/prepunion.c | 23 ++ src/backend/optimizer/util/relnode.c | 59 +++- src/include/optimizer/pathnode.h | 2 + src/include/optimizer/prep.h | 3 + src/test/regress/expected/equivclass.out | 386 +++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/equivclass.sql | 224 +++++++++++++ 10 files changed, 720 insertions(+), 27 deletions(-) create mode 100644 src/test/regress/expected/equivclass.out create mode 100644 src/test/regress/sql/equivclass.sql diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index b7aff3775e..e5dd58efe3 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -48,7 +48,7 @@ static List *generate_join_implied_equalities_broken(PlannerInfo *root, Relids nominal_join_relids, Relids outer_relids, Relids nominal_inner_relids, - AppendRelInfo *inner_appinfo); + RelOptInfo *inner_rel); static Oid select_equality_operator(EquivalenceClass *ec, Oid lefttype, Oid righttype); static RestrictInfo *create_join_clause(PlannerInfo *root, @@ -1000,22 +1000,18 @@ generate_join_implied_equalities(PlannerInfo *root, 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); + /* 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 { - inner_appinfo = NULL; nominal_inner_relids = inner_relids; nominal_join_relids = join_relids; } @@ -1051,7 +1047,7 @@ generate_join_implied_equalities(PlannerInfo *root, nominal_join_relids, outer_relids, nominal_inner_relids, - inner_appinfo); + inner_rel); result = list_concat(result, sublist); } @@ -1244,7 +1240,7 @@ generate_join_implied_equalities_broken(PlannerInfo *root, Relids nominal_join_relids, Relids outer_relids, Relids nominal_inner_relids, - AppendRelInfo *inner_appinfo) + RelOptInfo *inner_rel) { List *result = NIL; ListCell *lc; @@ -1266,10 +1262,16 @@ generate_join_implied_equalities_broken(PlannerInfo *root, * 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_appinfo) - result = (List *) adjust_appendrel_attrs(root, (Node *) result, - inner_appinfo); + if (inner_rel->reloptkind == RELOPT_OTHER_MEMBER_REL && + result != NIL) + result = (List *) adjust_appendrel_attrs_multilevel(root, + (Node *) result, + inner_rel); return result; } @@ -2071,14 +2073,14 @@ generate_implied_equalities_for_column(PlannerInfo *root, { List *result = NIL; bool is_child_rel = (rel->reloptkind == RELOPT_OTHER_MEMBER_REL); - Index parent_relid; + Relids parent_relids; ListCell *lc1; - /* If it's a child rel, we'll need to know what its parent is */ + /* If it's a child rel, we'll need to know what its parent(s) are */ if (is_child_rel) - parent_relid = find_childrel_appendrelinfo(root, rel)->parent_relid; + parent_relids = find_childrel_parents(root, rel); else - parent_relid = 0; /* not used, but keep compiler quiet */ + parent_relids = NULL; /* not used, but keep compiler quiet */ foreach(lc1, root->eq_classes) { @@ -2148,10 +2150,10 @@ generate_implied_equalities_for_column(PlannerInfo *root, /* * Also, if this is a child rel, avoid generating a useless join - * to its parent rel. + * to its parent rel(s). */ if (is_child_rel && - bms_is_member(parent_relid, other_em->em_relids)) + 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 42dcb111ae..9c22d31150 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -2586,16 +2586,11 @@ check_partial_indexes(PlannerInfo *root, RelOptInfo *rel) * 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. + * that case we must remove its parents' relid(s) 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)); - } + 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 0410fddc54..1cec511e0f 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -1979,3 +1979,26 @@ adjust_inherited_tlist(List *tlist, AppendRelInfo *context) 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); + else + Assert(parent_rel->reloptkind == RELOPT_BASEREL); + /* 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 c938c2700f..4c76f542b9 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -713,7 +713,8 @@ build_empty_join_rel(PlannerInfo *root) * 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. + * 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) @@ -737,6 +738,62 @@ find_childrel_appendrelinfo(PlannerInfo *root, RelOptInfo *rel) } +/* + * 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); + + Assert(rel->reloptkind == RELOPT_BASEREL); + + 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); + + Assert(rel->reloptkind == RELOPT_BASEREL); + + return result; +} + + /* * get_baserel_parampathinfo * Get the ParamPathInfo for a parameterized path for a base relation, diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index a0bcc82b31..26b17f5f7a 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -145,6 +145,8 @@ extern RelOptInfo *build_join_rel(PlannerInfo *root, 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 f5fc7e8e71..1891f4d40e 100644 --- a/src/include/optimizer/prep.h +++ b/src/include/optimizer/prep.h @@ -58,4 +58,7 @@ extern void expand_inherited_tables(PlannerInfo *root); 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 new file mode 100644 index 0000000000..6357bf81e2 --- /dev/null +++ b/src/test/regress/expected/equivclass.out @@ -0,0 +1,386 @@ +-- +-- 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 function int8alias1lt(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8lt'; +create operator < ( + procedure = int8alias1lt, + leftarg = int8alias1, rightarg = int8alias1 +); +alter operator family integer_ops using btree add + operator 1 < (int8alias1, int8alias1); +create function int8alias1cmp(int8, int8alias1) returns int + strict immutable language internal as 'btint8cmp'; +alter operator family integer_ops using btree add + function 1 int8alias1cmp (int8, int8alias1); +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); +-- for the moment we only want to look at nestloop 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))) + Filter: ((((ec1_1.ff + 2) + 1)) = ((ff + 2) + 1)) + -> Index Scan using ec1_expr3 on ec1 ec1_5 + Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1))) + Filter: ((((ec1_1.ff + 2) + 1)) = ((ff + 3) + 1)) + -> Index Scan using ec1_expr4 on ec1 ec1_6 + Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1))) + Filter: ((((ec1_1.ff + 2) + 1)) = (ff + 4)) +(21 rows) + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; +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 +----------------------------------------------------------------- + Merge Join + Merge Cond: ((((ec1_4.ff + 2) + 1)) = (((ec1_1.ff + 2) + 1))) + -> Merge Append + Sort Key: (((ec1_4.ff + 2) + 1)) + -> Index Scan using ec1_expr2 on ec1 ec1_4 + -> Index Scan using ec1_expr3 on ec1 ec1_5 + -> Index Scan using ec1_expr4 on ec1 ec1_6 + -> Materialize + -> Merge Join + Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1) + -> Merge Append + Sort Key: (((ec1_1.ff + 2) + 1)) + -> Index Scan using ec1_expr2 on ec1 ec1_1 + -> Index Scan using ec1_expr3 on ec1 ec1_2 + -> Index Scan using ec1_expr4 on ec1 ec1_3 + -> Materialize + -> Sort + Sort Key: ec1.f1 + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = 42::bigint) +(20 rows) + +-- check partially indexed scan +set enable_nestloop = on; +set enable_mergejoin = off; +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) + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; +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 +----------------------------------------------------- + Merge Join + Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1) + -> Merge Append + Sort Key: (((ec1_1.ff + 2) + 1)) + -> Index Scan using ec1_expr2 on ec1 ec1_1 + -> Sort + Sort Key: (((ec1_2.ff + 3) + 1)) + -> Seq Scan on ec1 ec1_2 + -> Index Scan using ec1_expr4 on ec1 ec1_3 + -> Materialize + -> Sort + Sort Key: ec1.f1 + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = 42::bigint) +(14 rows) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index c0416f4fb9..ce1d83cfcc 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -98,7 +98,7 @@ test: event_trigger # ---------- # Another group of parallel tests # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock json jsonb indirect_toast +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap 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 16a190507d..167184c2dd 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -125,6 +125,7 @@ test: advisory_lock 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 new file mode 100644 index 0000000000..17fad673e9 --- /dev/null +++ b/src/test/regress/sql/equivclass.sql @@ -0,0 +1,224 @@ +-- +-- 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 function int8alias1lt(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8lt'; +create operator < ( + procedure = int8alias1lt, + leftarg = int8alias1, rightarg = int8alias1 +); +alter operator family integer_ops using btree add + operator 1 < (int8alias1, int8alias1); + +create function int8alias1cmp(int8, int8alias1) returns int + strict immutable language internal as 'btint8cmp'; +alter operator family integer_ops using btree add + function 1 int8alias1cmp (int8, int8alias1); + +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); + +-- for the moment we only want to look at nestloop 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; + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; + +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; + +-- check partially indexed scan +set enable_nestloop = on; +set enable_mergejoin = off; + +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; + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; + +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; -- 2.40.0