public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: Tomas Vondra <[email protected]>
To: Alexandra Wang <[email protected]>
To: Corey Huinker <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Jeff Davis <[email protected]>
Subject: Re: Is there value in having optimizer stats for joins/foreignkeys?
Date: Mon, 2 Feb 2026 10:53:16 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CADkLM=cUwMftPLFq0iD6-qKRyNiRM2HZGYVp6=0noxA8GfuEtA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CADkLM=fEi_GeeS3zyg6B5WgswyPe0wNXHfKQOxjy8A5fXHD7=A@mail.gmail.com>
	<CAK98qZ2mW=geT9NKe5vC68-sB9EJe_887uV=MCFt6y9AhyTp7A@mail.gmail.com>
	<CAK98qZ0LwJbUoiZjjFXitojHy4UskkjYDiSd_JZfGE9LbfZm9w@mail.gmail.com>
	<[email protected]>
	<[email protected]>

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

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]
  Subject: Re: Is there value in having optimizer stats for joins/foreignkeys?
  In-Reply-To: <[email protected]>

* 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