Re: Tracking per-RelOptInfo uniqueness during planning

Hi Richard:

> 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.

Yes, it was started around 2021... and great to know lot of work is still
applicable today. and I'm glad that you can continue with this.

> 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.

This is nice.

> 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.

Besides the above user case, if the user case (3) "Figure out more
interesting pathkey after join with normal UniqueKey" at [1] is correct,
it would be another promising user case.

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

--
Best Regards
Andy Fan