Use exact nullingrels matches for NestLoopParams
Started by Richard Guo2 months ago2 messages
As noted in the commit message of 66e9df9f6, we can now safely use
NRM_EQUAL to process NestLoopParams in setrefs.c. This is because
identify_current_nestloop_params now ensures that Vars or PHVs seen in
a NestLoopParam expression have nullingrels that include exactly the
outer-join relids that appear in the outer side's output and can null
the respective Var or PHV.
Here is a patch that makes that change. It also removes the
NRM_SUBSET enum value, along with all remaining checks for it, since
it is no longer used.
(I'm wondering if we have a way to identify the nullingrels added by
an outer join when fixing up its targetlist and qpqual. If so, we
might be able to switch to exact nullingrels matches for them and
thereby get rid of NRM_SUPERSET too.)
- Richard
Attachments:
v1-0001-Use-exact-nullingrels-matches-for-NestLoopParams.patchapplication/octet-stream; name=v1-0001-Use-exact-nullingrels-matches-for-NestLoopParams.patchDownload
From c5afc439a029cbb33bed914d5639abb85dc2af64 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 10 Nov 2025 17:05:42 +0900
Subject: [PATCH v1] Use exact nullingrels matches for NestLoopParams
We have been using NRM_SUBSET to process NestLoopParams in setrefs.c,
because Vars or PHVs in NestLoopParam expressions may previously have
had nullingrels that were just subsets of those in the Vars or PHVs
actually available from the outer side.
Since 66e9df9f6, identify_current_nestloop_params ensures that any
Vars or PHVs seen in a NestLoopParam expression have nullingrels that
include exactly the outer-join relids that appear in the outer side's
output and can null the respective Var or PHV. As noted in that
commit's message, we can now safely use NRM_EQUAL to process
NestLoopParams in setrefs.c.
This patch makes that change and removes the definition of NRM_SUBSET,
along with all remaining checks for it, since it is no longer used.
---
src/backend/optimizer/plan/setrefs.c | 29 ++++++++++------------------
1 file changed, 10 insertions(+), 19 deletions(-)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index ccdc9bc264a..a4c220e47fb 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -34,7 +34,6 @@
typedef enum
{
NRM_EQUAL, /* expect exact match of nullingrels */
- NRM_SUBSET, /* actual Var may have a subset of input */
NRM_SUPERSET, /* actual Var may have a superset of input */
} NullingRelsMatch;
@@ -2387,22 +2386,19 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
NestLoopParam *nlp = (NestLoopParam *) lfirst(lc);
/*
- * Because we don't reparameterize parameterized paths to match
- * the outer-join level at which they are used, Vars seen in the
- * NestLoopParam expression may have nullingrels that are just a
- * subset of those in the Vars actually available from the outer
- * side. (Lateral references can also cause this, as explained in
- * the comments for identify_current_nestloop_params.) Not
- * checking this exactly is a bit grotty, but the work needed to
- * make things match up perfectly seems well out of proportion to
- * the value.
+ * identify_current_nestloop_params has already ensured that any
+ * Vars or PHVs seen in the NestLoopParam expression have
+ * nullingrels that include exactly the outer-join relids that
+ * appear in the outer side's output and can null the respective
+ * Var or PHV. So we can use exact nullingrels matches for the
+ * NestLoopParam expression.
*/
nlp->paramval = (Var *) fix_upper_expr(root,
(Node *) nlp->paramval,
outer_itlist,
OUTER_VAR,
rtoffset,
- NRM_SUBSET,
+ NRM_EQUAL,
NUM_EXEC_TLIST(outer_plan));
/* Check we replaced any PlaceHolderVar with simple Var */
if (!(IsA(nlp->paramval, Var) &&
@@ -2882,8 +2878,7 @@ build_tlist_index_other_vars(List *tlist, int ignore_rel)
* We cross-check the varnullingrels of the subplan output Var based on
* nrm_match. Most call sites should pass NRM_EQUAL indicating we expect
* an exact match. However, there are places where we haven't cleaned
- * things up completely, and we have to settle for allowing subset or
- * superset matches.
+ * things up completely, and we have to settle for allowing superset matches.
*/
static Var *
search_indexed_tlist_for_var(Var *var, indexed_tlist *itlist,
@@ -2919,9 +2914,7 @@ search_indexed_tlist_for_var(Var *var, indexed_tlist *itlist,
* would affect only system columns.)
*/
if (!(varattno <= 0 ||
- (nrm_match == NRM_SUBSET ?
- bms_is_subset(var->varnullingrels, vinfo->varnullingrels) :
- nrm_match == NRM_SUPERSET ?
+ (nrm_match == NRM_SUPERSET ?
bms_is_subset(vinfo->varnullingrels, var->varnullingrels) :
bms_equal(vinfo->varnullingrels, var->varnullingrels))))
elog(ERROR, "wrong varnullingrels %s (expected %s) for Var %d/%d",
@@ -2975,9 +2968,7 @@ search_indexed_tlist_for_phv(PlaceHolderVar *phv,
continue;
/* Verify that we kept all the nullingrels machinations straight */
- if (!(nrm_match == NRM_SUBSET ?
- bms_is_subset(phv->phnullingrels, subphv->phnullingrels) :
- nrm_match == NRM_SUPERSET ?
+ if (!(nrm_match == NRM_SUPERSET ?
bms_is_subset(subphv->phnullingrels, phv->phnullingrels) :
bms_equal(subphv->phnullingrels, phv->phnullingrels)))
elog(ERROR, "wrong phnullingrels %s (expected %s) for PlaceHolderVar %d",
--
2.39.5 (Apple Git-154)
Re: Use exact nullingrels matches for NestLoopParams
On Tue, Nov 11, 2025 at 3:58 PM Richard Guo <guofenglinux@gmail.com> wrote:
Here is a patch that makes that change. It also removes the
NRM_SUBSET enum value, along with all remaining checks for it, since
it is no longer used.
With the changes in 0001, we should now be able to use exact
nullingrels matches in all cases when fixing up expressions of
upper-level plan nodes that are not joins. Therefore, I think we can
remove the nrm_match parameter from fix_upper_expr(), along with the
corresponding field in fix_upper_expr_context. I've done that in 0002
(which will be squashed into 0001 when committed).
(I'm wondering if we have a way to identify the nullingrels added by
an outer join when fixing up its targetlist and qpqual. If so, we
might be able to switch to exact nullingrels matches for them and
thereby get rid of NRM_SUPERSET too.)
After looking into this, I think it's quite challenging to determine
the effects of an outer join when the join has been commuted with
another one per outer join identity 3. In such cases, the Vars/PHVs
in the join's targetlist and qpqual should have the same nullingrels
they would have if the two joins had been done in syntactic order.
Unfortunately, in setrefs.c we do not have enough information (such as
the relevant SpecialJoinInfos) to determine that.
For example, consider when we transform
(A leftjoin B on (Pab)) leftjoin C on (Pbc)
to
A leftjoin (B leftjoin C on (Pbc)) on (Pab)
For the now-upper A/B join, whose ojrelids is {3, 5}, the Vars from B
in its targetlist and qpqual have nullingrels = {3}, and the Vars from
C have nullingrels = {5}. In its inner plan's targetlist, however,
both B and C Vars have empty nullingrels. The problem is that we have
no reliable way in setrefs.c to determine which of the join's ojrelids
should apply to which Vars.
On the other hand, if we perform the transformation in the reverse
order, then for the B/C join whose ojrelids is {4}, the C Vars in its
targetlist and qpqual have nullingrels = {4, 5}, while in its inner
plan's targetlist the C Vars have empty nullingrels. We have no
reliable way to determine that {5} should also be applied to the C
Vars.
However, we can tighten the check somewhat. Currently, we check
whether the jointype is JOIN_INNER and use NRM_SUPERSET if it is not.
We can improve this by checking whether the Join node has non-empty
ojrelids and using NRM_SUPERSET only in that case. This allows us to
perform exact matches in more situations, such as the pushed-down B/C
join in the first case.
0003 implements this. To support it, we record the outer-join relids
in Join plan nodes (the related changes for adding ojrelids are
adapted from the patch in [1]/messages/by-id/3200728.1758662857@sss.pgh.pa.us). This may seem like overengineering to
have a new field just for this check, but the field also improves
EXPLAIN (RANGE_TABLE) output by showing which outer-join relids are
completed by each Join plan node. I expect that we will find other
uses for the ojrelids information in the future.
[1]: /messages/by-id/3200728.1758662857@sss.pgh.pa.us
- Richard
Attachments:
v2-0001-Use-exact-nullingrels-matches-for-NestLoopParams.patchapplication/octet-stream; name=v2-0001-Use-exact-nullingrels-matches-for-NestLoopParams.patchDownload
From ae2757cb7a954a815e6dc6a581b9bda0718bf2b0 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 10 Nov 2025 17:05:42 +0900
Subject: [PATCH v2 1/3] Use exact nullingrels matches for NestLoopParams
We have been using NRM_SUBSET to process NestLoopParams in setrefs.c,
because Vars or PHVs in NestLoopParam expressions may previously have
had nullingrels that were just subsets of those in the Vars or PHVs
actually available from the outer side.
Since 66e9df9f6, identify_current_nestloop_params ensures that any
Vars or PHVs seen in a NestLoopParam expression have nullingrels that
include exactly the outer-join relids that appear in the outer side's
output and can null the respective Var or PHV. As noted in that
commit's message, we can now safely use NRM_EQUAL to process
NestLoopParams in setrefs.c.
This patch makes that change and removes the definition of NRM_SUBSET,
along with all remaining checks for it, since it is no longer used.
---
src/backend/optimizer/plan/setrefs.c | 29 ++++++++++------------------
1 file changed, 10 insertions(+), 19 deletions(-)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index ccdc9bc264a..a4c220e47fb 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -34,7 +34,6 @@
typedef enum
{
NRM_EQUAL, /* expect exact match of nullingrels */
- NRM_SUBSET, /* actual Var may have a subset of input */
NRM_SUPERSET, /* actual Var may have a superset of input */
} NullingRelsMatch;
@@ -2387,22 +2386,19 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
NestLoopParam *nlp = (NestLoopParam *) lfirst(lc);
/*
- * Because we don't reparameterize parameterized paths to match
- * the outer-join level at which they are used, Vars seen in the
- * NestLoopParam expression may have nullingrels that are just a
- * subset of those in the Vars actually available from the outer
- * side. (Lateral references can also cause this, as explained in
- * the comments for identify_current_nestloop_params.) Not
- * checking this exactly is a bit grotty, but the work needed to
- * make things match up perfectly seems well out of proportion to
- * the value.
+ * identify_current_nestloop_params has already ensured that any
+ * Vars or PHVs seen in the NestLoopParam expression have
+ * nullingrels that include exactly the outer-join relids that
+ * appear in the outer side's output and can null the respective
+ * Var or PHV. So we can use exact nullingrels matches for the
+ * NestLoopParam expression.
*/
nlp->paramval = (Var *) fix_upper_expr(root,
(Node *) nlp->paramval,
outer_itlist,
OUTER_VAR,
rtoffset,
- NRM_SUBSET,
+ NRM_EQUAL,
NUM_EXEC_TLIST(outer_plan));
/* Check we replaced any PlaceHolderVar with simple Var */
if (!(IsA(nlp->paramval, Var) &&
@@ -2882,8 +2878,7 @@ build_tlist_index_other_vars(List *tlist, int ignore_rel)
* We cross-check the varnullingrels of the subplan output Var based on
* nrm_match. Most call sites should pass NRM_EQUAL indicating we expect
* an exact match. However, there are places where we haven't cleaned
- * things up completely, and we have to settle for allowing subset or
- * superset matches.
+ * things up completely, and we have to settle for allowing superset matches.
*/
static Var *
search_indexed_tlist_for_var(Var *var, indexed_tlist *itlist,
@@ -2919,9 +2914,7 @@ search_indexed_tlist_for_var(Var *var, indexed_tlist *itlist,
* would affect only system columns.)
*/
if (!(varattno <= 0 ||
- (nrm_match == NRM_SUBSET ?
- bms_is_subset(var->varnullingrels, vinfo->varnullingrels) :
- nrm_match == NRM_SUPERSET ?
+ (nrm_match == NRM_SUPERSET ?
bms_is_subset(vinfo->varnullingrels, var->varnullingrels) :
bms_equal(vinfo->varnullingrels, var->varnullingrels))))
elog(ERROR, "wrong varnullingrels %s (expected %s) for Var %d/%d",
@@ -2975,9 +2968,7 @@ search_indexed_tlist_for_phv(PlaceHolderVar *phv,
continue;
/* Verify that we kept all the nullingrels machinations straight */
- if (!(nrm_match == NRM_SUBSET ?
- bms_is_subset(phv->phnullingrels, subphv->phnullingrels) :
- nrm_match == NRM_SUPERSET ?
+ if (!(nrm_match == NRM_SUPERSET ?
bms_is_subset(subphv->phnullingrels, phv->phnullingrels) :
bms_equal(subphv->phnullingrels, phv->phnullingrels)))
elog(ERROR, "wrong phnullingrels %s (expected %s) for PlaceHolderVar %d",
--
2.39.5 (Apple Git-154)
v2-0002-Remove-nrm_match-parameter-from-fix_upper_expr.patchapplication/octet-stream; name=v2-0002-Remove-nrm_match-parameter-from-fix_upper_expr.patchDownload
From c5891b0a5754a905ee36c354e0065a13d09f68ae Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 17 Nov 2025 10:28:36 +0900
Subject: [PATCH v2 2/3] Remove nrm_match parameter from fix_upper_expr
---
src/backend/optimizer/plan/setrefs.c | 32 ++++++----------------------
1 file changed, 7 insertions(+), 25 deletions(-)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index a4c220e47fb..95c5cd05339 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -78,7 +78,6 @@ typedef struct
indexed_tlist *subplan_itlist;
int newvarno;
int rtoffset;
- NullingRelsMatch nrm_match;
double num_exec;
} fix_upper_expr_context;
@@ -197,7 +196,6 @@ static Node *fix_upper_expr(PlannerInfo *root,
indexed_tlist *subplan_itlist,
int newvarno,
int rtoffset,
- NullingRelsMatch nrm_match,
double num_exec);
static Node *fix_upper_expr_mutator(Node *node,
fix_upper_expr_context *context);
@@ -1383,7 +1381,6 @@ set_indexonlyscan_references(PlannerInfo *root,
index_itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_TLIST((Plan *) plan));
plan->scan.plan.qual = (List *)
fix_upper_expr(root,
@@ -1391,7 +1388,6 @@ set_indexonlyscan_references(PlannerInfo *root,
index_itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) plan));
plan->recheckqual = (List *)
fix_upper_expr(root,
@@ -1399,7 +1395,6 @@ set_indexonlyscan_references(PlannerInfo *root,
index_itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) plan));
/* indexqual is already transformed to reference index columns */
plan->indexqual = fix_scan_list(root, plan->indexqual,
@@ -1629,7 +1624,6 @@ set_foreignscan_references(PlannerInfo *root,
itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_TLIST((Plan *) fscan));
fscan->scan.plan.qual = (List *)
fix_upper_expr(root,
@@ -1637,7 +1631,6 @@ set_foreignscan_references(PlannerInfo *root,
itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) fscan));
fscan->fdw_exprs = (List *)
fix_upper_expr(root,
@@ -1645,7 +1638,6 @@ set_foreignscan_references(PlannerInfo *root,
itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) fscan));
fscan->fdw_recheck_quals = (List *)
fix_upper_expr(root,
@@ -1653,7 +1645,6 @@ set_foreignscan_references(PlannerInfo *root,
itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) fscan));
pfree(itlist);
/* fdw_scan_tlist itself just needs fix_scan_list() adjustments */
@@ -1715,7 +1706,6 @@ set_customscan_references(PlannerInfo *root,
itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_TLIST((Plan *) cscan));
cscan->scan.plan.qual = (List *)
fix_upper_expr(root,
@@ -1723,7 +1713,6 @@ set_customscan_references(PlannerInfo *root,
itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) cscan));
cscan->custom_exprs = (List *)
fix_upper_expr(root,
@@ -1731,7 +1720,6 @@ set_customscan_references(PlannerInfo *root,
itlist,
INDEX_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) cscan));
pfree(itlist);
/* custom_scan_tlist itself just needs fix_scan_list() adjustments */
@@ -1988,7 +1976,6 @@ set_hash_references(PlannerInfo *root, Plan *plan, int rtoffset)
outer_itlist,
OUTER_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL(plan));
/* Hash doesn't project */
@@ -2390,15 +2377,14 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
* Vars or PHVs seen in the NestLoopParam expression have
* nullingrels that include exactly the outer-join relids that
* appear in the outer side's output and can null the respective
- * Var or PHV. So we can use exact nullingrels matches for the
- * NestLoopParam expression.
+ * Var or PHV. Therefore, fix_upper_expr will not complain when
+ * performing the nullingrels matches here.
*/
nlp->paramval = (Var *) fix_upper_expr(root,
(Node *) nlp->paramval,
outer_itlist,
OUTER_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_TLIST(outer_plan));
/* Check we replaced any PlaceHolderVar with simple Var */
if (!(IsA(nlp->paramval, Var) &&
@@ -2441,7 +2427,6 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
outer_itlist,
OUTER_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL((Plan *) join));
}
@@ -2546,7 +2531,6 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
subplan_itlist,
OUTER_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_TLIST(plan));
}
else
@@ -2555,7 +2539,6 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
subplan_itlist,
OUTER_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_TLIST(plan));
tle = flatCopyTargetEntry(tle);
tle->expr = (Expr *) newexpr;
@@ -2569,7 +2552,6 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
subplan_itlist,
OUTER_VAR,
rtoffset,
- NRM_EQUAL,
NUM_EXEC_QUAL(plan));
pfree(subplan_itlist);
@@ -3275,11 +3257,13 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
* expensive, so we don't want to try it in the common case where the
* subplan tlist is just a flattened list of Vars.)
*
+ * When cross-checking the nullingrels of the subplan output Vars/PHVs, we
+ * always expect exact matches.
+ *
* 'node': the tree to be fixed (a target item or qual)
* 'subplan_itlist': indexed target list for subplan (or index)
* 'newvarno': varno to use for Vars referencing tlist elements
* 'rtoffset': how much to increment varnos by
- * 'nrm_match': as for search_indexed_tlist_for_var()
* 'num_exec': estimated number of executions of expression
*
* The resulting tree is a copy of the original in which all Var nodes have
@@ -3292,7 +3276,6 @@ fix_upper_expr(PlannerInfo *root,
indexed_tlist *subplan_itlist,
int newvarno,
int rtoffset,
- NullingRelsMatch nrm_match,
double num_exec)
{
fix_upper_expr_context context;
@@ -3301,7 +3284,6 @@ fix_upper_expr(PlannerInfo *root,
context.subplan_itlist = subplan_itlist;
context.newvarno = newvarno;
context.rtoffset = rtoffset;
- context.nrm_match = nrm_match;
context.num_exec = num_exec;
return fix_upper_expr_mutator(node, &context);
}
@@ -3321,7 +3303,7 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
context->subplan_itlist,
context->newvarno,
context->rtoffset,
- context->nrm_match);
+ NRM_EQUAL);
if (!newvar)
elog(ERROR, "variable not found in subplan target list");
return (Node *) newvar;
@@ -3336,7 +3318,7 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
newvar = search_indexed_tlist_for_phv(phv,
context->subplan_itlist,
context->newvarno,
- context->nrm_match);
+ NRM_EQUAL);
if (newvar)
return (Node *) newvar;
}
--
2.39.5 (Apple Git-154)
v2-0003-Tighten-nullingrels-checks-for-outer-joins.patchapplication/octet-stream; name=v2-0003-Tighten-nullingrels-checks-for-outer-joins.patchDownload
From 52adab31e867a28f3930ff413c470ddbb510992e Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 17 Nov 2025 10:54:17 +0900
Subject: [PATCH v2 3/3] Tighten nullingrels checks for outer joins
When fixing up the targetlist and qpqual of an outer join, we must
account for the effects of the outer join. Vars and PHVs appearing
there are logically above the join, so they should have nullingrels
equal to the input Vars/PHVs' nullingrels plus the bit added by the
outer join.
Determining the effects of the outer join can be tricky when the join
has been commuted with another one per outer join identity 3. In this
case, the Vars/PHVs in the join's targetlist and qpqual should have
the same nullingrels that they would if the two joins had been done in
syntactic order. Unfortunately, in setrefs.c, we don't have enough
information to identify what that should be, so we have to use
superset nullingrels matches instead of exact ones.
However, we can tighten the check somewhat. Currently, we check
whether the jointype is JOIN_INNER and use NRM_SUPERSET if it is not.
We can improve this by checking whether the Join node has non-empty
ojrelids and using NRM_SUPERSET only in that case. This allows us to
perform exact matches in more situations.
To support this, we record the outer-join relids in Join plan nodes.
This information can also improve EXPLAIN (RANGE_TABLE) output by
showing which outer-join relids are completed by each Join plan node.
We may discover additional uses for this information in the future.
---
.../expected/pg_overexplain.out | 40 ++++++++++++++++++-
contrib/pg_overexplain/pg_overexplain.c | 21 ++++++++++
contrib/pg_overexplain/sql/pg_overexplain.sql | 14 ++++++-
src/backend/optimizer/plan/createplan.c | 39 ++++++++++++++++--
src/backend/optimizer/plan/setrefs.c | 18 ++++-----
src/include/nodes/plannodes.h | 2 +
6 files changed, 118 insertions(+), 16 deletions(-)
diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out
index 55d34666d87..e8dfab1d9fc 100644
--- a/contrib/pg_overexplain/expected/pg_overexplain.out
+++ b/contrib/pg_overexplain/expected/pg_overexplain.out
@@ -377,14 +377,15 @@ $$);
(15 rows)
-- Create an index, and then attempt to force a nested loop with inner index
--- scan so that we can see parameter-related information. Also, let's try
--- actually running the query, but try to suppress potentially variable output.
+-- scan so that we can see parameter-related information.
CREATE INDEX ON vegetables (id);
ANALYZE vegetables;
SET enable_hashjoin = false;
SET enable_material = false;
SET enable_mergejoin = false;
SET enable_seqscan = false;
+-- Let's try actually running the query, but try to suppress potentially
+-- variable output.
SELECT explain_filter($$
EXPLAIN (BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF, ANALYZE, DEBUG)
SELECT * FROM vegetables v1, vegetables v2 WHERE v1.id = v2.id;
@@ -440,6 +441,41 @@ $$);
Parse Location: 0 to end
(47 rows)
+-- Test the RANGE_TABLE option with a case that involves an outer join.
+SELECT explain_filter($$
+EXPLAIN (RANGE_TABLE, COSTS OFF)
+SELECT * FROM daucus d LEFT JOIN brassica b ON d.id = b.id;
+$$);
+ explain_filter
+---------------------------------------------------------
+ Nested Loop Left Join
+ Outer Join RTIs: 3
+ -> Index Scan using daucus_id_idx on daucus d
+ Scan RTI: 1
+ -> Index Scan using brassica_id_idx on brassica b
+ Index Cond: (id = d.id)
+ Scan RTI: 2
+ RTI 1 (relation, in-from-clause):
+ Alias: d ()
+ Eref: d (id, name, genus)
+ Relation: daucus
+ Relation Kind: relation
+ Relation Lock Mode: AccessShareLock
+ Permission Info Index: 1
+ RTI 2 (relation, in-from-clause):
+ Alias: b ()
+ Eref: b (id, name, genus)
+ Relation: brassica
+ Relation Kind: relation
+ Relation Lock Mode: AccessShareLock
+ Permission Info Index: 2
+ RTI 3 (join, in-from-clause):
+ Eref: unnamed_join (id, name, genus, id, name, genus)
+ Join Type: Left
+ Unprunable RTIs: 1 2
+(25 rows)
+
+-- Restore default settings.
RESET enable_hashjoin;
RESET enable_material;
RESET enable_mergejoin;
diff --git a/contrib/pg_overexplain/pg_overexplain.c b/contrib/pg_overexplain/pg_overexplain.c
index bd70b6d9d5e..92cfd8af2eb 100644
--- a/contrib/pg_overexplain/pg_overexplain.c
+++ b/contrib/pg_overexplain/pg_overexplain.c
@@ -248,6 +248,27 @@ overexplain_per_node_hook(PlanState *planstate, List *ancestors,
overexplain_bitmapset("RTIs",
((Result *) plan)->relids,
es);
+ break;
+
+ case T_MergeJoin:
+ case T_NestLoop:
+ case T_HashJoin:
+ {
+ Join *join = (Join *) plan;
+
+ /*
+ * 'ojrelids' is only meaningful for non-inner joins, but
+ * if it somehow ends up set for an inner join, print it
+ * anyway.
+ */
+ if (join->jointype != JOIN_INNER ||
+ join->ojrelids != NULL)
+ overexplain_bitmapset("Outer Join RTIs",
+ join->ojrelids,
+ es);
+ break;
+ }
+
default:
break;
}
diff --git a/contrib/pg_overexplain/sql/pg_overexplain.sql b/contrib/pg_overexplain/sql/pg_overexplain.sql
index 42e275ac2f9..351b69757cf 100644
--- a/contrib/pg_overexplain/sql/pg_overexplain.sql
+++ b/contrib/pg_overexplain/sql/pg_overexplain.sql
@@ -86,18 +86,28 @@ INSERT INTO vegetables (name, genus)
$$);
-- Create an index, and then attempt to force a nested loop with inner index
--- scan so that we can see parameter-related information. Also, let's try
--- actually running the query, but try to suppress potentially variable output.
+-- scan so that we can see parameter-related information.
CREATE INDEX ON vegetables (id);
ANALYZE vegetables;
SET enable_hashjoin = false;
SET enable_material = false;
SET enable_mergejoin = false;
SET enable_seqscan = false;
+
+-- Let's try actually running the query, but try to suppress potentially
+-- variable output.
SELECT explain_filter($$
EXPLAIN (BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF, ANALYZE, DEBUG)
SELECT * FROM vegetables v1, vegetables v2 WHERE v1.id = v2.id;
$$);
+
+-- Test the RANGE_TABLE option with a case that involves an outer join.
+SELECT explain_filter($$
+EXPLAIN (RANGE_TABLE, COSTS OFF)
+SELECT * FROM daucus d LEFT JOIN brassica b ON d.id = b.id;
+$$);
+
+-- Restore default settings.
RESET enable_hashjoin;
RESET enable_material;
RESET enable_mergejoin;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 8af091ba647..e516a7ce82b 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -232,14 +232,18 @@ static BitmapOr *make_bitmap_or(List *bitmapplans);
static NestLoop *make_nestloop(List *tlist,
List *joinclauses, List *otherclauses, List *nestParams,
Plan *lefttree, Plan *righttree,
- JoinType jointype, bool inner_unique);
+ JoinType jointype,
+ Relids ojrelids,
+ bool inner_unique);
static HashJoin *make_hashjoin(List *tlist,
List *joinclauses, List *otherclauses,
List *hashclauses,
List *hashoperators, List *hashcollations,
List *hashkeys,
Plan *lefttree, Plan *righttree,
- JoinType jointype, bool inner_unique);
+ JoinType jointype,
+ Relids ojrelids,
+ bool inner_unique);
static Hash *make_hash(Plan *lefttree,
List *hashkeys,
Oid skewTable,
@@ -253,7 +257,9 @@ static MergeJoin *make_mergejoin(List *tlist,
bool *mergereversals,
bool *mergenullsfirst,
Plan *lefttree, Plan *righttree,
- JoinType jointype, bool inner_unique,
+ JoinType jointype,
+ Relids ojrelids,
+ bool inner_unique,
bool skip_mark_restore);
static Sort *make_sort(Plan *lefttree, int numCols,
AttrNumber *sortColIdx, Oid *sortOperators,
@@ -4189,6 +4195,7 @@ create_nestloop_plan(PlannerInfo *root,
Plan *outer_plan;
Plan *inner_plan;
Relids outerrelids;
+ Relids ojrelids;
List *tlist = build_path_tlist(root, &best_path->jpath.path);
List *joinrestrictclauses = best_path->jpath.joinrestrictinfo;
List *joinclauses;
@@ -4255,6 +4262,11 @@ create_nestloop_plan(PlannerInfo *root,
replace_nestloop_params(root, (Node *) otherclauses);
}
+ /* Identify any outer joins computed at this level */
+ ojrelids = bms_difference(best_path->jpath.path.parent->relids,
+ bms_union(best_path->jpath.outerjoinpath->parent->relids,
+ best_path->jpath.innerjoinpath->parent->relids));
+
/*
* Identify any nestloop parameters that should be supplied by this join
* node, and remove them from root->curOuterParams.
@@ -4326,6 +4338,7 @@ create_nestloop_plan(PlannerInfo *root,
outer_plan,
inner_plan,
best_path->jpath.jointype,
+ ojrelids,
best_path->jpath.inner_unique);
copy_generic_path_info(&join_plan->join.plan, &best_path->jpath.path);
@@ -4340,6 +4353,7 @@ create_mergejoin_plan(PlannerInfo *root,
MergeJoin *join_plan;
Plan *outer_plan;
Plan *inner_plan;
+ Relids ojrelids;
List *tlist = build_path_tlist(root, &best_path->jpath.path);
List *joinclauses;
List *otherclauses;
@@ -4418,6 +4432,11 @@ create_mergejoin_plan(PlannerInfo *root,
mergeclauses = get_switched_clauses(best_path->path_mergeclauses,
best_path->jpath.outerjoinpath->parent->relids);
+ /* Identify any outer joins computed at this level */
+ ojrelids = bms_difference(best_path->jpath.path.parent->relids,
+ bms_union(outer_path->parent->relids,
+ inner_path->parent->relids));
+
/*
* Create explicit sort nodes for the outer and inner paths if necessary.
*/
@@ -4678,6 +4697,7 @@ create_mergejoin_plan(PlannerInfo *root,
outer_plan,
inner_plan,
best_path->jpath.jointype,
+ ojrelids,
best_path->jpath.inner_unique,
best_path->skip_mark_restore);
@@ -4695,6 +4715,7 @@ create_hashjoin_plan(PlannerInfo *root,
Hash *hash_plan;
Plan *outer_plan;
Plan *inner_plan;
+ Relids ojrelids;
List *tlist = build_path_tlist(root, &best_path->jpath.path);
List *joinclauses;
List *otherclauses;
@@ -4843,6 +4864,11 @@ create_hashjoin_plan(PlannerInfo *root,
hash_plan->rows_total = best_path->inner_rows_total;
}
+ /* Identify any outer joins computed at this level */
+ ojrelids = bms_difference(best_path->jpath.path.parent->relids,
+ bms_union(best_path->jpath.outerjoinpath->parent->relids,
+ best_path->jpath.innerjoinpath->parent->relids));
+
join_plan = make_hashjoin(tlist,
joinclauses,
otherclauses,
@@ -4853,6 +4879,7 @@ create_hashjoin_plan(PlannerInfo *root,
outer_plan,
(Plan *) hash_plan,
best_path->jpath.jointype,
+ ojrelids,
best_path->jpath.inner_unique);
copy_generic_path_info(&join_plan->join.plan, &best_path->jpath.path);
@@ -5925,6 +5952,7 @@ make_nestloop(List *tlist,
Plan *lefttree,
Plan *righttree,
JoinType jointype,
+ Relids ojrelids,
bool inner_unique)
{
NestLoop *node = makeNode(NestLoop);
@@ -5937,6 +5965,7 @@ make_nestloop(List *tlist,
node->join.jointype = jointype;
node->join.inner_unique = inner_unique;
node->join.joinqual = joinclauses;
+ node->join.ojrelids = ojrelids;
node->nestParams = nestParams;
return node;
@@ -5953,6 +5982,7 @@ make_hashjoin(List *tlist,
Plan *lefttree,
Plan *righttree,
JoinType jointype,
+ Relids ojrelids,
bool inner_unique)
{
HashJoin *node = makeNode(HashJoin);
@@ -5969,6 +5999,7 @@ make_hashjoin(List *tlist,
node->join.jointype = jointype;
node->join.inner_unique = inner_unique;
node->join.joinqual = joinclauses;
+ node->join.ojrelids = ojrelids;
return node;
}
@@ -6008,6 +6039,7 @@ make_mergejoin(List *tlist,
Plan *lefttree,
Plan *righttree,
JoinType jointype,
+ Relids ojrelids,
bool inner_unique,
bool skip_mark_restore)
{
@@ -6027,6 +6059,7 @@ make_mergejoin(List *tlist,
node->join.jointype = jointype;
node->join.inner_unique = inner_unique;
node->join.joinqual = joinclauses;
+ node->join.ojrelids = ojrelids;
return node;
}
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 95c5cd05339..51b028e702e 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2432,13 +2432,13 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
/*
* Now we need to fix up the targetlist and qpqual, which are logically
- * above the join. This means that, if it's not an inner join, any Vars
- * and PHVs appearing here should have nullingrels that include the
- * effects of the outer join, ie they will have nullingrels equal to the
- * input Vars' nullingrels plus the bit added by the outer join. We don't
- * currently have enough info available here to identify what that should
- * be, so we just tell fix_join_expr to accept superset nullingrels
- * matches instead of exact ones.
+ * above the join. This means that, if it's an outer join with non-empty
+ * ojrelids, any Vars and PHVs appearing here should have nullingrels that
+ * include the effects of the outer join, ie they will have nullingrels
+ * equal to the input Vars' nullingrels plus the bit added by the outer
+ * join. We don't currently have enough info available here to identify
+ * what that should be, so we just tell fix_join_expr to accept superset
+ * nullingrels matches instead of exact ones.
*/
join->plan.targetlist = fix_join_expr(root,
join->plan.targetlist,
@@ -2446,7 +2446,7 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
inner_itlist,
(Index) 0,
rtoffset,
- (join->jointype == JOIN_INNER ? NRM_EQUAL : NRM_SUPERSET),
+ (bms_is_empty(join->ojrelids) ? NRM_EQUAL : NRM_SUPERSET),
NUM_EXEC_TLIST((Plan *) join));
join->plan.qual = fix_join_expr(root,
join->plan.qual,
@@ -2454,7 +2454,7 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
inner_itlist,
(Index) 0,
rtoffset,
- (join->jointype == JOIN_INNER ? NRM_EQUAL : NRM_SUPERSET),
+ (bms_is_empty(join->ojrelids) ? NRM_EQUAL : NRM_SUPERSET),
NUM_EXEC_QUAL((Plan *) join));
pfree(outer_itlist);
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index c4393a94321..0b6e5144325 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -945,6 +945,7 @@ typedef struct CustomScan
* inner_unique each outer tuple can match to no more than one inner tuple
* joinqual: qual conditions that came from JOIN/ON or JOIN/USING
* (plan.qual contains conditions that came from WHERE)
+ * ojrelids: outer joins completed at this level
*
* When jointype is INNER, joinqual and plan.qual are semantically
* interchangeable. For OUTER jointypes, the two are *not* interchangeable;
@@ -969,6 +970,7 @@ typedef struct Join
bool inner_unique;
/* JOIN quals (in addition to plan.qual) */
List *joinqual;
+ Bitmapset *ojrelids;
} Join;
/* ----------------
--
2.39.5 (Apple Git-154)