Thread

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

Tom Lane <tgl@sss.pgh.pa.us> — 2026-05-27T20:31:16Z

Tomas Vondra <tomas@vondra.me> writes:
> On 5/27/26 19:49, Alexandra Wang wrote:
>> 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).

> My 2c: AFAIR there's no fundamental reason to keep those two lists
> separate, other than that expressions were "bolted on" later, after we
> already had stats on plain attributes. In hindsight, it might have been
> better to just unify the view back then, probably.

Yeah.  There are some other oddities that arise from that: expressions
get shoved to the end.  For example, if I put in

create statistics my_stats (mcv) on ten, (ten+four), four from tenk1;

pg_dump will regurgitate that as

CREATE STATISTICS public.my_stats (mcv) ON four, ten, (ten + four) FROM public.tenk1;

and I see that that column ordering is consistent with what appears in
pg_stats_ext and perhaps other places.  I'd expect a rewritten version
to stop doing that and preserve the user-written column order.
So there are going to be some potential minor incompatibilities for
anything that is looking too closely at this view, and it seems to
me that it might be better for such code to fail noisily rather than
perhaps silently mis-associate stats with columns.

(It might be a good idea to have some test cases that exercise this
kind of scenario in pg_upgrade, especially now that we are trying to
transfer extended stats in upgrades...)

			regards, tom lane