Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables

Tender Wang <tndrwang(at)gmail(dot)com> 于2026年3月18日周三 09:12写道：
>
> Alexander Korotkov <aekorotkov(at)gmail(dot)com> 于2026年3月18日周三 01:46写道：
> >
> > On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> > > Kirill Reshke <reshkekirill(at)gmail(dot)com> 于2026年3月17日周二 21:24写道：
> > > >
> > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <tndrwang(at)gmail(dot)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