Tracking per-RelOptInfo uniqueness during planning

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Andy Fan <zhihuifan1213(at)163(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>
Subject: Tracking per-RelOptInfo uniqueness during planning
Date: 2026-07-22 05:50:12
Message-ID: CAMbWs4-iLcqBr_n_F5gNrzQbBMrKgkpGwqTu7boWeoYepf=+8g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

This has been proposed several times over the years. The most recent
and most developed attempts are the work by Andy Fan and David Rowley
[1]. This patch takes a lot from that work and the discussion around
it: the basic shape of a per-RelOptInfo list of unique keys, built
bottom-up, with the keys expressed over ECs in the same spirit as
pathkeys.

One of the main things that are different in this patch is how
outer-join nullability is represented, which was one of the harder
parts of the earlier attempts.

A UniqueKey is a set of ECs plus a single boolean "nullable" flag, and
keys are always kept over the base (un-nulled) ECs. The flag records
whether an outer join may have introduced NULLs into the key columns,
rather than recomputing that from the members' varnullingrels and NOT
NULL information at each lookup.

The payoff is that one structure serves two kinds of consumer with
different needs. When the flag is false the guarantee is NULL-aware,
which is what justifies removing a DISTINCT or GROUP BY. When it is
true, distinctness is guaranteed only among rows whose key columns are
all non-NULL; that is weaker, but still enough to prove a join
inner-unique or to match a strict join clause, where a NULL key row
cannot match anything anyway. Keeping keys on the base EC also makes
stacked outer joins fall out naturally, with no per-join remapping.

Another thing that is different in this patch is that the join-level
derivation is more thorough: a side's key is preserved when the other
side is unique for the join clauses, the union of a key from each side
is a key of the join, and an inner join's strict clauses can
strengthen a nullable key back to non-nullable.

Also, in addition to base and join relations, this patch also derives
unique keys for subqueries and for the query's upper relations. And
it makes the unique keys usable in more cases:

* removing a redundant DISTINCT step;
* removing a redundant GROUP BY step;
* proving a join inner-unique in innerrel_is_unique(), now including
multi-relation inner sides and not just base relations;
* skipping the unique-ification of a semijoin's RHS that is already
distinct.

Please see the commit message and the README in this patch for a
fuller description.

This patch is still WIP. But since I plan to switch to v19 items and
some bug fixes, I'd like to post it here now in case others are
interested.

[1] https://postgr.es/m/7mlamswjp81p.fsf@e18c07352.et15sqa

- Richard

Attachment Content-Type Size
v1-0001-Introduce-UniqueKeys-to-track-the-distinctness-of.patch application/octet-stream 104.5 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Ajin Cherian 2026-07-22 05:54:00 Re: [PATCH] Preserve replication origin OIDs in pg_upgrade
Previous Message shveta malik 2026-07-22 05:42:53 Re: sequencesync worker race with REFRESH SEQUENCES