MIME-Version: 1.0
References: <19435-3cc1a87f291129f1@postgresql.org> <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>
 <CAPpHfducqLJ=o3LkoPKGfZJVQuuei+P=2oUF6hX6rzHTZSxoyA@mail.gmail.com>
 <a78fe5d4-e6b8-4b3c-9cfd-135edbb68e4c@gmail.com> <CAPpHfduTWFCHaK8U7bDfYid5pjVA=FHG1b0nTEMFqFKHebGJxQ@mail.gmail.com>
 <a498f5b8-2f17-4ee0-b021-63ff9829b45b@gmail.com> <CALdSSPhpUdY7-5Zg38oS1uRtu5iTFzdo0R7Z2YZD603M9RpJxg@mail.gmail.com>
 <5a039d60-d13b-4cf0-a807-9c7269f06831@gmail.com> <CAPpHfdsyNYEbjjLdsa8i8Ds-5=4pFif1+uCHn3vwzx2Pq5y29A@mail.gmail.com>
 <CAPpHfdsrmAg+aqpjAF4Fdp2c59-dFmwBuNLhNqrxzTguiAKf=w@mail.gmail.com> <afb2c07f-05b7-403c-b10c-ca7390316e94@gmail.com>
In-Reply-To: <afb2c07f-05b7-403c-b10c-ca7390316e94@gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Fri, 27 Mar 2026 09:19:29 +0800
Message-ID: <CAHewXNnt5bSWUirqV7mtkCit592j3h7VA-gOOJW_Ms0x8zv62w@mail.gmail.com>
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by
 Complex Join with Self-Referencing Tables
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Alexander Korotkov <aekorotkov@gmail.com>, Kirill Reshke <reshkekirill@gmail.com>, 
	Fujii Masao <masao.fujii@gmail.com>, ammmkilo@163.com, pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAHewXNnt5bSWUirqV7mtkCit592j3h7VA-gOOJW_Ms0x8zv62w%40mail.gmail.com>
Precedence: bulk

Andrei Lepikhov <lepihov@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=8827=E6=
=97=A5=E5=91=A8=E4=BA=94 03:59=E5=86=99=E9=81=93=EF=BC=9A
>
> On 20/3/26 15:02, Alexander Korotkov wrote:
> > OK. I've pushed this.  Let's go back to
> > restrict_infos_logically_equal().  I'm still not convinced that we
> > need to check if required_relids is singleton.  Why we can ignore
> > outer_relids for singleton, but can't do if, for instance, two
> > relations involved?
>
> Let's continue. In the attachment, the Tender's proposal that I changed
> a little and added some tests.
>
> As you can see in the tests, the SINGLETON limitation keeps duplicates
> of clauses like 'a.x + b.y =3D c'.
> This example shows the main flaw of this approach. Introducing the
> restrict_infos_logically_equal(), we do a little more job than just the
> equal() routine could because of the context where we call this function
> and on which clauses.
> But skipping all other RestrictInfo fields except required_relids seems
> excessive. - see the example with security_level difference - ignoring
> its value, we potentially might remove the clause with enforced security
> level in favour of an unsecured one.

Yes, it seems too strict to require all fields to be equal, but
skipping some fields is unsafe.


> That's more, further some new optimisations might introduce more fields
> into RestrictInfo that should be checked to correctly decide on the
> equality, and we may forget to arrange this specific place.
>

Agree.

> So, formally it works, and making the following replacement, we close
> the singleton issue:
>
> -       if (bms_membership(a->required_relids) =3D=3D BMS_SINGLETON &&
> -               a->security_level =3D=3D b->security_level)
> +       if (bms_equal(a->required_relids, b->required_relids) &&
> +               a->security_level =3D=3D b->security_level &&
> +               a->is_pushed_down =3D=3D b->is_pushed_down)
>

The singleton issue does not seem to be the correct way; I don't dive
deeply to cover all cases.

> but I'm unsure, in general, that this approach is conservative enough.
> Maybe we shouldn=E2=80=99t change this logic and invent one more optimisa=
tion
> =E2=80=98deduplication=E2=80=99 stage later, before the selectivity estim=
ation stage.


--=20
Thanks,
Tender Wang