On 04.06.2025 13:40, Alena Rybakina wrote:
Do you have any ideas on how to solve this problem? So far, the only
approach I see is to try an alternative plan but I'm still learning this.
Hi,
I've reviewed this patch, and I have suggestion about the approach.
Currently, the patch extends 'convert_EXISTS_sublick_to_join' with quite
complex logic (clause collection, volatile checks, rewriting join quals,
etc). While it works, the amount of branching and special cases makes
the function harder to follow.
Looking at the logic, it seems that a large part of the complexity comes
from trying to directly adapt 'convert_EXISTS_sublink_to_join' instead
of factoring out a dedicated path. An alternative would be to introduce
a separate function *'convert_EXISTS_sublink_to_lateral_join' *- with a
similar API to 'convert_ANY_sublink_to_join'. Such a function can focus
only on the EXISTS-to-join case, while keeping the existing function
shorter and easier to reason about.
I even made some first rough sketches of this approach (not a finished
patch, just an outline). Of course, it would still need proper
adaptation, but I think it demonstrates that the overall structure can
be kept simpler.
What do you think about refactoring in this direction?
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index d71ed958e31..6dbbc3d9b72 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -104,6 +104,96 @@ static Bitmapset *finalize_plan(PlannerInfo *root,
static bool finalize_primnode(Node *node, finalize_primnode_context *context);
static bool finalize_agg_primnode(Node *node, finalize_primnode_context *context);
+/*
+ * convert_EXISTS_sublink_to_lateral_join:
+ * try to convert an EXISTS SubLink to a lateral join
+ *
+ * The API of this function is identical to convert_ANY_sublink_to_join's,
+ * except that we also support the case where the caller has found NOT EXISTS,
+ * so we need an additional input parameter "under_not".
+ */
+JoinExpr *
+convert_EXISTS_sublink_to_lateral_join(PlannerInfo *root, SubLink *sublink,
+ bool under_not, Relids available_rels)
+{
+ JoinExpr *result;
+ Query *parse = root->parse;
+ Query *subselect = (Query *) sublink->subselect;
+ int rtindex;
+ ParseNamespaceItem *nsitem;
+ RangeTblEntry *rte;
+ RangeTblRef *rtr;
+ ParseState *pstate;
+ Relids sub_ref_outer_relids;
+ bool use_lateral;
+
+ Assert(sublink->subLinkType == EXISTS_SUBLINK);
+
+ /*
+ * See if the subquery can be simplified based on the knowledge that it's
+ * being used in EXISTS(). If we aren't able to get rid of its
+ * targetlist, we have to fail, because the pullup operation leaves us
+ * with noplace to evaluate the targetlist.
+ */
+ if (!simplify_EXISTS_query(root, subselect))
+ return NULL;
+
+ /*
+ * If the sub-select contains any Vars of the parent query, we treat it as
+ * LATERAL. (Vars from higher levels don't matter here.)
+ */
+ sub_ref_outer_relids = pull_varnos_of_level(NULL, (Node *) subselect, 1);
+ use_lateral = !bms_is_empty(sub_ref_outer_relids);
+
+ /*
+ * Can't convert if the sub-select contains parent-level Vars of relations
+ * not in available_rels.
+ */
+ if (!bms_is_subset(sub_ref_outer_relids, available_rels))
+ return NULL;
+
+ /* Create a dummy ParseState for addRangeTableEntryForSubquery */
+ pstate = make_parsestate(NULL);
+
+ /*
+ * Okay, pull up the sub-select into upper range table.
+ *
+ * We rely here on the assumption that the outer query has no references
+ * to the inner (necessarily true, other than the Vars that we build
+ * below). Therefore this is a lot easier than what pull_up_subqueries has
+ * to go through.
+ */
+ nsitem = addRangeTableEntryForSubquery(pstate,
+ subselect,
+ makeAlias("EXISTS_subquery", NIL),
+ use_lateral,
+ false);
+ rte = nsitem->p_rte;
+ parse->rtable = lappend(parse->rtable, rte);
+ rtindex = list_length(parse->rtable);
+
+ /*
+ * Form a RangeTblRef for the pulled-up sub-select.
+ */
+ rtr = makeNode(RangeTblRef);
+ rtr->rtindex = rtindex;
+
+ /*
+ * And finally, build the JoinExpr node.
+ */
+ result = makeNode(JoinExpr);
+ result->jointype = under_not ? JOIN_ANTI : JOIN_SEMI;;
+ result->isNatural = false;
+ result->larg = NULL; /* caller must fill this in */
+ result->rarg = (Node *) rtr;
+ result->usingClause = NIL;
+ result->join_using_alias = NULL;
+ result->quals = NULL;
+ result->alias = NULL;
+ result->rtindex = 0; /* we don't need an RTE for it */
+
+ return result;
+}
/*
* Get the datatype/typmod/collation of the first column of the plan's output.
@@ -1462,6 +1552,9 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
Assert(sublink->subLinkType == EXISTS_SUBLINK);
+ return convert_EXISTS_sublink_to_lateral_join(root, sublink,
+ under_not, available_rels);
+
/*
* Can't flatten if it contains WITH. (We could arrange to pull up the
* WITH into the parent query's cteList, but that risks changing the
