From: Chengpeng Yan <chengpeng_yan@outlook.com>
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
CC: jian he <jian.universality@gmail.com>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>,
	Tomas Vondra <tomas@vondra.me>, Andrei Lepikhov <lepihov@gmail.com>, Corey
 Huinker <corey.huinker@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	"hs@cybertec.at" <hs@cybertec.at>, Jeff Davis <pgsql@j-davis.com>
Subject: Re: Is there value in having optimizer stats for joins/foreignkeys?
Thread-Topic: Is there value in having optimizer stats for joins/foreignkeys?
Thread-Index: 
 AQHcYv6ZC10TlFUn8kO0zZchsQZHS7UNRXkAgAATi4CAAFWhAIACmcGAgFirD4CAA41FgIAB6/4AgAEgywCAAGXAAICGgW0AgAB4AACAAF5OAIAKlBSAgAsxnYCAACXWAIAB8gYAgABjVACAAK2rgIAPgNKA
Date: Mon, 25 May 2026 12:15:47 +0000
Message-ID: <33CC910C-8F46-444E-BE61-C9A3D157A96D@outlook.com>
References: 
 <CADkLM=cUwMftPLFq0iD6-qKRyNiRM2HZGYVp6=0noxA8GfuEtA@mail.gmail.com>
 <ea04ba03-50dd-4de8-a2bd-111f246a2f30@vondra.me>
 <246035.1764627115@sss.pgh.pa.us>
 <CADkLM=fEi_GeeS3zyg6B5WgswyPe0wNXHfKQOxjy8A5fXHD7=A@mail.gmail.com>
 <CAK98qZ2mW=geT9NKe5vC68-sB9EJe_887uV=MCFt6y9AhyTp7A@mail.gmail.com>
 <CAK98qZ0LwJbUoiZjjFXitojHy4UskkjYDiSd_JZfGE9LbfZm9w@mail.gmail.com>
 <8df3d212-5d60-4e30-9606-d8849f7d37ae@gmail.com>
 <e9165403-7b0e-4c01-96d0-b73512ce359d@vondra.me>
 <3c477f2f-10e4-4705-bb21-90ccbe67e9d2@gmail.com>
 <d079f515-db2c-43f3-a11f-ceff7b1616c9@vondra.me>
 <CAK98qZ2ySno00SApwj3X_MgN4iuBzKaXKXOY+U3jaFqTxPS8Tg@mail.gmail.com>
 <CAK98qZ0Vr4D3usesj_qpNFAH1=7NKX=bR9bNRNM7tdnk5KWHDw@mail.gmail.com>
 <CAK98qZ2TveWY34VBw8LmQUEq2GUrPLhsaYYORgDAA5VZybyS5w@mail.gmail.com>
 <CAK98qZ3TGXKcudcnyEhGuMUiru36m=Vm=Zm5Gyug0h+VyxmD0w@mail.gmail.com>
 <CACJufxFOe4rx=J+0+=_g+K=bnhxs1OUdMJCgQ+tV0KdkQ_2aeQ@mail.gmail.com>
 <CAK98qZ2mMJ3Nvx9U1S9N9Put1bb=iOgy8vFdp=rfjfta4wJ2AQ@mail.gmail.com>
 <CAK98qZ3_4nPQ4DYXG_2QAD-A-BfmWs2psJ9N_HQ9rSDZb0HF3A@mail.gmail.com>
 <CAK98qZ3ASXEBuftvd=SxPwLOXgo=2-88SyZyLxXf3k4HfDKKsw@mail.gmail.com>
 <CAK98qZ397f5xEkNgqEoeZTbXBnr7h665wZMvGJOnok+ch+XWSA@mail.gmail.com>
In-Reply-To: 
 <CAK98qZ397f5xEkNgqEoeZTbXBnr7h665wZMvGJOnok+ch+XWSA@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_|OS3PR01MB9621:EE_
x-ms-office365-filtering-correlation-id: 1ba0c247-f3ea-4f90-f2ff-08deba575a7c
x-microsoft-antispam: 
 BCL:0;ARA:14566002|22091999003|24121999003|8060799015|24021099003|8022599003|8062599012|19110799012|15080799012|31061999003|3412199025|440099028|102099032;
x-microsoft-antispam-message-info: 
	=?us-ascii?Q?vfS727jx4nNT+1cexJfKGbm/WAQKLxjDjjUzrrlWPs3wBvRe9kmhO03xKofu?=
 =?us-ascii?Q?mQwlnbnZZuCzrBszcUU7avEhs5+oTKvnEtW31EEhnohd0IRjcAfeGhC9nz6U?=
 =?us-ascii?Q?9M/bTRYNyZMw41bYXSVJz2mI8/0G7Vje3Sk06OxJ+40aNYggDisPIb01jc6j?=
 =?us-ascii?Q?nbO9rs0lA9AqgzXZ2B8HcZZM0YfcOrJ1lHR5hmu4wFiT5hBfEeJjzaIz7cnb?=
 =?us-ascii?Q?Gl8ceduoX90/dJ3l9f28zu1OsYH/OBzq4NkSuFhB9g6XvLvmaqV4j1Y1qQMy?=
 =?us-ascii?Q?XhLtcnnYOSYlTKvvUyYgrtseafJqkgd0MYdmhUCX1GAauX5EF+qFW3ck2TAo?=
 =?us-ascii?Q?wMxYHZWhmKg33G28naZTQCuNJFsngNXBWJbtPVt9EKhQ+FDzlejFXacxgQWd?=
 =?us-ascii?Q?iKHhDchMxb+E5JYOTEn+/UGQiSyk4ui6SVTCnjvvbbENybZdIXPxTt+seYMt?=
 =?us-ascii?Q?jSuRq0C72k6HPdXjF4rvbVMi+fqmQWWBnh/qQd5lNSLUZkh7zIWBqHZkCD2c?=
 =?us-ascii?Q?YkoS9CwbObDJ+0hqayRymNExXNXcYjTxWgOWCba/qwVJ491lKych430IDL09?=
 =?us-ascii?Q?z9hGxxtqUIzFPgauQse71N2CtusAs1kz4Jejgojfj3oAhu73tp+oyW+heyV9?=
 =?us-ascii?Q?ZBaTnHGkJmcffvuboNf+M/M8SiKdiRJUfU9u0LF0oy4oG86GOqYFDxbWXCJz?=
 =?us-ascii?Q?9+fFtw5vq8twJsso1bxXPDlMPLm/6HHGcckegimKunwGpGGD0FPlKJ7H2SAV?=
 =?us-ascii?Q?UmcqxVDqSETp5dGi9vFKwnlsjIGiF+5BYif+dpIusJUitpP9o0pGubIniR8Q?=
 =?us-ascii?Q?5w6oAJ4IjdKfIVZUyTqhgagi59OmQ2ae6ubMORJIg5jZxSZLrAm1M+Jy6xtn?=
 =?us-ascii?Q?OkdmKZKnzyzAqvTsWqw7pfWMca46GZXO+mK+EyZI1YtC2W99m02jZDXa4spW?=
 =?us-ascii?Q?7Br/0hTAo/hTx8V1EN1T4Q=3D=3D?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?insSijgg3dP4ztLAgNZcTzLfFhraeE7av5MyTohrVZl/moIgx3p2poKwGRP4?=
 =?us-ascii?Q?cBstUOTjr9dg00nRkCVBQNRVyHI9vk3yUhkE4bT2xWktEwlJA+TmT1wX6oe1?=
 =?us-ascii?Q?2HQPTGuvBXJ+FUudrN2C1H70QWRxHOcmhrsbeqGSgnc8I+9JQK7wt48elJTZ?=
 =?us-ascii?Q?ZoTu5PBGJ4Bf0T7C1NNEOQtXemppT4durtwxYmRSH+P8Lia+f7pxGjKi1lQn?=
 =?us-ascii?Q?nFYXr5IDI4IhM8E0kJl6Ymq30NixFZt3pNPpPSdl7M6mwlvCHiDNU5d5s7nV?=
 =?us-ascii?Q?XMWSug10Zb/Ei9/aPVqdTRIPSsitVuZ5r9lGQmu784qPu/nG4O1CuGtpC929?=
 =?us-ascii?Q?mSXNIaA2jUKygy+bW1Q3Ajg/PR8f/SUGRm43JGaDKOeHguLwq+fYn6+HPTa5?=
 =?us-ascii?Q?2psLGW6AdpXaMtJO0ITWmfAkhdJdLlE99998iEv5M7t3Q4bS0CcjC1mWOmvd?=
 =?us-ascii?Q?5gdKcFrLUB3N3EhM4OguaMb8otFAa6tBXCtEcTK0rzeGvtQRBl5mwxcRev2Y?=
 =?us-ascii?Q?90Z0JDPcRTuMn/Cy856+IeEa+twv0Sy8ohSpBltdJEcKUX2S1O4MfMnls77i?=
 =?us-ascii?Q?y21xW17KkVI98V+tfMbaRwm7N5WStgm5u4LrZTrvk02G4VufyRRKH6Z/rsga?=
 =?us-ascii?Q?KuV15g9690KqBbQQfjgPU/hbDkJHKq7MEMI6TEOBl/LZd2pq8s5kzVWXbPgy?=
 =?us-ascii?Q?V40lYHaWsxHFJNnqArgwiM7pKVWnJN3GhAovTQepahyp04wfACrj2BssdjbL?=
 =?us-ascii?Q?31pt8iC90OFEWEDZtr9Ob3Zt1POjTgfnwx28SrnSFBuNwGMUnv+ngoZw0hYy?=
 =?us-ascii?Q?WwVauTTDWx5lbqAyAGHT7hA2CEtxcq+gxF30utrUeOmUjw6J0ZgmfiVQ6Cmz?=
 =?us-ascii?Q?durygBgeRaYXZgJHqeBl/k4YyEJSfsrkVxiivv+4K+/MXIo8aa8wxWY/er+x?=
 =?us-ascii?Q?NDgN9aS4EzVKcNtcCx+cVRP/5SlsEc2lQNocrQzv8fh328u+Gh2Yb6C40Ksy?=
 =?us-ascii?Q?GRZBxCp1QSnU7rYb847wVd72H8KSIwaEb2pe8JR2xEf7WaZaVMtvFYrxkXjR?=
 =?us-ascii?Q?lkgAMA+v5aYxquplJk4GKcQWoJfkMr7VrZYtJ64gGMTMyvPyP7LXTnNwZL1p?=
 =?us-ascii?Q?CSRv5+y3XTEbdieYIlU24MN3iJY/RK0kjSXmyCzVAq+P8NuaYL65HgSvExYt?=
 =?us-ascii?Q?t0J9M2qVsmAIhroI1w/3M0wxPIWh1QClFpY0xU9eWLHVW5x9iQMK64yUcUQ2?=
 =?us-ascii?Q?JLPBQowuMSqPBBEN5d5QLfTs1XcRtr91+8zFB5CcH8eWzQG833xxaj5dB9Hl?=
 =?us-ascii?Q?mQ+s0Ls2FJa2H8Uw+Fr5pyD2FO2HUcGXyEzONPHz8VJDOSavYwIhXmXlBe6B?=
 =?us-ascii?Q?eEbayttjQeyNBQP4GaCkcedELjfkGpIdHTD/pRdEUmZaTfvG0w=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-ID: <106239790BE1884D8DAD39A2C63FC6CE@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: 
 1ba0c247-f3ea-4f90-f2ff-08deba575a7c
X-MS-Exchange-CrossTenant-originalarrivaltime: 25 May 2026 12:15:47.6039
 (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: OS3PR01MB9621
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/33CC910C-8F46-444E-BE61-C9A3D157A96D%40outlook.com>
Precedence: bulk

Hi,

> On May 15, 2026, at 23:30, Alexandra Wang <alexandra.wang.oss@gmail.com> =
wrote:
>=20
> Here's v7, another attempt to fix the unstable tests.

Thanks for working on this. I have a few design comments about the lifecycl=
e
and the intended scope of the join statistic.

First, the index dependency contract seems worth clarifying. CREATE STATIST=
ICS
records a normal dependency on the selected index, so DROP INDEX is blocked
unless CASCADE is used, although that index is not part of the statistics
definition. But ANALYZE appears to re-discover a suitable index when refres=
hing
the statistic. Is the index intended to be part of the statistic's persiste=
nt
contract, or only a creation-time proof that index-based sampling is possib=
le?
If the latter, should DROP INDEX still be blocked when another equivalent i=
ndex
exists?

Second, this seems related to the earlier concern that ANALYZE is per-table=
.
The statistic is owned by the anchor relation, but its contents depend on t=
he
probed relation too. In the current patch, ANALYZE on the probed relation c=
an
refresh its own statistics without refreshing the join statistic. If the
probed relation has changed substantially, that leaves a possible staleness
gap where the planner combines fresh base-table statistics with stale
cross-relation skew information.

Third, the contract for non-unique indexes on the probed side seems worth
clarifying. The comments define raw_sel as anchor-relative:
P(join AND covered_filters) / anchor_totalrows, roughly Jf / anchor_totalro=
ws,
where Jf is the number of joined rows satisfying the covered filters. But t=
he
implementation computes raw_sel from MCV frequencies. Since the MCV list is
built from sampled joined rows, a plain MCV frequency is naturally measured
inside that joined sample, roughly Jf / J where J is the sampled join-resul=
t
size. It would be useful to clarify when those two quantities are expected =
to
be equivalent, especially when a non-unique probed-side index allows one
anchor row to contribute multiple joined rows.

These two measures are close in FK-like cases where the joined sample size
tracks the anchor sample size. With a non-unique lookup, one anchor row may
appear many times in the joined sample. For example, if one key matches man=
y
red rows and another key matches only one blue row, red may dominate the
joined sample because of match multiplicity. That frequency describes the
distribution within the joined result, but not how many matching joined row=
s
are produced per anchor row. It would be useful to state whether such
one-to-many joins are outside the current supported scope, or how the
MCV-derived raw_sel accounts for how many joined rows each anchor row
contributed before it is converted into planner join selectivity.

--
Best regards,
Chengpeng Yan