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

On Wed, May 6, 2026 at 8:19 PM Alexandra Wang
<alexandra(dot)wang(dot)oss(at)gmail(dot)com> wrote:
>
> Here's the rebased patch, it only needed a catalog version bump.
>

No need to update src/include/catalog/catversion.h during dev cycle.

+ if (!IsA(lfirst(l), OpExpr))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("join statistics require a single equijoin condition per pair
of tables")));

In the error message, should we replace "equijoin" with "equality join"?
IMHO, "equijoin" is kind of informal wording.

-- Unintended error case1
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, val TEXT NOT NULL);
CREATE STATISTICS x (mcv) ON t1.val FROM t1 JOIN t1 as t1s ON (t1.id = t1s.id);
alter table t1 alter column id set data type int8;
ERROR: could not open relation with OID 0

-- Unintended error case2
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, val TEXT NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, t1_id INTEGER NOT NULL);
CREATE STATISTICS xx (mcv) ON t1.val FROM t2 JOIN t1 ON (t2.t1_id = t1.id);
alter table t1 alter column id set data type int8;
ERROR: missing FROM-clause entry for table "t1"
LINE 1: alter table t1 alter column id set data type int8;
^

-- Unintended error case3
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, val TEXT NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, t1_id INTEGER generated always as (1));
CREATE STATISTICS xx (mcv) ON t1.val FROM t2 JOIN t1 ON (t2.t1_id = t1.id);
alter table t2 alter column t1_id set expression as (2);
ERROR: missing FROM-clause entry for table "t1"
LINE 1: alter table t2 alter column t1_id set expression as (2);
^

-- Unintended error case4
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, val TEXT NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, t1_id INTEGER generated always as (1));
CREATE STATISTICS xx (mcv) ON t1.val FROM t2 JOIN t1 ON (t2 = t1);
ERROR: cache lookup failed for attribute 0 of relation 18388

In src/test/regress/sql/stats_ext_crossrel.sql, we need to add test cases for
ALTER COLUMN SET DATA TYPE and ALTER COLUMN SET EXPRESSION on columns
involved in join statistics.
These ALTER TABLE operations will internally recreate the affected join stats.

src/test/regress/sql/stats_ext_crossrel.sql currently lacks tests for equality
joins where the join qual contains whole-row variable references.This is
important because whole-row variables raise the question of whether join
statistics should be recreated when any column in the relation is modified via
ALTER COLUMN SET DATA TYPE or ALTER COLUMN SET EXPRESSION.

--
jian
https://www.enterprisedb.com/