In many cases, the idiomatic/generally best way to write a query requires a uniqueness check (a SELECT WHERE ... = ANY()/IN or really any semi-join when optimized to an inner join, UNION, etc), meaning a Unique/HashAggregate node will be added, increasing overhead unless there is an explicit unique constraint. An unenforced unique constraint would allow developers to use their knowledge of the data/previous validation procedures to eliminate the extra node. This would also help with documentation/observability tools by providing more context on the data without adding overhead.
I'm not very familiar with the UNIQUE NOT ENFORCED constraint, so apologies if I make any mistakes here. If we want the query planner to generate an execution plan as if a column were unique, would setting n_distinct = -1 in the table statistics achieve the same effect?
