Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wD1vw-002XJ0-2Q for pgsql-hackers@arkaria.postgresql.org; Wed, 15 Apr 2026 15:13:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wD1vv-000K8c-3B for pgsql-hackers@arkaria.postgresql.org; Wed, 15 Apr 2026 15:13:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wD1vv-000K8C-1e for pgsql-hackers@lists.postgresql.org; Wed, 15 Apr 2026 15:13:39 +0000 Received: from mail-francecentralazon11023083.outbound.protection.outlook.com ([40.107.162.83] helo=PA4PR04CU001.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wD1vt-00000001Eyg-10PE for pgsql-hackers@lists.postgresql.org; Wed, 15 Apr 2026 15:13:39 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=ktLQ2w9AYsvmJli9JdAgTT0iDQZflSEZigGyRCll2uj47e05tK36HFpnmTILZgioz+F2Ud2xO8+Y441OnWTRJwqKr0yhTQOe7iyvMLgr4KIRWHmPXVdGtGwBOpnHTRHTovKQmCCJwpnCNo4I4j9+q6ISVE5la/untEYuco274SMXBB7HrO2F4AAjujvc/bU1vq/ngdEM8lxozzj3iJHFey3Tr5vQqKPta2TBWOdIcg9H0XEXHxDyjxgCCwGpEveHyvPqkBb5nIhUSF9Jdmufv8GvjDVfl49vG5q2OKVdGK0z34LflDaB6VuR5mhPU7VOkxjsUVnK0cvQU/k1qGdcdw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=FWu4lV3Fl7LQ/HDDV0HkvLC2IG2+BbySxpIHKBLieO4=; b=BV/8ToneWvFzLpq5XGVaREppJlCMWV9Eogfp1rgz/pBcoz7MtDH65WaEhIed0Wkv8GCpDQ+Tyb2/S6K0uIXp+VOfIAkk2inc8aJI5ok/5yOkj+wvRS7ZDJiFC5d4I2/exA930yzaGYZpRPvK1jwXHcTebEebVSUlQUrusoKYzO2X7KfqHr/F7AdprDbaPv0oLeR9grQ2oEpdpjijBEmDY8kbyXK1rAOKz285MjwxmtpCTMGtOI2emyhfijl9WZ1bm90ExqZKGrRLn1pw9Qw1aJXu7nFPnNHsgNVghad8xZfuXhQdW+GgaV+mSnOGig+OeFGT5hYSaFHEeoQffSjfpA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=ulb.be; dmarc=pass action=none header.from=ulb.be; dkim=pass header.d=ulb.be; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ulb.be; s=selector2; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=FWu4lV3Fl7LQ/HDDV0HkvLC2IG2+BbySxpIHKBLieO4=; b=fVSqeT2J90lwbDIAzTEN8+UzVpsM6PH6NB/Cqs8svm1wzZblxksXAoH8IkTqhYHOCA3lfP8qJKssz1XsHxCnONz0B65+FyMmK90FkMU1S4cEiXsHw5wOTEFCYUnt/Y798ne84Xh8LINCxZ0kGJNDfUiWhY3ri36hxe0AYCG+wPw= Received: from DB8P190MB0731.EURP190.PROD.OUTLOOK.COM (2603:10a6:10:125::14) by DB9P190MB1227.EURP190.PROD.OUTLOOK.COM (2603:10a6:10:222::16) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9769.48; Wed, 15 Apr 2026 15:13:34 +0000 Received: from DB8P190MB0731.EURP190.PROD.OUTLOOK.COM ([fe80::3f82:98a0:536f:f5af]) by DB8P190MB0731.EURP190.PROD.OUTLOOK.COM ([fe80::3f82:98a0:536f:f5af%5]) with mapi id 15.20.9769.046; Wed, 15 Apr 2026 15:13:34 +0000 From: SCHOEMANS Maxime To: Haibo Yan CC: vignesh C , Tom Lane , Damir Belyalov , jian he , PostgreSQL Hackers , SAKR Mahmoud , Diogo Repas , Andrey Lepikhov Subject: Re: Implement missing join selectivity estimation for range types Thread-Topic: Implement missing join selectivity estimation for range types Thread-Index: AQHZK2HTFElV4cAAG0qhvSWvkCiFBa6keKmAgAAVOQCAAtFcgIACBB+AgAYni4CAVKFYAICOo58AgABu2wCAG78GAIAAfkQAgMyTtICACXamgIBHqSyAgAB2DQCAEmj+gIAHr52AhPImLgCAC/F48YAAt/OAgADvhWA= Date: Wed, 15 Apr 2026 15:13:34 +0000 Message-ID: References: <8afecd87-d1e5-241c-5e3e-75e1c62c279b@enterprisedb.com> <1bc5e1e4-380b-213d-6e6a-d5e8f7f0c7f5@enterprisedb.com> <84ffb566-8038-ab35-c841-7a5e5728a247@enterprisedb.com> <525ea957-88ff-e2c6-2975-474c849aa234@ulb.be> <48a78946-f380-a279-e75f-711846e509f8@ulb.be> <4c443cf6-96af-0953-fd5b-bd2b5c0c6f00@ulb.be> <2516071.1699991181@sss.pgh.pa.us> <08b2dea8-6dca-06e4-a53d-3e6334e7645b@ulb.be> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=ulb.be; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DB8P190MB0731:EE_|DB9P190MB1227:EE_ x-ms-office365-filtering-correlation-id: 9a76aef9-bcfa-4f3d-0240-08de9b018fe3 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|376014|786006|366016|8096899003|38070700021|22082099003|18002099003|56012099003; x-microsoft-antispam-message-info: +xUxo2KAIVcN/B+w3yD3/l2F4XbnHGZXtJ9dAVAbhturovdCW9028tx4Y/rY5dVGE0YYJbFB/yw1hm22wcwzCcPM7Fb0Ro4bxgyIto41pkg23hj5dnRUYHCGvf8m+3Ua21Zw/3mw7eHRUrFANVpinoMZxGRv8JrOlrDXniUL0IdHPlifcm83QGZEFlmepTE5u6MKv7zhBE0u89ikJ4zzBMu1+hznqp1MNiEnAtgTHXZnSSWeGx2Uwwki6YooE4I9NiyZSkD9OE8ZNT2bJydMNSxCmfKb4Kzzjco5KVryleg7+IcarJh5tbRpfrQ0+YxV4MG+9zN2i16Jz5ZxWoFRPQhhJ+vPoSQyvFVovqZsHpbuPSFJEbzIuAjtyyxrXCrwO0y2q7bHxcaWJ09Nx3GtRWxXNogGPj41Y8YuN9jCWwEGeJ9mPspMZ99AW98LQ6Bnvr2K9/50jadSOR7+VYeZzLDtWR/c7PCbgIaZvXc5Y9sWzvVCD2pQnheRRHCIlmYkm/Z9Yi74Vm9ahdJ/cRnMV2iDsyc8O+9glGtlJkjgmLhxqb7CDqa3/ecU5xNoJHfqK2nYvqtTfvWKN9rJHqs4LI26RBAj0dQixCuGKxkf27+o6UcDuj3vy267dk6WXexOVbmfQ+G+TYC/1kMxzDv9wJCJH1pwppMjECDv5uU6+YWO6TfD85WkR2ToYCaoDj4U13N4trR6wnJSoXzsG6+0AH9P0zvVkYwFuw1m1g08+Nrwcyp7hHWQCUDhnRUnTDMvwSndk08prcn73ymKJBl3reK4wl0xl3Tv9QkplVGWCYc= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DB8P190MB0731.EURP190.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(376014)(786006)(366016)(8096899003)(38070700021)(22082099003)(18002099003)(56012099003);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?5hF+vfFJ//fo8hUbEa3cKhvlAkna6kl3bhiJxkUFXC0MfCWLcln3fU9aBz?= =?iso-8859-1?Q?uDy7wy556e/n0grnqJNsT7auOjt3jmve6mHkrmTAeZH02DZK58Hir7RmTW?= =?iso-8859-1?Q?Y/0pxPdHeLvWERoNK+Ix821jKvvKooA043MbmOsGajefAI9c6ktrKO2/cW?= =?iso-8859-1?Q?ScZN2Q71pUXU9Vgg6gZ6Lzfowq6Gdq8T9rdkDnTOVkLRJBYng0nQA8JBtx?= =?iso-8859-1?Q?40AyXtHbZgpjlfan9ydtmam1T7dKr9pFD0g7LBRaCdbgokaw9KYrExPLzq?= =?iso-8859-1?Q?VXLqlpmcImNy8Vb/GmPIxLpWlQtKpSjHayP8DGNS++GAv+p84iEH0PW/xE?= =?iso-8859-1?Q?FGQfOvDtFGVs0HIajS1s7chBwukdYrrLMee+ofHUApONBmCfF9/xPhEzX+?= =?iso-8859-1?Q?CyNFVNPVLIezlKtKBJNGaYMipNdaUD3IrsLV0hAbvOb3y7ZoBiKeRpQO9D?= =?iso-8859-1?Q?rFuTrSkBC9ApUILwjaYB9W+6iGopFu2HmNN0TnpZrHjptXqhT6CH71vPQe?= =?iso-8859-1?Q?CE60SQyYi3xGCne5HqyJx/zkd3Vd3AYgcQZXOmie8t9C9ehBagSCWzHu8a?= =?iso-8859-1?Q?POm9/FEBtuxfkmX1On+29CtNhyGm7XROMnvW5jcSi11KyQpmQOvyEtjFa2?= =?iso-8859-1?Q?SZZ2jLzd7PZWeOQ+YKU1Lph/TTYxKSolI25GDeBnrG4uozBgsZ3nFClf3/?= =?iso-8859-1?Q?alxYySZml859mBd6fHwrd/BGwzwr8KY77mIzqA6CPvkzwuP6reWKsYgSC0?= =?iso-8859-1?Q?V/lYv2MMoUJGAhlD101LLeb8XaBnuB4wEhYIkUsGeVov4v9Iq8Hr83D/vW?= =?iso-8859-1?Q?ctZ/U3Zq6nGygFYTkRCeJ2Tg4VpVq87Z9YoLo73FHB2sEBEXM9MgH46PYs?= =?iso-8859-1?Q?NTc5/UtVmRSQYt7UODtOM787MGAoocWPYYcHJ6Ez6FwRH7cwU3daB8TZ0e?= =?iso-8859-1?Q?beczay6YhzA6059k2dWiUHIL8lrYiqbOVaDFsDV/9ATG2jFpvU3MxRFjKp?= =?iso-8859-1?Q?r5X1ewS4Vzg5f15SIT1F4E5gGCxOom5ciwH7IQEXo9PWKSMvt6/E9QB7Ia?= =?iso-8859-1?Q?ISGC7ZIZrTnsufXlYE00ahhnKzXvzagV4XzYwBsTWJviKUhz6zVd1tfdTc?= =?iso-8859-1?Q?C01/VMNbJfbCWpIRbxkEauKYbx5aWc0ysMEO44Zb3N2ZHaqKnCCE3U4rS8?= =?iso-8859-1?Q?e2KOAg2zbw8qlUu+jupKfMib+3AUVAxYMRZAtaqwnznkP12VAkHMQs7Pp/?= =?iso-8859-1?Q?3E1GtX9nM8cS15FocN3fGDIva76voaGbAiDr1If0rGKZcBfd608HhqS3Dp?= =?iso-8859-1?Q?/SeuCjmPrN8UTDsdcSYtLl9aEmwi+JpCWk25Y3KSt5WIHSC075BwfA/Uwy?= =?iso-8859-1?Q?Yj5/LI9AVOYkH8CLXzbzl+nqAQ8mQ+2VckGJiJsPNyeM5q1tsa9YY4FwEl?= =?iso-8859-1?Q?wv4wIJoXxyd/2Ki2qCz7eVTSD2PjazVSziAGOiMLe0Oy69oRdqh1Sktqx1?= =?iso-8859-1?Q?svGY8tNxZ3rAwzgjzBF37fo673mynOHl7pz+PhiFoguekCSShZIGk0T29M?= =?iso-8859-1?Q?P3AOSBTc/OgveR4zacXQinnRFLgsNAxul++q3lAizsMtCQ9OYDGzxk4+b5?= =?iso-8859-1?Q?Lv3Xn4mk0u4v6yRqci/fV7hMb4HjLBYDljQ1bhrDnGcBJdugSYIlL6f3BA?= =?iso-8859-1?Q?b1DiZ7qS4Po3re9KbdHsbGvcKqB+y/em+kSlQsZbEoZEjG3Wy6jg9y7vp6?= =?iso-8859-1?Q?BJH5vhnuWZtstd1hBlC1mR/Kfb+3QpU3mpR4QMmT/1a3r7SzB6dSbms03b?= =?iso-8859-1?Q?TVHb5mc8Mw=3D=3D?= Content-Type: multipart/alternative; boundary="_000_DB8P190MB0731EA1B8381F48AA15B9437F0222DB8P190MB0731EURP_" MIME-Version: 1.0 X-OriginatorOrg: ulb.be X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DB8P190MB0731.EURP190.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 9a76aef9-bcfa-4f3d-0240-08de9b018fe3 X-MS-Exchange-CrossTenant-originalarrivaltime: 15 Apr 2026 15:13:34.4257 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 30a5145e-75bd-4212-bb02-8ff9c0ea4ae9 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: NjFyTM+KkK1SgdIJTtk5s9vZrdTqHCC7sDflcyxu79MXekOUvH45ZPpSex+hV1M8O90hEnCYWRqHgXQ50iSzXg== X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB9P190MB1227 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DB8P190MB0731EA1B8381F48AA15B9437F0222DB8P190MB0731EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Haibo, Thank you for the review. > One small note: the localized bool join_is_reversed; in my version was > intentional. I left it that way because get_join_variables() wants a > storage location, and I preferred to keep that use local and explicit > rather than trying to reshape things around it. Fair point. I moved it out of the bare block because it looked unusual, but I can change it back if you prefer. > For patch 2, I am less convinced, especially for &&. > [...] > for multiranges there is a third possibility: neither side is entirely > left nor entirely right, and yet they still do not overlap because of > an internal gap. This is a valid concern, but it is an existing limitation of multirange statistics, not something we are introducing. The existing restriction selectivity code in multirangetypes_selfuncs.c already uses the same NOT(<<) AND NOT(>>) decomposition for && on multiranges. And multirange_typanalyze explicitly says: /* Treat multiranges like a big range without gaps. */ The statistics only store the outermost bounds, so the gap information is already lost before our estimator sees it. The multirange GiST opclass does the same (stores the bounding range). Our join estimator is just consistent with how multiranges are handled elsewhere. The alternative is falling back to the 0.005 default, which will almost certainly be worse. Would a comment explaining the limitation be enough? > For patch 3, I agree with the motivation [...] But I am not convinced > that exporting those helpers via selfuncs.h is the right boundary. > My preference would be something tighter: [...] a backend-private > internal header just for the range-family selfuncs code Good point about the visibility. I'll move the declarations to a separate backend-private header in the next version. Regards, Maxime --_000_DB8P190MB0731EA1B8381F48AA15B9437F0222DB8P190MB0731EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Haibo,

Thank you for the review.

> One small note: the localized bool join_is_reversed; in my version was=
> intentional. I left it that way because get_join_variables() wants a
> storage location, and I preferred to keep that use local and explicit<= /div>
> rather than trying to reshape things around it.

Fair point. I moved it out of the bare block because it looked unusual,
but I can change it back if you prefer.

> For patch 2, I am less convinced, especially for &&.
> [...]
> for multiranges there is a third possibility: neither side is entirely=
> left nor entirely right, and yet they still do not overlap because of<= /div>
> an internal gap.

This is a valid concern, but it is an existing limitation of multirange
statistics, not something we are introducing. The existing restriction
selectivity code in multirangetypes_selfuncs.c already uses the same
NOT(<<) AND NOT(>>) decomposition for && on multiranges= . And
multirange_typanalyze explicitly says:

    /* Treat multiranges like a big range without gaps. */

The statistics only store the outermost bounds, so the gap information
is already lost before our estimator sees it. The multirange GiST
opclass does the same (stores the bounding range). Our join estimator
is just consistent with how multiranges are handled elsewhere.

The alternative is falling back to the 0.005 default, which will almost
certainly be worse. Would a comment explaining the limitation be enough?

> For patch 3, I agree with the motivation [...] But I am not convinced<= /div>
> that exporting those helpers via selfuncs.h is the right boundary.
> My preference would be something tighter: [...] a backend-private
> internal header just for the range-family selfuncs code

Good point about the visibility. I'll move the declarations to a
separate backend-private header in the next version.

Regards,
Maxime
--_000_DB8P190MB0731EA1B8381F48AA15B9437F0222DB8P190MB0731EURP_--