Re: Is there value in having optimizer stats for joins/foreignkeys?

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Alexandra Wang <[email protected]>
To: Tom Lane <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: jian he <[email protected]>
Cc: [email protected]
Cc: Andrei Lepikhov <[email protected]>
Cc: Corey Huinker <[email protected]>
Cc: [email protected]
Cc: Jeff Davis <[email protected]>
Subject: Re: Is there value in having optimizer stats for joins/foreignkeys?
Date: Wed, 27 May 2026 10:49:44 -0700
Message-ID: <CAK98qZ0_4Kodyemk0Tdmew=YG8jeHQ=wzOydQws4s99A1X2h5g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CADkLM=cUwMftPLFq0iD6-qKRyNiRM2HZGYVp6=0noxA8GfuEtA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<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>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Hi Tom and Tomas,

Thank you so much for the feedback!

On Mon, May 25, 2026 at 8:04 AM Tom Lane <[email protected]> wrote:
> Tomas Vondra <[email protected]> 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 complementary
columns:

- attnames (name[]) — Names of the columns included in the statistics object
- exprs (text[]) — 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?

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Is there value in having optimizer stats for joins/foreignkeys?
  In-Reply-To: <CAK98qZ0_4Kodyemk0Tdmew=YG8jeHQ=wzOydQws4s99A1X2h5g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox