From 8f5a432f6fbbcad1fd2937f33af09e9328690b6b Mon Sep 17 00:00:00 2001 From: "Andrey V. Lepikhov" Date: Tue, 4 Jul 2023 16:07:50 +0700 Subject: [PATCH] Add lost arrangements of relids and varnos. Add the test to check it. Add one more cleaning procedure on JoinDomain relids which was introduced recently with commit 3bef56e. Fix the corner case when we haven't removed SJ if the selfjoinquals list was empty. --- src/backend/optimizer/plan/analyzejoins.c | 15 ++++++++++- src/test/regress/expected/join.out | 26 ++++++++++++++++--- src/test/regress/expected/updatable_views.out | 17 +++++------- src/test/regress/sql/join.sql | 9 +++++++ 4 files changed, 53 insertions(+), 14 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index a93e4ce05c..15234b7a3b 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -424,6 +424,8 @@ remove_rel_from_query_common(PlannerInfo *root, RelOptInfo *rel, sjinf->commute_above_r = replace_relid(sjinf->commute_above_r, ojrelid, subst); sjinf->commute_below_l = replace_relid(sjinf->commute_below_l, ojrelid, subst); sjinf->commute_below_r = replace_relid(sjinf->commute_below_r, ojrelid, subst); + + replace_varno((Node *) sjinf->semi_rhs_exprs, relid, subst); } /* @@ -465,6 +467,8 @@ remove_rel_from_query_common(PlannerInfo *root, RelOptInfo *rel, /* ph_needed might or might not become empty */ phv->phrels = replace_relid(phv->phrels, relid, subst); phv->phrels = replace_relid(phv->phrels, ojrelid, subst); + phinfo->ph_lateral = replace_relid(phinfo->ph_lateral, relid, subst); + phinfo->ph_var->phrels = replace_relid(phinfo->ph_var->phrels, relid, subst); Assert(!bms_is_empty(phv->phrels)); Assert(phv->phnullingrels == NULL); /* no need to adjust */ } @@ -1545,6 +1549,7 @@ update_eclass(EquivalenceClass *ec, int from, int to) } em->em_relids = replace_relid(em->em_relids, from, to); + em->em_jdomain->jd_relids = replace_relid(em->em_jdomain->jd_relids, from, to); /* We only process inner joins */ replace_varno((Node *) em->em_expr, from, to); @@ -2101,7 +2106,7 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) */ restrictlist = generate_join_implied_equalities(root, joinrelids, inner->relids, - outer, 0); + outer, NULL); /* * Process restrictlist to seperate the self join quals out of @@ -2111,6 +2116,14 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) split_selfjoin_quals(root, restrictlist, &selfjoinquals, &otherjoinquals, inner->relid, outer->relid); + /* + * To enable SJE for the only degenerate case without any self join + * clauses at all, add baserestrictinfo to this list. + * Degenerate case works only if both sides have the same clause. So + * doesn't matter which side to add. + */ + selfjoinquals = list_concat(selfjoinquals, outer->baserestrictinfo); + /* * Determine if the inner table can duplicate outer rows. We must * bypass the unique rel cache here since we're possibly using a diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index b1f43f6ff8..027c356bcc 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5807,11 +5807,13 @@ explain (costs off) select p.* from (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k where p.k = 1 and p.k = 2; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------------------ Result One-Time Filter: false -(2 rows) + -> Index Scan using parent_pkey on parent x + Index Cond: (k = 1) +(4 rows) -- bug 5255: this is not optimizable by join removal begin; @@ -6327,6 +6329,24 @@ on true; -> Seq Scan on int8_tbl y (7 rows) +-- Check updating of Lateral links from top-level query to the removing relation +explain (COSTS OFF) +SELECT * FROM pg_am am WHERE am.amname IN ( + SELECT c1.relname AS relname + FROM pg_class c1 + JOIN pg_class c2 + ON c1.oid=c2.oid AND c1.oid < 10 +); + QUERY PLAN +--------------------------------------------------------------------- + Nested Loop Semi Join + Join Filter: (am.amname = c2.relname) + -> Seq Scan on pg_am am + -> Materialize + -> Index Scan using pg_class_oid_index on pg_class c2 + Index Cond: ((oid < '10'::oid) AND (oid IS NOT NULL)) +(6 rows) + -- -- SJR corner case: uniqueness of an inner is [partially] derived from -- baserestrictinfo clauses. diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 1950e6f281..a73c1f90c4 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2499,16 +2499,13 @@ SELECT * FROM rw_view1; (1 row) EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); - QUERY PLAN -------------------------------------------------------------------- - Update on base_tbl base_tbl_1 - -> Nested Loop - -> Index Scan using base_tbl_pkey on base_tbl base_tbl_1 - Index Cond: (id = 1) - -> Index Scan using base_tbl_pkey on base_tbl - Index Cond: (id = 1) - Filter: ((NOT deleted) AND snoop(data)) -(7 rows) + QUERY PLAN +-------------------------------------------------- + Update on base_tbl + -> Index Scan using base_tbl_pkey on base_tbl + Index Cond: (id = 1) + Filter: ((NOT deleted) AND snoop(data)) +(4 rows) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); NOTICE: snooped value: Row 1 diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index a170a9bbd0..1a64363837 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2394,6 +2394,15 @@ left join (select coalesce(y.q1, 1) from int8_tbl y on true) z on true; +-- Check updating of Lateral links from top-level query to the removing relation +explain (COSTS OFF) +SELECT * FROM pg_am am WHERE am.amname IN ( + SELECT c1.relname AS relname + FROM pg_class c1 + JOIN pg_class c2 + ON c1.oid=c2.oid AND c1.oid < 10 +); + -- -- SJR corner case: uniqueness of an inner is [partially] derived from -- baserestrictinfo clauses. -- 2.41.0