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/[email protected] -- Best Regards Andy Fan
