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 1wDwtR-003WCN-36 for pgsql-hackers@arkaria.postgresql.org; Sat, 18 Apr 2026 04:02:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wDwtP-00C0l3-3A for pgsql-hackers@arkaria.postgresql.org; Sat, 18 Apr 2026 04:02:51 +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 1wDwtP-00C0kv-27 for pgsql-hackers@lists.postgresql.org; Sat, 18 Apr 2026 04:02:51 +0000 Received: from mail-yx1-xb12e.google.com ([2607:f8b0:4864:20::b12e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wDwtN-00000001l7n-0FWK for pgsql-hackers@lists.postgresql.org; Sat, 18 Apr 2026 04:02:51 +0000 Received: by mail-yx1-xb12e.google.com with SMTP id 956f58d0204a3-64937edbc9eso1252321d50.2 for ; Fri, 17 Apr 2026 21:02:48 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776484967; cv=none; d=google.com; s=arc-20240605; b=FEyEXW1I3dA4DR4BedD2VAHKURpp4XHFbCQppk5K5gtlpTokEWOI6AsmoVG7Y4ioKl RST5WDUMnsw6zUisdeRDPoQSy5rLHBjAd8xK9MfF1x0WRslQ+PNMkns0eYGfErcbgcUf o2Ef/ihT9e3mW2yMwRvUKp7+TwAkh0jhvP+tQDA75b7K5cSFs1rmZmmIE5uWiz0RVD9S ptG4eWhIa8JLgPjte6bZIKuJpgz2C1aY/Anls/owS1oh24kEoRk0zUmVNGvVTWveAwJ1 V2W00oNky8AQweB8n4qjgkMesejcYltZTMZj+gVD7kil/C3ZXOQclKHeo0bP0S1Cdjls UJsQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=BdbvttZd+cYXA9BdMZk0FYUnCwJZ+YyX08uVJmkVLjQ=; fh=4iOMPPqaoHbqal2kHqawjWfPce1Onj//wsK2CJPOPao=; b=JtpWwnOCrrJ7SS+FI7W5HY6A6F1pRBXrnt3Lku9Ns33nION3RaLeSfp7/qyRMcyxN6 JeBluE4m83p35r3mVGC5ZA38zrhzo/IK6ydRfu1gFLOZKLqD5TQ0AqGVw9l/2jcsATdK faX52C8Y3VDfjOF/JzXRIGcj/V+GetyXCDXaKxunVRmfri1BZg09IY7xKMj4dYKpYsLx u0XPF46rYxoOA4LPEUBPR4tnzJkgMdG34QZyst2dtV8VCVs8TdnZayOoMp7wmvr6us8H 8h+Q0slbXW529WLg/2tFdUgmOTV4l49idvWY9l8j2ga0Mx5eoNEloSAq+jX5lA2Z75ZB 6HlQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1776484967; x=1777089767; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=BdbvttZd+cYXA9BdMZk0FYUnCwJZ+YyX08uVJmkVLjQ=; b=HdFHLfQ0j/6+OrqEJ5m0QvuCkRIk8z6cjS8Wu7nKvmpAIRnBE9+7DQPAKXOxzTrx6U nYkU9DP9TWx/m9R2dAM44hkwph5HOh3CACboLq9M3PAW5ZNr8F8KysLlXby7noNQGHlF 2AzCuzhfX3DLZZMEb6NOPZMRcDNOQE7oeS9SsWU0eJt+uCm9Yce7maa9JNOD6kQFWyqZ cydx64I+GaxNsqB/+F+Ty/v7ddwp9kBXSB5otsks+MdwT8zEUN+g2ejFNKi7f0ftwg+9 nYU7tPpRVzHqUrGqqhduNredkCz3KsCBsLhTrJIWt6hD2KKYoBkcrMVazp44cMCVBJlZ TNOg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776484967; x=1777089767; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=BdbvttZd+cYXA9BdMZk0FYUnCwJZ+YyX08uVJmkVLjQ=; b=OcX54/adXHMYg7+xjTKkF94Whz1TNRWbHV2tdfQ3Crb02AafyVMXXf8zKzTK4iBh6a TXI3dRLpPjuA8R8ZtPp1b35TwuF+Etic0l3J7zR83M1GY1rx2jKHb8AgoRxWE4+8XhSm JQl9M18l9dTMyfekBJZsVT3ArBHTG3HKdlvg/h6fNZ2YScG7wuZ5AB9CWiz/4leO9bgN sYPVLxX9YHHrY/pb1y63odw/AqvX1KRddhPeg0K/mi4Ex3O2EF8hs+W5B3OcYMMaafwL puh/+scSsC4r2OV2FvpMRK+lzi1Buwe4YaI29GdXptg4w0La/z/FnEFTR7FnP+NjJAIX PKvA== X-Forwarded-Encrypted: i=1; AFNElJ8rV7hvGpfRHQ93aOuNfC5aQHCBeRqUFGVd7koNTl/QxA5hphVEG/C8R4Bd55hFmglUkTXbX2+3Jlu2CSJN@lists.postgresql.org X-Gm-Message-State: AOJu0YzBGAHExBFHGxAjdSzRVMYbJV83AM0oN3wVOT17u3wMA6L26MnZ HYgMmkHzneGlq8ULs+YwWrAo5VMmu0I89RdMNuECaUrx2M/PGkduBWNwqO3bS9+Uj7rHitae7bD pvWBMWwDMAr7XDK1onEswmHRvNZqmXag= X-Gm-Gg: AeBDieuxsM5cazN7l+SBZuG5U+mWrw/GU2j4bZRUf1g0DR5I6tSYKOVp2Zfzh0yY743 92KQnJraCIUBD/VVFkOxq5Zszyo9h8WzjFiWyRDXGlTiYWIvuBbdobCypcLbUNcB68kYx/6MIxI eZbt1bmFwqoEktg8TvHA4u5yYVVl8cDFSgvH6fGxZICb9DowzctzUr1rXn1XoJ1iXoy2QKb571x G1agdLlBwUsF+9rKGac1S9PgllI6WTSLVZprTT7R5aVaORt3cGbdprUFDnGxvBQbDjK4UFhsKOL ss2twynGBLxwrdtIPIKzhNTbfclSOCMS4442lQWbj8TyiWgpslM1Gh4nQZk7 X-Received: by 2002:a05:690e:159b:20b0:651:97ba:8507 with SMTP id 956f58d0204a3-65310b44ba8mr4349748d50.59.1776484966734; Fri, 17 Apr 2026 21:02:46 -0700 (PDT) MIME-Version: 1.0 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: From: Haibo Yan Date: Fri, 17 Apr 2026 21:02:36 -0700 X-Gm-Features: AQROBzAxHtJTMwAfuPcx3UinbDw415TPnyedBrFNpFPGZ4obEii9G-V97xaprK0 Message-ID: Subject: Re: Implement missing join selectivity estimation for range types To: SCHOEMANS Maxime Cc: vignesh C , Tom Lane , Damir Belyalov , jian he , PostgreSQL Hackers , SAKR Mahmoud , Diogo Repas , Andrey Lepikhov Content-Type: multipart/alternative; boundary="00000000000064211d064fb423ce" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000064211d064fb423ce Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Apr 16, 2026 at 8:13=E2=80=AFAM SCHOEMANS Maxime wrote: > Hi Haibo, > > Attached is v7 with the changes we discussed. > > Patch 2 now has an inline comment on the && case explaining the > outer-bounds approximation and its consistency with existing restriction > selectivity. The commit message mentions it as well. > > Patch 3 uses a separate backend-private header (rangetypes_selfuncs.h) > instead of selfuncs.h. > > Regards, > Maxime > Hi Maxime, Thanks for the updated series. Overall I do not have major objections to the direction here. A few small nits on patch 2: 1. In the commit message, I wonder if =E2=80=9Cthe core algorithm is ide= ntical=E2=80=9D is a bit stronger than necessary. Since the main point is that we are reusing the same approximation based on outer bounds, something like =E2= =80=9Cthe same outer-bounds-based estimator can be reused=E2=80=9D might be a bit = more precise. 2. In a few comments, the wording still says just =E2=80=9Crange=E2=80= =9D, but in patch 2 we are really dealing with range/multirange combinations. I think it would be a bit clearer to make that explicit where appropriate, and rese= rve =E2=80=9Crange=E2=80=9D for the underlying range-type/bound-comparison l= evel. 3. I think it would be good to add the reverse mixed-direction test as well, since patch 2 covers multirange =C3=97 range in addition to range = =C3=97 multirange. Something like: -------------------------------------------------------- explain (costs off) select count(*) from test_mr_join_mr a, test_mr_join_r b where a.mr << b.r; explain (costs off) select count(*) from test_mr_join_mr a, test_mr_join_r b where a.mr >> b.r; explain (costs off) select count(*) from test_mr_join_mr a, test_mr_join_r b where a.mr && b.r; -------------------------------------------------------- I think that would make the mixed-case coverage feel more complete. Regards, Haibo --00000000000064211d064fb423ce Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Apr 16, 2026 at 8:13=E2=80=AFAM S= CHOEMANS Maxime <maxime.schoe= mans@ulb.be> wrote:
Hi Haibo,

Attached is v7 with the changes we discussed.

Patch 2 now has an inline comment on the && case explaining the
outer-bounds approximation and its consistency with existing restriction
selectivity. The commit message mentions it as well.

Patch 3 uses a separate backend-private header (rangetypes_selfuncs.h)
instead of selfuncs.h.

Regards,
Maxime

H= i Maxime,

Thanks for the updated series. Overall I do not have = major objections to the direction here.

A few small nits on patch 2:

  1. In the commit message, I wonder if =E2=80=9Cthe core algorithm is identical= =E2=80=9D is a bit stronger than necessary. Since the main point is that we= are reusing the same approximation based on outer bounds, something like = =E2=80=9Cthe same outer-bounds-based estimator can be reused=E2=80=9D might= be a bit more precise.
  2. In a few comments, the wording still says j= ust =E2=80=9Crange=E2=80=9D, but in patch 2 we are really dealing with rang= e/multirange combinations. I think it would be a bit clearer to make that e= xplicit where appropriate, and reserve =E2=80=9Crange=E2=80=9D for the unde= rlying range-type/bound-comparison level.
  3. I think it would be good = to add the reverse mixed-direction test as well, since patch 2 covers multi= range =C3=97 range in addition to range =C3=97 multirange. Something like:<= /li>
--------------------------------------------------------
explain (costs off)
select count(*)
from test_mr_join_mr a, tes= t_mr_join_r b
where a.mr << b.r;
explain (costs off)
select count(*)
from test_mr_join_mr a, test_mr= _join_r b
where a.mr >> b.r;

ex= plain (costs off)
select count(*)
from test_mr_join_mr a, test_mr_joi= n_r b
where a.mr && b.r;
-= -------------------------------------------------------

I think that would make the mixed-case coverage feel more com= plete.

Regards,
Haibo

--00000000000064211d064fb423ce--