From: Japin Li <japinli@hotmail.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Convert NOT IN sublinks to anti-joins when safe
In-Reply-To: 
 <CAMbWs4_g4+Vc1oKgoTMfo=sEw5h4seMQyC5m1Wx23Kpy21gvHg@mail.gmail.com>
	(Richard Guo's message of "Wed, 4 Mar 2026 18:52:07 +0900")
References: 
 <CAMbWs495eF=-fSa5CwJS6B-BaEi3ARp0UNb4Lt3EkgUGZJwkAQ@mail.gmail.com>
	<CAMbWs49tBNbW7S7VCbTasJEfPQKKU7fbJT2TMoi9uHMhT6rwqw@mail.gmail.com>
	<CAMbWs4-YfL4pnSxg87=6YDdhZD-DYtu1Oa9OvyXaR79q4Xk_mQ@mail.gmail.com>
	<CAMbWs4_V2QM=kUR3s3Jq8GVWAaZtouqpqU46GUuUXtCgAzBriA@mail.gmail.com>
	<CAMbWs4_g4+Vc1oKgoTMfo=sEw5h4seMQyC5m1Wx23Kpy21gvHg@mail.gmail.com>
User-Agent: mu4e 1.12.12; emacs 29.3
Date: Thu, 05 Mar 2026 09:57:48 +0800
Message-ID: 
 <SY7PR01MB1092144FE2B738E660860DED4B67DA@SY7PR01MB10921.ausprd01.prod.outlook.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SY7PR01MB10921:EE_|ME5PR01MB10058:EE_
X-MS-Office365-Filtering-Correlation-Id: 95b6b38f-fc52-4580-29a7-08de7a5a9c53
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|41001999006|15080799012|5072599009|23021999003|8060799015|6090799003|19110799012|461199028|12121999013|440099028|3412199025|40105399003|52005399003;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?UjFlSFlPTXlIMG1yaGlxUGorNmNONnhNWFU1SVM4ekMweFp1aWdtL1VHeWY5?=
 =?utf-8?B?Q3ZpYXJ0OVVBUFBBVUMzWGJjT016cWg0Y0RmVFpRekNEWmhkaXZidjBSZ0U0?=
 =?utf-8?B?dW9td05vUVlYcjdMcWRhaFBFQWQxUzVVdjBXbVE0SXRZR3pNM2p2K0ljNmxV?=
 =?utf-8?B?TFZvRzV4SEpJQUxsUk9Fd21WNEtCcE1GaDBVMlJBcFUzamhGNTBsVWR5Yjh1?=
 =?utf-8?B?cElFMXNPdENsMjFOWk5DL0FaRTdoWlhBeVFtNkxoY1RQZ2dhNU5JR3dpcldT?=
 =?utf-8?B?REZqYnl2S2JHUmIxTnVyVExCZmtDeDdNalFIc0RIdVpMUkVHMjhmbVEwZmpw?=
 =?utf-8?B?cnFROGNlSnFwV2t5Z3JOc092MFQxWWNiY1lNZk84dTJCdFVDUzZMRjk0Z3Bw?=
 =?utf-8?B?Q1JqekFHZGlLOXBZaHgvZ21IN3l2T1BPSkFXK3JSQ1N5SGVxanI4Mzk5QlZq?=
 =?utf-8?B?TldvUEd2dnRWc1AwbW9jRHdnOEk0dTcvWEFnaDdMZHo1cWxzQ0NFVXY1M1Rq?=
 =?utf-8?B?OTc0b3ZSVUhRa0ZQVVc4bStsQ3VNSzkyZTZKNytwRGE3MFp6Y1J1aFVkUzhD?=
 =?utf-8?B?eHFjR1pnSjVkck1EWVpEWUR6QkhZbXdGTzJFSDBCTmtjRldTanNmREYrWHJF?=
 =?utf-8?B?d3hHMWM5YWN2MGNzNjN0THN4N3B1cnBMQWFNb3pLT2NZRzBia2hLeHpKV1hN?=
 =?utf-8?B?NTB5TWYwUWpqRVdlbUEralAyYVA5Z1BCL3U3RDhCNkFQRTBVWGxyRUt1dVNS?=
 =?utf-8?B?dlpRdWxiN00zQjFueS92TFlKNDBjejBYNWRCcDA0dFhwRUVkSVArd2tHSTdI?=
 =?utf-8?B?MURXZzJDRFd5ZkFpNGhpamdGeW56Qk93dHJWRUVTbWQ2cG13dzhCVmV1Ym1E?=
 =?utf-8?B?OElDMW1ka1JXMjJ6b3YzVFRLZ3VJMUxMajlmRnlLZzdRTFo1MWl3WmRtamRP?=
 =?utf-8?B?Z2FwQlJYd2JxVW5MWUxVRWVHRHloNVN3K0tuTWJ1U3UyY2VzU2V5MmRqYndx?=
 =?utf-8?B?ekk3dDRmR043ODdrZDdIVWQrNUIrTUowU2lkc3NOU2RtcVdpRXM2MXVZbXJT?=
 =?utf-8?B?YWxYS0FlM1JVTzBFcFVQakIySU5wMUgvZUk3cW5NSFNOaDl0d2s1bURYeS9a?=
 =?utf-8?B?ZnZqSFlYL1hyVzNvMHZDanZ2dlprWjcrV2tqZlFjS0phcVhtRjNhNGJiUmZU?=
 =?utf-8?B?RUYvU0hDU1VEK3BBREw1ZzZLMUNReHB5SThkY1RaalpFNlZpM1hHckpCODVE?=
 =?utf-8?B?ejJuN2lRZUVidkY3YkdLNWUxQTlLVTRWbDluS0ZpOEZ3akdaMy9TaWdyVzdu?=
 =?utf-8?Q?IQAbb0kv7PET5BHHE1CqbC8pEFPO1fRwrp?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?U3dvS1pnZFdwK09pWGFwWDRuT1Avb1V3ZDVpVkpDZEhubFR5ZndKRnNBUFpC?=
 =?utf-8?B?RkVzMzB5UDg4cWFmOTB2cUVJdnk5dWFZYWliZE9YNlNDZTRhU2sxYVVqdUVJ?=
 =?utf-8?B?Z3hSZVBma3pGc3p2dTZJSDVtaTg2Rmd0OTgvcHQrYVo3a3JJS3lRSmlWVFNy?=
 =?utf-8?B?SGROTzZLZHQ3S05nOW1aeFo1WlV4Rll2S0svZTZ5K2pJdnBqblUwMUtWUmFk?=
 =?utf-8?B?U1QrTnFqUTQyeHI3SmpTSC9BcE9VOXlTb25QVzdhTjZjWG8zdGRUMkFoK0NS?=
 =?utf-8?B?d0FBcG1JbnIyamFZbnAwQ0Exbkk4UzVxS2pxVDVEMDZzdWRiNHlhWXdNbmlT?=
 =?utf-8?B?Wk9tQmg4cEtzR0cwYkV6VDRia003MXhvMytrMW9FMzNzMk5HK1dVaEx5dWlw?=
 =?utf-8?B?NXBnN0dHTS9ld2Rkd0xKV0JLdDhyZnE5UGFBSjFYWTNCT3RmMXQzeWpFc0pP?=
 =?utf-8?B?ZnVqM3lGZm90TnEvT3BRTGxhZDgvejh3enBDVG9ob1M0YjMxSlZoeTNPWnhn?=
 =?utf-8?B?YUF1blhsdkdwQk1ycy9vRTAvcGJHZjZCT3hUUU5SaWNsd3ByRmJzeVZsMmVG?=
 =?utf-8?B?RytIa3UzVVl6TFErWloreDRJcEZCMnpudU9JWDBDb2ZSZk9wS0ZYZGs5Q1Ni?=
 =?utf-8?B?VDErVlUwZHFvTzJFSzR3YUlxa2dITnpyVHlRbXc4REdDYTZZYWNxSS9KaGtZ?=
 =?utf-8?B?dCtra0JEaG96cmU5MzlvdW9xNC8yQjVSSW91WmQ5TVpzdGZoY0ZBQTdxOEV1?=
 =?utf-8?B?eWVJWlNXcm8razhhdE9vK1ZXcE52SWNna3pEcGpFY3pDYzZNSkJOWGwvRXlS?=
 =?utf-8?B?bk1kcmd1SHR1cE1RWDNXdno2N3dMY1lrT0hhQ1dHNDZzdnZTaDZQU0ZFZk5M?=
 =?utf-8?B?VXFNMW5KYWp0YnZlQVFlVzh3dmZZWWtpMGZ4WEtHMWZxSGdhMUV6eXpvWG4r?=
 =?utf-8?B?SndGUnBvSUlhc0J3amF6aUFoN1EyRTNld1ZycDhwcEF4NnZVYnNqYUtQU29N?=
 =?utf-8?B?eFhXMjF1cWpCR3lDVUsxVnFVT3JPTklSdmIzdXh6MVFoL1htWm04TDFYdWo2?=
 =?utf-8?B?Mk1QT0R4Z0owN3RHU3RIYlU5RndBUGh6UHVNd2grdXg0NWpaY2ZYd3dZMGEv?=
 =?utf-8?B?Z3hnTjYrSm5YUVBLYkdVaHBaNUF5dUo4Z3FkVmgxdFd2d2MxeHJxOWxMTUdO?=
 =?utf-8?B?WGpFR25xZkp0akNkeUNaZFYyTElNcEcxSm9hS2gycFh1Y1U4UVpTSElSOG9Q?=
 =?utf-8?B?a1pyZVY0NjJvQVg3RWExeUxpUEdTZlp1TW5tUU5ZR2psb29kZG54VUdZYlRs?=
 =?utf-8?B?dXEremVMZlBHajJYY3grakdQOFdVYWpXME1YaEFXa3FuMnRKSG1LYXFZcVcv?=
 =?utf-8?B?LzduWFFjRW0rWWFxWW50RlRpelJIdCszU2NCak5QamEzamlTMlBZcG43Z0pn?=
 =?utf-8?B?ZWJ5NDNOMXdwR29XWjlEcml5ZlBrOUpUYkNoMWJuNFhRTWJQS21tUjN0SThj?=
 =?utf-8?B?emgrUEN2YUs1Y2VGbGNCWTB1VW5vRjRKYU1lYlJNWkxLZ083Z3ZUZEtET2tY?=
 =?utf-8?B?TGl4eGxlWTExSkFGRGtieERBYlNoS2RJTVRHbk05RVRFSDBYVmlZZ01IblA2?=
 =?utf-8?B?c2lZVGhxK3JGNzY4NkNSMFRrRFlpUDc2K21wOW1iZC85WmhwSzgzYUp5YUlQ?=
 =?utf-8?B?M2pCRzI5WWVnWkVGeFJVZ0VzYjhSb25LMmMwOGU4aXl0bGdIY1JrSmZoaFdQ?=
 =?utf-8?B?b0phUnhFU0xaa0UvazNDTjdzQW1NOEhPSmZHY1JKQkpzSG5yTGQ1b2YybEJI?=
 =?utf-8?B?ZkpWeFJxTXQybW9CK3dJVXN6N0w5VHU1S2NXankvSXdrakZEb3dKVHZtZmpF?=
 =?utf-8?Q?V17Hd5joOBafl?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-feddd.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 95b6b38f-fc52-4580-29a7-08de7a5a9c53
X-MS-Exchange-CrossTenant-AuthSource: SY7PR01MB10921.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 05 Mar 2026 01:57:52.4152
 (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: ME5PR01MB10058
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/SY7PR01MB1092144FE2B738E660860DED4B67DA%40SY7PR01MB10921.ausprd01.prod.outlook.com>
Precedence: bulk


Hi, Richard

On Wed, 04 Mar 2026 at 18:52, Richard Guo <guofenglinux@gmail.com> wrote:
> On Sat, Feb 14, 2026 at 4:37=E2=80=AFPM Richard Guo <guofenglinux@gmail.c=
om> wrote:
>> On Thu, Feb 5, 2026 at 3:51=E2=80=AFPM Richard Guo <guofenglinux@gmail.c=
om> wrote:
>> > Attached is the updated patch, which adds the check requiring the
>> > operator to be a member of a btree or hash opfamily.
>
>> Attached is another updated patch rebased on current master, with the
>> addition of support for RowCompareExpr to handle multi-column ordering
>> operations; otherwise unchanged.
>
> Attached is another updated patch rebased on current master, with some
> minor cosmetic adjustments; nothing essential has changed.
>

Thank you for working on this!

Just a quick note: I think `foreach_ptr` is more appropriate here than `for=
each`.

diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer=
/plan/subselect.c
index 299b3354f6d..0d31861da7f 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1484,7 +1484,6 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, S=
ubLink *sublink)
 {
 	Node	   *testexpr =3D sublink->testexpr;
 	List	   *outer_exprs =3D NIL;
-	ListCell   *lc;
=20
 	/* Punt if sublink is not in the expected format */
 	if (sublink->subLinkType !=3D ANY_SUBLINK || testexpr =3D=3D NULL)
@@ -1514,10 +1513,8 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, =
SubLink *sublink)
 		/* multi-column equality or inequality checks */
 		BoolExpr   *bexpr =3D (BoolExpr *) testexpr;
=20
-		foreach(lc, bexpr->args)
+		foreach_ptr(OpExpr, opexpr, bexpr->args)
 		{
-			OpExpr	   *opexpr =3D (OpExpr *) lfirst(lc);
-
 			if (!IsA(opexpr, OpExpr))
 				return false;
=20
@@ -1537,10 +1534,8 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, =
SubLink *sublink)
 		/* multi-column ordering checks */
 		RowCompareExpr *rcexpr =3D (RowCompareExpr *) testexpr;
=20
-		foreach(lc, rcexpr->opnos)
+		foreach_oid(opno, rcexpr->opnos)
 		{
-			Oid			opno =3D lfirst_oid(lc);
-
 			/* verify operator safety; see comment above */
 			if (!op_is_safe_index_member(opno))
 				return false;
@@ -1566,10 +1561,8 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, =
SubLink *sublink)
 		flatten_join_alias_vars(root, root->parse, (Node *) outer_exprs);
=20
 	/* Check that every outer expression is non-nullable */
-	foreach(lc, outer_exprs)
+	foreach_ptr(Expr, expr, outer_exprs)
 	{
-		Expr	   *expr =3D (Expr *) lfirst(lc);
-
 		/*
 		 * We have already collected relation-level not-null constraints for
 		 * the outer query, so we can consult the global hash table for
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/u=
til/clauses.c
index c47c9da4a9b..3f3baf2149a 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2052,7 +2052,6 @@ query_outputs_are_not_nullable(Query *query)
 	List	   *safe_quals =3D NIL;
 	List	   *nonnullable_vars =3D NIL;
 	bool		computed_nonnullable_vars =3D false;
-	ListCell   *tl;
=20
 	/*
 	 * If the query contains set operations, punt.  The set ops themselves
@@ -2083,9 +2082,8 @@ query_outputs_are_not_nullable(Query *query)
 	/*
 	 * Examine each targetlist entry to prove that it can't produce NULL.
 	 */
-	foreach(tl, query->targetList)
+	foreach_ptr(TargetEntry, tle, query->targetList)
 	{
-		TargetEntry *tle =3D (TargetEntry *) lfirst(tl);
 		Expr	   *expr =3D tle->expr;
=20
 		/* Resjunk columns can be ignored: they don't produce output values */
@@ -2194,11 +2192,10 @@ find_subquery_safe_quals(Node *jtnode, List **safe_=
quals)
 	else if (IsA(jtnode, FromExpr))
 	{
 		FromExpr   *f =3D (FromExpr *) jtnode;
-		ListCell   *lc;
=20
 		/* All elements of the FROM list are allowable */
-		foreach(lc, f->fromlist)
-			find_subquery_safe_quals((Node *) lfirst(lc), safe_quals);
+		foreach_ptr(Node, node, f->fromlist)
+			find_subquery_safe_quals(node, safe_quals);
 		/* ... and its WHERE quals are too */
 		if (f->quals)
 			*safe_quals =3D lappend(*safe_quals, f->quals);


> - Richard
>
> [2. text/x-diff; v4-0001-Convert-NOT-IN-sublinks-to-anti-joins-when-safe.=
patch]...

--=20
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.