public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Tender Wang <tndrwang@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>
Cc: Fujii Masao <masao.fujii@gmail.com>
Cc: ammmkilo@163.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
Date: Wed, 18 Mar 2026 13:44:36 +0800
Message-ID: <CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com> (raw)
In-Reply-To: <CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com>
References: <19435-3cc1a87f291129f1@postgresql.org>
<CAHGQGwEEHFnH8DsZbwxdZeiHozm2LCRTLSgL8Qn=5MoN4450ZA@mail.gmail.com>
<CAPpHfdsDQhDqvqi+-UxvyYRP72ASkdWzCn43e2Hj6WsY15opvA@mail.gmail.com>
<CAHewXNmU3E0oXLgvoOtXw7kfTiAZsKu8La_hQ=pyScgxvP45iw@mail.gmail.com>
<CAHewXN=LjuWz3PcyhjdbJAyo+Zs9MisPDRYnSZBUy4PMeKi+zA@mail.gmail.com>
<CALdSSPj1kTTQvmV3H3HMf5P3um8ybxoH3DaTPm+XgdYAur1Q4A@mail.gmail.com>
<CAHewXNndByMu3S+_h4LLDkXA5qrO1s=s-CE8HqUtc9vTA9yrjg@mail.gmail.com>
<CAPpHfdv6gzSTXHJxYSgB8sULadXM4wvhgoQODaOxYCJfagKNPw@mail.gmail.com>
<CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com>
Tender Wang <tndrwang@gmail.com> 于2026年3月18日周三 09:12写道：
>
> Alexander Korotkov <aekorotkov@gmail.com> 于2026年3月18日周三 01:46写道：
> >
> > On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <tndrwang@gmail.com> wrote:
> > > Kirill Reshke <reshkekirill@gmail.com> 于2026年3月17日周二 21:24写道：
> > > >
> > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <tndrwang@gmail.com> wrote:
> > > > Yes, it looks like your analysis is valid. Will you share a patch for
> > > > updating `clause` ?
> > > >
> > > Sorry, it's already the middle of the night here, so I'm afraid I
> > > don't have much time to work on this right now.
> >
> > Thank you for your research. I've written a simple draft patch. It
> > fixes the reported case, but I doubt it is correct in general. I'll
> > continue the investigation.
> I tried the above fix, no error again. But I got a plan like this:
> QUERY PLAN
> -------------------------------------------------------------------------------------------
> Nested Loop Left Join (cost=0.00..115164616.71 rows=7458350250 width=4)
> -> Seq Scan on pg_table_a tom3 (cost=0.00..34.70 rows=2470 width=0)
> -> Materialize (cost=0.00..99509.82 rows=3019575 width=0)
> -> Nested Loop Left Join (cost=0.00..75564.95 rows=3019575 width=0)
> Join Filter: tom2.col_bool
> -> Seq Scan on pg_table_a tom2 (cost=0.00..34.70
> rows=2445 width=5)
> Filter: ((col_bool IS NOT NULL) AND (col_bool IS NOT NULL))
> -> Materialize (cost=0.00..47.05 rows=2470 width=0)
> -> Seq Scan on pg_table_a tom0
> (cost=0.00..34.70 rows=2470 width=0)
> (9 rows)
>
> Filter: ((col_bool IS NOT NULL) AND (col_bool IS NOT NULL))
> This makes me unhappy.
> Your patch gets the same plan.
>
In replace_relid_callback(), we add NullTest to rinfo, but it is not a
logical equal check by restrict_infos_logically_equal().
I think for baserestrictinfo, we can just use rinfo->clause, no need
to check the equality of RestrictInfo.
I tried this way, the plan looks as follows:
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..115776846.35 rows=7498006100 width=4)
-> Seq Scan on pg_table_a tom3 (cost=0.00..34.70 rows=2470 width=0)
-> Materialize (cost=0.00..100038.47 rows=3035630 width=0)
-> Nested Loop Left Join (cost=0.00..75966.32 rows=3035630 width=0)
Join Filter: tom2.col_bool
-> Seq Scan on pg_table_a tom2 (cost=0.00..34.70
rows=2458 width=5)
Filter: (col_bool IS NOT NULL)
-> Materialize (cost=0.00..47.05 rows=2470 width=0)
-> Seq Scan on pg_table_a tom0
(cost=0.00..34.70 rows=2470 width=0)
(9 rows)
No redundant filter anymore.
Please see the attached patch.
--
Thanks,
Tender Wang
Attachments:
[application/octet-stream] 0001-Fix-sje-bug.patch (2.1K, 2-0001-Fix-sje-bug.patch)
download | inline diff:
From 13b720474fb04e9027abb72ae63ec651d6ed80c8 Mon Sep 17 00:00:00 2001
From: Tender Wang <tndrwang@gmail.com>
Date: Wed, 18 Mar 2026 13:29:33 +0800
Subject: [PATCH] Fix sje bug
---
src/backend/optimizer/plan/analyzejoins.c | 11 +++++++---
src/backend/rewrite/rewriteManip.c | 26 ++++++++++++++++++++---
2 files changed, 31 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..4b9b5d563f6 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1635,9 +1635,14 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
int saved_rinfo_serial = a->rinfo_serial;
bool result;
- a->rinfo_serial = b->rinfo_serial;
- result = equal(a, b);
- a->rinfo_serial = saved_rinfo_serial;
+ if (bms_membership(a->required_relids) == BMS_SINGLETON)
+ result = equal(a->clause, b->clause);
+ else
+ {
+ a->rinfo_serial = b->rinfo_serial;
+ result = equal(a, b);
+ a->rinfo_serial = saved_rinfo_serial;
+ }
return result;
}
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..fff566eeb3a 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,29 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varlevelsup == context->sublevels_up)
+ {
+ if (var->varno == context->rt_index)
+ var->varno = context->new_index;
+ var->varnullingrels = adjust_relid_set(var->varnullingrels,
+ context->rt_index,
+ context->new_index);
+ if (var->varnosyn == context->rt_index)
+ var->varnosyn = context->new_index;
+ }
+ return false;
+ }
+ else
+ return expression_tree_walker(node,
+ ChangeVarNodes_walker,
+ (void *) context);
}
/*
--
2.34.1
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tndrwang@gmail.com, aekorotkov@gmail.com, reshkekirill@gmail.com, masao.fujii@gmail.com, ammmkilo@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
In-Reply-To: <CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox