From: Chengpeng Yan <chengpeng_yan@outlook.com>
To: John Naylor <johncnaylorls@gmail.com>
CC: David Rowley <dgrowleyml@gmail.com>, Richard Guo <guofenglinux@gmail.com>,
	"imchifan@163.com" <imchifan@163.com>, "pgsql-bugs@lists.postgresql.org"
	<pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown
 on count() with EXCLUDE CURRENT ROW
Thread-Topic: BUG #19533: Wrong results from WindowAgg run-condition pushdown
 on count() with EXCLUDE CURRENT ROW
Thread-Index: 
 AQHdBjQ36OrnPlHeFUa7BV8yKQiSCrZSnoYAgAIUgwCAANtUAIAFoBMAgABckQCAAPf4gIACRwEAgAAkboA=
Date: Sun, 5 Jul 2026 12:54:38 +0000
Message-ID: <D1C82EBB-54F6-420E-9006-3A737B0E70A6@outlook.com>
References: <19533-413a1014e5d0e766@postgresql.org>
 <EC273387-5C73-4C9C-BF30-8BA260A90FFB@outlook.com>
 <CAMbWs4-6umOecRifmC88RW0SHWDAvb4c3-VD8XQ3NJufsUyUOQ@mail.gmail.com>
 <CAApHDvqHXZpF9pPStLqPp5O=G2gVpPD959bMTRRkLjdy4Tvx7g@mail.gmail.com>
 <CAApHDvoJ07GM6V2QWyJAgsJcM-dcFS+nwPq4Um=-NsXv5PmoCw@mail.gmail.com>
 <CANWCAZbiYQ+=CHAaftwp2x83Gy6tJCB8GUe15yO5DW-cpp0Mxg@mail.gmail.com>
 <CAApHDvrN-HL=dSBez=pme5RQKCiH7SrsMEFV-eQRJN3Vs94ZEQ@mail.gmail.com>
 <CANWCAZap+k7-E2f-XoC36Hk4izZjbN8w2c9OuomummGfZ5d20w@mail.gmail.com>
In-Reply-To: 
 <CANWCAZap+k7-E2f-XoC36Hk4izZjbN8w2c9OuomummGfZ5d20w@mail.gmail.com>
Accept-Language: zh-CN, en-US
Content-Language: en-US
x-ms-exchange-messagesentrepresentingtype: 1
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: TYYPR01MB14116:EE_|TY4PR01MB14451:EE_
x-ms-office365-filtering-correlation-id: 00d7f3df-9dd4-4dd2-f855-08deda9492cf
x-ms-exchange-slblob-mailprops: 
 WaIXnCbdHrO+2YmbT5jW+WUSUt4IT5vR+UhljfdES4o7xkYZfrtQGlAAUDbABRU3eBDP6NVp17wCU2B7/QDRmcIPHX9T+37F0IWD0HxmEYSWs9V9uh2hKVQLqkStApn3qsv6WvKXc8zJOz+4OqyEbHusI+r42yekToTMzgM5EufSyvEs8fxjTPeXZ4CoZJAKT3AXnxEbWZ5D7m0nRLZagfxaDEY4kcRpYuqIBG4/2z1Yw1bI+54KaM0CrPux4FS0hfa84K9knynR+gIE7S/sKXYH478WeSptflmQLgd2slSBHqXq+CdHtMhf09KzSzRE7IOAQ3z/x3NqVkIKYO5xQkn/lwEwvIAbdunXpLR88b5DnXBrRukWRtrloTcpD3RG3JsB6zjxXsc8MGpKIxEeNMuH4gayIubYcF62oF0mdLtgO4n+BSC7NhzDk6iuVK1m5vtwGOQijWN0hOIrUgqpCKgXRaEQjz8PC7iHZli0x8L5nrcnFMnSKmiw+oP8C5yaxVXeBUR53i4BcNhGHfSK73/LWzv9TBcwb2jwmxNvsG+lvbL/HM4268hh59aShsSntbpiP4ofuZdJ/+onLFUSLBs+0fWP8lFmCKU47yt8SBLtjzpavSSg/XhZoUGRwzrhEK5+bKFjb+4tZIfnKDiEredDqv/6ZY1kVVWSFXY/0coqve1ww2YIYtDz/zz/VQZ9XguezvFSSR20bnOLZ8ewizkVqnp26te6OMnlvZ/DImiY8WTD4ZwHmV9YWvc/1XET7tnvvE38ai0=
x-microsoft-antispam: 
 BCL:0;ARA:14566002|8062599012|8060799015|51005399006|19110799012|31061999003|41001999006|24021099003|15080799012|25010399006|22091999003|24121999003|40105399003|102099032|440099028|3412199025;
x-microsoft-antispam-message-info: 
 =?us-ascii?Q?NJ+JDLjGeA9ys/20lJQXIln7wS4mp+/xR2hc5Z/1opmAdWrEt4OWeFI7oVvg?=
 =?us-ascii?Q?DCEFEqIjidbYFZHfc/6f2KhLjjr2IjHySF+y04uyZ7W2sF5XYw06oMlmzBjn?=
 =?us-ascii?Q?sFFzs4tc7o5hENvTIqv2shJRxNEhClDpUFInKNazeXZM/WGjjWjJUjebjE9a?=
 =?us-ascii?Q?1rUTd5WhaGlaoLgl1TaJSmH3pSttuN47syUggbToYwmL9EnsaYV+VSqiSviY?=
 =?us-ascii?Q?FOMhTqS6PyvIt+g3J7YzkGvp2A1pCqbATJEwFAcW9KpGMI05Y2oblQzP4xUM?=
 =?us-ascii?Q?gbVdWDMUqQTwPeIDAbwulolu6PScRPb329X1zc9Sz2ORYjtc6Zq0M4ZYhTyz?=
 =?us-ascii?Q?TmtdIpFGLS3O6BjG/CQw0Z3eQbm8gEvsUN26Qmdt4sHQbDF7pbyV+b0M3eyM?=
 =?us-ascii?Q?kYDQD9mzDI1xg7ZGygEcSjRwTf5SNdtGWZIuniZRhToeFVUK7yDzWlWq7xQ5?=
 =?us-ascii?Q?LJPs1f0ILv4ySPjEDu+QLtprglirUx4b1g2ZvprsZrvtUK7G9bbEb0PmGylX?=
 =?us-ascii?Q?EuGMvkdAYcADa6Sw/d1vIjGVFuEmJjI8oHTkNyBSikuS35cD4hLfcXHH3Vfu?=
 =?us-ascii?Q?H9aE1rIZiU3TpayNSqIAK4INaJ+I6j5PoNi8AI22UKbpqIuT7GAv5HkOvBPz?=
 =?us-ascii?Q?k7EUohTBsVDXgxeag73YU7V+536t2nVOnM9ZKmH6JLbZ2uFBlg8XjU+8Wg66?=
 =?us-ascii?Q?Rxl9YtCFrKKU1DokkRS5IOZc+Hjw2PGX3PbN/fj4rdhWfZGFUc4eaHjzcvyb?=
 =?us-ascii?Q?rvn3lg306wKSx56Qx8lfMrkzX+2mz5IReGjpLwzieqG/h7r1F9Y+iluvow1T?=
 =?us-ascii?Q?HfrGu4rKZm39b7ulIgTLlG+x6QkGowGNTNQfkc4TmAwtWU6bJPbiuWcSrqvc?=
 =?us-ascii?Q?1fAh5VirJ0vM1JESOyTAN4AImbNWy8NyH5fwTFMY/No8UeO1W+LoqZTeWRN6?=
 =?us-ascii?Q?5KtcKILYZAXMh/L2McOH9eB/mFGzB+ElKjzD8IrBz6wr27HT+brIrgx7lo9g?=
 =?us-ascii?Q?MQC9hD1OsTJWBDQgmS2Ybj4TfQzelpRHOkv6LTNbByNQ/CU=3D?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?KzGACBwxKRWlQfgeokAEArQu1b4GEHOs8sRLPRqwBus4js2x9+Cflyk4+IZa?=
 =?us-ascii?Q?JQi3tPJKainDtvkWKdtmnDa1l6h5TQaZehcmfk0nbyzrci2+kdcBvWTGckE/?=
 =?us-ascii?Q?pWJvA1pVDCSojw9wUBTPlstc6QoQjqGBO2KcpVVk7aNzxxbCWs6V98AXbMTh?=
 =?us-ascii?Q?CwXGXgKnuE5OfTCJh68LVsFPFkpB9v74oQGTT0McptXnBFCWEE5Vy/00NITU?=
 =?us-ascii?Q?3vh74Gh4YLUXGGVVCBEtiBPJhh3v11vfPTW+V0BbDPZ2eUNTS0PkMxCFN317?=
 =?us-ascii?Q?xXFrNjD+P5X4VbPfa5IV3Fs2rZGn6o2Ps5R8zaxydeADXew0ahLiY9V8PVid?=
 =?us-ascii?Q?uNoHQjIJ5JaC4MVYNH+02yGm2aXBKbHj2U/i1tic2VnqF7CrIQqpsK2f4i7v?=
 =?us-ascii?Q?/8hz2XKsBL7yW8U+zkEeKEq2pa+K0xGQdn9SdCRhlLfVTTYb1oDEm/AcSzLY?=
 =?us-ascii?Q?pEBL3DPaeDHQ+PjMICkPIHQ48NLL0jkN8YwSj2HatfpammKziEjn+ll3NJz+?=
 =?us-ascii?Q?Sv/5OJnZ9PEYCVr6jLRrmKL1OyCSWKDawc3KDFrb/6tl2AMjq4xxzjKzxFlt?=
 =?us-ascii?Q?a7l0qpsyM8slNPDtumkfSB/TP2UNZ83WiTZglgrY1B4aUib/TXEDrQoYvyIW?=
 =?us-ascii?Q?qcgcGd9uDhS2BgbFkj00OfK9lWbJkSKD2XsCGlR9nE2ANeWYLdCZLrVks3ma?=
 =?us-ascii?Q?wn8acw6ErTxx2FMo76MNcNpo59eRS7mJ6wyWzk6kN2MJDC3lBvIMbbnh0HRw?=
 =?us-ascii?Q?As3g08OVCvkqL6mjCOae5rfPfWJUFUc1YSIrJwXeLxditqEv9l1EgDodvMHT?=
 =?us-ascii?Q?chqHPmGF4v6Tw157y2VcDt31vyWwxTAV3uFnE9aV+C9NYxd87M0k5U+2wOHY?=
 =?us-ascii?Q?ZDKLqCNQ9znvdsyay3fqGNyLlFRAhC4Sl8aa00/YPku1HwnbnwCGTleReIdH?=
 =?us-ascii?Q?m10ELEGGVce+LRIP3ECP7eCECr8bIC0eVmTjJPOCT7RXluqdzZ1ZKsjd+oo/?=
 =?us-ascii?Q?NpvON83lp3hKTjQKBiaKubQ/C5sfegUkx5DyMVShJSaGYclM365o1Vr6y4K/?=
 =?us-ascii?Q?9Qa/SJNbSW5hfUVWEvOTdD8f99ZblgeW01BEsIBnjbruPZ0gj9zzEbVSS9vJ?=
 =?us-ascii?Q?xsuywgNq4n0ZZTSdgBuZRg7MCareE2u9NU68JdW3UI08pabwKHpJBDar5dl1?=
 =?us-ascii?Q?twPzTc09fhB3nxOCU0Hx6/wmaN23Mc2r7W2NERRK+Mwf1Oavg9U52hKJdD4c?=
 =?us-ascii?Q?YKVtw6TYJtijOC/10oUpN6M2uScw6p3n4fBZSk9XwhTSRMW/UxtcsJRQ16H0?=
 =?us-ascii?Q?5TL6Kfgdp5yA766hWmof1ivSmmYAz7F/GYq7FYKgL7MuEc57A1xxyWM26k1/?=
 =?us-ascii?Q?7wZ5xEdn0OO3AOa5ps8Y0AQW8t8Il2jvG5U+4fk3ZoBPLknUcQ=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-ID: <83D77673034EF643A83B4F257D8A490B@jpnprd01.prod.outlook.com>
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: TYYPR01MB14116.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 00d7f3df-9dd4-4dd2-f855-08deda9492cf
X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Jul 2026 12:54:38.6078
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY4PR01MB14451
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/D1C82EBB-54F6-420E-9006-3A737B0E70A6%40outlook.com>
Precedence: bulk

Hi,

> On Jul 5, 2026, at 18:44, John Naylor <johncnaylorls@gmail.com> wrote:
>=20
> I was going to share the three examples, but then I had a better idea:
> I had Claude generate a PL/pgSQL script to exhaustively (or something
> close to it) iterate through the the whole space on a few different
> data set shapes, look for plans with run conditions, and compare the
> result to the "c+0" oracle. (attached as
> count_runcondition_matrix.sql). On master, it found 295 (!) distinct
> window specs that give wrong answers with run conditions on at least
> one data set (attached as runcond-failures-master.txt). With v2, that
> number falls to 162, which is still a surprisingly large number
> (attached as runcond-failures-v2.txt). This script takes a while to
> run, so it's probably not a good basis for a regression test, but it
> seems useful to test against, and it can be modified to test
> hypotheses.
>=20
> I then had Claude generate two patches against master, attached as v3.
> One is the minimal fix to get the above test matrix to pass. I didn't
> try to determine how many currently working cases got lost that way.
> The other is an attempt to provide complete coverage of all
> monotonicity proofs. The latter is quite an indigestible mess, so we
> probably cannot commit something like that, but I think a reasonable
> takeaway is that EXCLUDE presents a really difficult set of cases.
>=20
> I'll also note that two of v2's tests are valid on the data they work
> on, but are not valid in general. I've attached v2-wrong-test-repo.sql
> to demonstrate.

Thanks for putting together the matrix script. That seems like a very
good way to test hypotheses here.

I have also been thinking about this tradeoff while trying to put
together a patch for it. It looks possible to prove more EXCLUDE cases,
but the code and the test space seem to get complicated quickly. My
current inclination is to be conservative here: return
`MONOTONICFUNC_NONE` unless a case is clearly proven safe. The matrix
results seem to support that direction. That may miss some
run-condition opportunities, but it avoids relying on a monotonicity
claim that is not yet fully justified.

This is also close to why the v1 patch I posted earlier simply disabled
the EXCLUDE cases. That version missed the no-ORDER-BY issue, but the
conservative part matches the direction of your minimal v3: do not try
to infer monotonicity for excluded frames unless the case is very
simple.

One extra axis I noticed in the matrix is FILTER. It currently
enumerates `count(*)` and `count(e)`, but not `count(...) FILTER (...)`,
for example:

```
count(*) FILTER (WHERE ...)
```

That is not the same as plain `count(*)`, and it makes the set of
combinations even larger.

I was about to send a WIP patch that is very close to your minimal v3.
The main code difference is this guard:

```
int exclusion =3D frameOptions & FRAMEOPTION_EXCLUSION;
bool plain_count_star =3D req->window_func->winfnoid =3D=3D F_COUNT_ &&
req->window_func->aggfilter =3D=3D NULL;

if (exclusion &&
(exclusion !=3D FRAMEOPTION_EXCLUDE_CURRENT_ROW || !plain_count_star))
{
req->monotonic =3D MONOTONICFUNC_NONE;
PG_RETURN_POINTER(req);
}
```

So compared with the minimal v3 rule that rejects all EXCLUDE cases,
this only keeps plain `count(*) EXCLUDE CURRENT ROW`, with no FILTER.
The reason is that removing the current row removes a fixed
contribution. Everything else with EXCLUDE returns
`MONOTONICFUNC_NONE`. I am not strongly attached to keeping that case
if people prefer the simpler minimal fix, but I think the general shape
should still be reject-by-default rather than allow-by-default.

So I agree with the takeaway from your complete-coverage experiment:
there are valid optimizations in this area, but proving all of them
seems too complex for a bug fix. Starting from the minimal safe rule
and adding back only small, clearly proven cases seems safer to me.

--
Best regards,
Chengpeng Yan