MIME-Version: 1.0
References: 
 <CAMbWs48jzLrPt1J_00ZcPZXWUQKawQOFE8ROc-ADiYqsqrpBNw@mail.gmail.com>
 <CAJgoLk+d_P5sKrx-SZt01Acm_j0QnWn6aKJzFJ=waRu_3C8AoQ@mail.gmail.com>
 <CAHewXNkdhBrQR3mRmLw6FThgXd-tD2LmgfLreb+OeiyXmO2PPQ@mail.gmail.com>
 <CAMbWs49zScoONs0151cmgoo5cttkkK2cGLx228+Wok-h8XoyOg@mail.gmail.com>
In-Reply-To: 
 <CAMbWs49zScoONs0151cmgoo5cttkkK2cGLx228+Wok-h8XoyOg@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Mon, 1 Jun 2026 15:57:39 +0800
Message-ID: 
 <CAHewXNna2r7cbwuotFJBdsHAedu1hZ1OD1Aj_hoRDOmFw-7z2A@mail.gmail.com>
Subject: Re: Eager aggregation, take 3
To: Richard Guo <guofenglinux@gmail.com>
Cc: Radim Marek <radim@boringsql.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXNna2r7cbwuotFJBdsHAedu1hZ1OD1Aj_hoRDOmFw-7z2A%40mail.gmail.com>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> =E4=BA=8E2026=E5=B9=B46=E6=9C=881=E6=
=97=A5=E5=91=A8=E4=B8=80 15:19=E5=86=99=E9=81=93=EF=BC=9A
>
> Thanks for the report.  This is a bug.  We should never push a partial
> aggregation down to a relation on the inner (RHS) side of a semi/anti
> join.  A semi/anti join does not preserve its inner rows in the join
> output, so a partial aggregate computed on the inner side would not
> survive the join and could not be combined by the final aggregation.
>
> > I haven't thought about it too deeply yet. Maybe we can do something
> > in the make_grouped_join_rel().
> > ...
> > if (sjinfo->jointype =3D=3D JOIN_ANTI || sjinfo->jointype =3D=3D JOIN_S=
EMI)
> >     return;
> > ...
>
> That does fix the reported case, but I think it's too broad: it also
> disables pushing a partial aggregate to the outer side of a semi/anti
> join, which is valid.  And by the time we reach make_grouped_join_rel
> the grouped relation for the inner-side relation has already been
> built, so it would just go unused.

Yes, checking only the jointype and concluding that partial agg is not allo=
wed
will cause us to miss some optimization opportunities for the outer
side of the semi or anti-join.


> So I'd rather fix it in eager_aggregation_possible_for_relation, right
> next to the existing outer-join check, by rejecting a relation that
> lies on the inner side of a semijoin/antijoin.  See attached.

The attached LGTM.


--=20
Thanks,
Tender Wang