MIME-Version: 1.0
References: 
 <CADkLM=cUwMftPLFq0iD6-qKRyNiRM2HZGYVp6=0noxA8GfuEtA@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>
 <CAK98qZ3ASXEBuftvd=SxPwLOXgo=2-88SyZyLxXf3k4HfDKKsw@mail.gmail.com>
 <CAK98qZ397f5xEkNgqEoeZTbXBnr7h665wZMvGJOnok+ch+XWSA@mail.gmail.com>
 <24247.1779395113@sss.pgh.pa.us>
 <910a4628-720a-4912-af8f-8b5a96a0b336@vondra.me>
 <2468544.1779721437@sss.pgh.pa.us>
In-Reply-To: <2468544.1779721437@sss.pgh.pa.us>
From: Alexandra Wang <alexandra.wang.oss@gmail.com>
Date: Wed, 27 May 2026 10:49:44 -0700
Message-ID: 
 <CAK98qZ0_4Kodyemk0Tdmew=YG8jeHQ=wzOydQws4s99A1X2h5g@mail.gmail.com>
Subject: Re: Is there value in having optimizer stats for joins/foreignkeys?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tomas Vondra <tomas@vondra.me>, jian he <jian.universality@gmail.com>,
	pgsql-hackers@lists.postgresql.org, Andrei Lepikhov <lepihov@gmail.com>,
	Corey Huinker <corey.huinker@gmail.com>, hs@cybertec.at,
 Jeff Davis <pgsql@j-davis.com>
Content-Type: multipart/alternative; boundary="000000000000d6c2090652d03e9f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK98qZ0_4Kodyemk0Tdmew%3DYG8jeHQ%3DwzOydQws4s99A1X2h5g%40mail.gmail.com>
Precedence: bulk

--000000000000d6c2090652d03e9f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Tom and Tomas,

Thank you so much for the feedback!

On Mon, May 25, 2026 at 8:04=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tomas Vondra <tomas@vondra.me> writes:
> > On 5/21/26 22:25, Tom Lane wrote:
> >> I don't love stxkeyrefs[].  I wonder if it's time to throw away
> >> stxkeys[], represent all the target columns as regular expression
> >> trees in stxexprs, and then special-case columns that are simple
> >> Vars where appropriate at execution.
> >> (In the same vein, I dislike the grammar's separation of plain
> >> columns from expressions; I'd like to replace stats_params
> >> with expr_list and sort it all out later.  But perhaps that's
> >> material for a separate patch.)
>
> > FWIW the extended stats copied this from pg_index, which also stores
> > keys and expressions separately. I suppose there was a reason for that,
> > most likely performance - is cheaper to compare attnums than
> > expressions, and plain keys are much more common.
>
> I think I might be to blame for the separate storage of indexprs.
> If so, the motivation was to avoid breakage of older code that only
> knew about indkey[].  (Of course, such code would necessarily fail
> on indexes with expressions, but we wanted to avoid breakage for the
> common case of no-expressions.)  I don't think that consideration is
> nearly as pressing for extended stats.  There's probably a lot less
> client-side code that knows about extended stats at all, and what
> there is seems more likely to rely on the server-side display
> functions than to dig into the catalog details for itself.  Also,
> if there is anything that's looking at pg_statistic_ext details,
> it will need work anyway after this patch; there's no way around that.

I'm working on removing stxkeys[] as a prerequisite commit before the main
join
stats patch, representing all target columns as Var nodes in stxexprs, as
you
both suggested.

One question about the pg_stats_ext view: currently it has two complementar=
y
columns:

- attnames (name[]) =E2=80=94 Names of the columns included in the statisti=
cs object
- exprs (text[]) =E2=80=94 Expressions included in the statistics object

With stxkeys gone from the catalog, should the view:

(a) Stay as-is: keep attnames and exprs as separate columns with the same
semantics. Implemented via a helper function that extracts plain column
names
from the unified stxexprs.

or

(b) Mirror the catalog: remove attnames, make exprs show all entries (both
column names and expressions together in one text[] array).

Any preference?

--=20
Alexandra Wang
EDB: https://www.enterprisedb.com

--000000000000d6c2090652d03e9f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Tom and Tomas,<br><br>Thank you so much for the fe=
edback!<br><br>On Mon, May 25, 2026 at 8:04=E2=80=AFAM Tom Lane &lt;<a href=
=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>&gt; Toma=
s Vondra &lt;<a href=3D"mailto:tomas@vondra.me">tomas@vondra.me</a>&gt; wri=
tes:<br>&gt; &gt; On 5/21/26 22:25, Tom Lane wrote:<br>&gt; &gt;&gt; I don&=
#39;t love stxkeyrefs[].=C2=A0 I wonder if it&#39;s time to throw away<br>&=
gt; &gt;&gt; stxkeys[], represent all the target columns as regular express=
ion<br>&gt; &gt;&gt; trees in stxexprs, and then special-case columns that =
are simple<br>&gt; &gt;&gt; Vars where appropriate at execution.<br>&gt; &g=
t;&gt; (In the same vein, I dislike the grammar&#39;s separation of plain<b=
r>&gt; &gt;&gt; columns from expressions; I&#39;d like to replace stats_par=
ams<br>&gt; &gt;&gt; with expr_list and sort it all out later.=C2=A0 But pe=
rhaps that&#39;s<br>&gt; &gt;&gt; material for a separate patch.)<br>&gt; <=
br>&gt; &gt; FWIW the extended stats copied this from pg_index, which also =
stores<br>&gt; &gt; keys and expressions separately. I suppose there was a =
reason for that,<br>&gt; &gt; most likely performance - is cheaper to compa=
re attnums than<br>&gt; &gt; expressions, and plain keys are much more comm=
on.<br>&gt; <br>&gt; I think I might be to blame for the separate storage o=
f indexprs.<br>&gt; If so, the motivation was to avoid breakage of older co=
de that only<br>&gt; knew about indkey[]. =C2=A0(Of course, such code would=
 necessarily fail<br>&gt; on indexes with expressions, but we wanted to avo=
id breakage for the<br>&gt; common case of no-expressions.) =C2=A0I don&#39=
;t think that consideration is<br>&gt; nearly as pressing for extended stat=
s.=C2=A0 There&#39;s probably a lot less<br>&gt; client-side code that know=
s about extended stats at all, and what<br>&gt; there is seems more likely =
to rely on the server-side display<br>&gt; functions than to dig into the c=
atalog details for itself.=C2=A0 Also,<br>&gt; if there is anything that=
9;s looking at pg_statistic_ext details,<br>&gt; it will need work anyway a=
fter this patch; there&#39;s no way around that.<br><br>I&#39;m working on =
removing stxkeys[] as a prerequisite commit before the main join<br>stats p=
atch, representing all target columns as Var nodes in stxexprs, as you<br>b=
oth suggested.<br><br>One question about the pg_stats_ext view: currently i=
t has two complementary<br>columns:<br><br>- attnames (name[]) =E2=80=94 Na=
mes of the columns included in the statistics object<br>- exprs (text[]) =
=E2=80=94 Expressions included in the statistics object<br><br>With stxkeys=
 gone from the catalog, should the view:<br><br>(a) Stay as-is: keep attnam=
es and exprs as separate columns with the same<br>semantics. Implemented vi=
a a helper function that extracts plain column names<br>from the unified st=
xexprs.<br><br>or<br><br>(b) Mirror the catalog: remove attnames, make expr=
s show all entries (both<br>column names and expressions together in one te=
xt[] array).<br><br>Any preference?<br></div><div><br></div><span class=3D"=
gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signa=
ture"><div dir=3D"ltr"><font face=3D"monospace">Alexandra Wang</font><div><=
font face=3D"monospace">EDB: <a href=3D"https://www.enterprisedb.com" targe=
t=3D"_blank">https://www.enterprisedb.com</a></font></div></div></div></div=
>

--000000000000d6c2090652d03e9f--