Message-ID: <3c477f2f-10e4-4705-bb21-90ccbe67e9d2@gmail.com>
Date: Mon, 2 Feb 2026 10:53:16 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Is there value in having optimizer stats for joins/foreignkeys?
To: Tomas Vondra <tomas@vondra.me>,
 Alexandra Wang <alexandra.wang.oss@gmail.com>,
 Corey Huinker <corey.huinker@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@lists.postgresql.org,
 hs@cybertec.at, Jeff Davis <pgsql@j-davis.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>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <e9165403-7b0e-4c01-96d0-b73512ce359d@vondra.me>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/3c477f2f-10e4-4705-bb21-90ccbe67e9d2%40gmail.com>
Precedence: bulk

On 1/2/26 17:39, Tomas Vondra wrote:
> We can't simply store an opaque VIEW, and build the stats by simply
> executing it (and sampling the results). The whole premise of extended
> stats is that people define them to fix incorrect estimates. And with
> incorrect estimates the plan may be terrible, and the VIEW may not even
> complete.

Ok, I got the point.
I think linking to a join or foreign key seems restrictive. In my mind, 
extended statistics may go the following way:

CREATE STATISTICS abc_stat ON (t1.x,t2.y,t3.z) FROM t1,t2,t3;

Suppose t1.x,t2.y, and t3.z have a common equality operator.

Here we can build statistics on (t1.x = t2.y), (t1.x = t3.z), (t2.y = 
t3.z), and potentially (t1.x = t2.y = t3.z).

But I don't frequently detect problems with JOIN estimation using a 
single join clause. Usually, we have problems with (I) join trees 
(clauses spread across joins) and (II) a single multi-clause join.
We can't solve (I) here (kinda statistics on a VIEW might help, I 
think), but may ease (II) using:

CREATE STATISTICS abc_stat ON ((t1.x=t2.x),(t1.y=t2.y)) FROM t1,t2;

or even more bravely:

CREATE STATISTICS abc_stat ON ((t1.x=t2.x),(t1.y=t2.y)) FROM t1,t2
WHERE (t1.z <> t2.z);

-- 
regards, Andrei Lepikhov,
pgEdge