public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: Tender Wang <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
Date: Wed, 18 Mar 2026 10:56:55 +0500
Message-ID: <CALdSSPiL5pFNvvh9HyDQ7syzF6jOvQirC5nQWaC_n1_NQy5-PQ@mail.gmail.com> (raw)
In-Reply-To: <CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com>
References: <[email protected]>
<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>
<CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com>
On Wed, 18 Mar 2026 at 10:44, Tender Wang <[email protected]> wrote:
>
> Tender Wang <[email protected]> 于2026年3月18日周三 09:12写道：
> >
> > Alexander Korotkov <[email protected]> 于2026年3月18日周三 01:46写道：
> > >
> > > On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <[email protected]> wrote:
> > > > Kirill Reshke <[email protected]> 于2026年3月17日周二 21:24写道：
> > > > >
> > > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <[email protected]> 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
Hi!
Your patch looks solid. ChangeVarNodesWalkExpression looks like a
generic rewriter utility function, so I was wondering why we never got
complaints about bugs related it. But this functions is reachable only
when SJE optimisation is allowed, so looks like this explains the
issue.
--
Best regards,
Kirill Reshke
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
In-Reply-To: <CALdSSPiL5pFNvvh9HyDQ7syzF6jOvQirC5nQWaC_n1_NQy5-PQ@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