MIME-Version: 1.0
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>
In-Reply-To: <CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Wed, 18 Mar 2026 13:44:36 +0800
Message-ID: <CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com>
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by
 Complex Join with Self-Referencing Tables
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>, Fujii Masao <masao.fujii@gmail.com>, ammmkilo@163.com, 
	pgsql-bugs@lists.postgresql.org
Content-Type: multipart/mixed; boundary="0000000000002758fa064d45f3c1"
Archived-At: <https://www.postgresql.org/message-id/CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g%40mail.gmail.com>
Precedence: bulk

--0000000000002758fa064d45f3c1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Tender Wang <tndrwang@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=8818=E6=97=
=A5=E5=91=A8=E4=B8=89 09:12=E5=86=99=E9=81=93=EF=BC=9A
>
> Alexander Korotkov <aekorotkov@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=
=8818=E6=97=A5=E5=91=A8=E4=B8=89 01:46=E5=86=99=E9=81=93=EF=BC=9A
> >
> > On Tue, Mar 17, 2026 at 3:30=E2=80=AFPM Tender Wang <tndrwang@gmail.com=
> wrote:
> > > Kirill Reshke <reshkekirill@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=
=8817=E6=97=A5=E5=91=A8=E4=BA=8C 21:24=E5=86=99=E9=81=93=EF=BC=9A
> > > >
> > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <tndrwang@gmail.com> wrot=
e:
> > > > Yes, it looks like your analysis is valid. Will you share a patch f=
or
> > > > 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=3D0.00..115164616.71 rows=3D7458350250 widt=
h=3D4)
>    ->  Seq Scan on pg_table_a tom3  (cost=3D0.00..34.70 rows=3D2470 width=
=3D0)
>    ->  Materialize  (cost=3D0.00..99509.82 rows=3D3019575 width=3D0)
>          ->  Nested Loop Left Join  (cost=3D0.00..75564.95 rows=3D3019575=
 width=3D0)
>                Join Filter: tom2.col_bool
>                ->  Seq Scan on pg_table_a tom2  (cost=3D0.00..34.70
> rows=3D2445 width=3D5)
>                      Filter: ((col_bool IS NOT NULL) AND (col_bool IS NOT=
 NULL))
>                ->  Materialize  (cost=3D0.00..47.05 rows=3D2470 width=3D0=
)
>                      ->  Seq Scan on pg_table_a tom0
> (cost=3D0.00..34.70 rows=3D2470 width=3D0)
> (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=3D0.00..115776846.35 rows=3D7498006100 width=
=3D4)
   ->  Seq Scan on pg_table_a tom3  (cost=3D0.00..34.70 rows=3D2470 width=
=3D0)
   ->  Materialize  (cost=3D0.00..100038.47 rows=3D3035630 width=3D0)
         ->  Nested Loop Left Join  (cost=3D0.00..75966.32 rows=3D3035630 w=
idth=3D0)
               Join Filter: tom2.col_bool
               ->  Seq Scan on pg_table_a tom2  (cost=3D0.00..34.70
rows=3D2458 width=3D5)
                     Filter: (col_bool IS NOT NULL)
               ->  Materialize  (cost=3D0.00..47.05 rows=3D2470 width=3D0)
                     ->  Seq Scan on pg_table_a tom0
(cost=3D0.00..34.70 rows=3D2470 width=3D0)
(9 rows)

No redundant filter anymore.

Please see the attached patch.

--=20
Thanks,
Tender Wang

--0000000000002758fa064d45f3c1
Content-Type: application/octet-stream; name="0001-Fix-sje-bug.patch"
Content-Disposition: attachment; filename="0001-Fix-sje-bug.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mmvm8mqq0>
X-Attachment-Id: f_mmvm8mqq0

RnJvbSAxM2I3MjA0NzRmYjA0ZTkwMjdhYmI3MmFlNjNlYzY1MWQ2ZWQ4MGM4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBUZW5kZXIgV2FuZyA8dG5kcndhbmdAZ21haWwuY29tPgpEYXRl
OiBXZWQsIDE4IE1hciAyMDI2IDEzOjI5OjMzICswODAwClN1YmplY3Q6IFtQQVRDSF0gRml4IHNq
ZSBidWcKCi0tLQogc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vYW5hbHl6ZWpvaW5zLmMgfCAx
MSArKysrKysrLS0tCiBzcmMvYmFja2VuZC9yZXdyaXRlL3Jld3JpdGVNYW5pcC5jICAgICAgICB8
IDI2ICsrKysrKysrKysrKysrKysrKysrLS0tCiAyIGZpbGVzIGNoYW5nZWQsIDMxIGluc2VydGlv
bnMoKyksIDYgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvb3B0aW1pemVy
L3BsYW4vYW5hbHl6ZWpvaW5zLmMgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9hbmFseXpl
am9pbnMuYwppbmRleCAxMmU5ZWQwZDBjNy4uNGI5YjVkNTYzZjYgMTAwNjQ0Ci0tLSBhL3NyYy9i
YWNrZW5kL29wdGltaXplci9wbGFuL2FuYWx5emVqb2lucy5jCisrKyBiL3NyYy9iYWNrZW5kL29w
dGltaXplci9wbGFuL2FuYWx5emVqb2lucy5jCkBAIC0xNjM1LDkgKzE2MzUsMTQgQEAgcmVzdHJp
Y3RfaW5mb3NfbG9naWNhbGx5X2VxdWFsKFJlc3RyaWN0SW5mbyAqYSwgUmVzdHJpY3RJbmZvICpi
KQogCWludAkJCXNhdmVkX3JpbmZvX3NlcmlhbCA9IGEtPnJpbmZvX3NlcmlhbDsKIAlib29sCQly
ZXN1bHQ7CiAKLQlhLT5yaW5mb19zZXJpYWwgPSBiLT5yaW5mb19zZXJpYWw7Ci0JcmVzdWx0ID0g
ZXF1YWwoYSwgYik7Ci0JYS0+cmluZm9fc2VyaWFsID0gc2F2ZWRfcmluZm9fc2VyaWFsOworCWlm
IChibXNfbWVtYmVyc2hpcChhLT5yZXF1aXJlZF9yZWxpZHMpID09IEJNU19TSU5HTEVUT04pCisJ
CXJlc3VsdCA9IGVxdWFsKGEtPmNsYXVzZSwgYi0+Y2xhdXNlKTsKKwllbHNlCisJeworCQlhLT5y
aW5mb19zZXJpYWwgPSBiLT5yaW5mb19zZXJpYWw7CisJCXJlc3VsdCA9IGVxdWFsKGEsIGIpOwor
CQlhLT5yaW5mb19zZXJpYWwgPSBzYXZlZF9yaW5mb19zZXJpYWw7CisJfQogCiAJcmV0dXJuIHJl
c3VsdDsKIH0KZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZU1hbmlwLmMg
Yi9zcmMvYmFja2VuZC9yZXdyaXRlL3Jld3JpdGVNYW5pcC5jCmluZGV4IGZlODk3NTRhNzNjLi5m
ZmY1NjZlZWIzYSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcmV3cml0ZS9yZXdyaXRlTWFuaXAu
YworKysgYi9zcmMvYmFja2VuZC9yZXdyaXRlL3Jld3JpdGVNYW5pcC5jCkBAIC03NDQsOSArNzQ0
LDI5IEBAIENoYW5nZVZhck5vZGVzKE5vZGUgKm5vZGUsIGludCBydF9pbmRleCwgaW50IG5ld19p
bmRleCwgaW50IHN1YmxldmVsc191cCkKIGJvb2wKIENoYW5nZVZhck5vZGVzV2Fsa0V4cHJlc3Np
b24oTm9kZSAqbm9kZSwgQ2hhbmdlVmFyTm9kZXNfY29udGV4dCAqY29udGV4dCkKIHsKLQlyZXR1
cm4gZXhwcmVzc2lvbl90cmVlX3dhbGtlcihub2RlLAotCQkJCQkJCQkgIENoYW5nZVZhck5vZGVz
X3dhbGtlciwKLQkJCQkJCQkJICAodm9pZCAqKSBjb250ZXh0KTsKKwlpZiAobm9kZSA9PSBOVUxM
KQorCQlyZXR1cm4gZmFsc2U7CisKKwlpZiAoSXNBKG5vZGUsIFZhcikpCisJeworCQlWYXIJCSAg
ICp2YXIgPSAoVmFyICopIG5vZGU7CisKKwkJaWYgKHZhci0+dmFybGV2ZWxzdXAgPT0gY29udGV4
dC0+c3VibGV2ZWxzX3VwKQorCQl7CisJCQlpZiAodmFyLT52YXJubyA9PSBjb250ZXh0LT5ydF9p
bmRleCkKKwkJCQl2YXItPnZhcm5vID0gY29udGV4dC0+bmV3X2luZGV4OworCQkJdmFyLT52YXJu
dWxsaW5ncmVscyA9IGFkanVzdF9yZWxpZF9zZXQodmFyLT52YXJudWxsaW5ncmVscywKKwkJCQkJ
CQkJCQkJCSAgIGNvbnRleHQtPnJ0X2luZGV4LAorCQkJCQkJCQkJCQkJICAgY29udGV4dC0+bmV3
X2luZGV4KTsKKwkJCWlmICh2YXItPnZhcm5vc3luID09IGNvbnRleHQtPnJ0X2luZGV4KQorCQkJ
CXZhci0+dmFybm9zeW4gPSBjb250ZXh0LT5uZXdfaW5kZXg7CisJCX0KKwkJcmV0dXJuIGZhbHNl
OworCX0KKwllbHNlCisJCXJldHVybiBleHByZXNzaW9uX3RyZWVfd2Fsa2VyKG5vZGUsCisJCQkJ
CQkJCQkgIENoYW5nZVZhck5vZGVzX3dhbGtlciwKKwkJCQkJCQkJCSAgKHZvaWQgKikgY29udGV4
dCk7CiB9CiAKIC8qCi0tIAoyLjM0LjEKCg==
--0000000000002758fa064d45f3c1--