On 07.01.26 14:21, Matthias van de Meent wrote:
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.
Unenforced constraints should be considered invalid (how would we
prove they're valid?), and because the planner is not allowed to rely
on invalid constraints, why would the planner be allowed to remove
these nodes for unenforced-and-therefore-invalid unique constraints?
It's quite likely that the query output would be incorrect when we
expected the data to be unique for unenforced constraints, while the
data in the table isn't actually unique for that unenforced unique
constraint; and knowingly giving incorrect results is not something I
can agree to.
Some other SQL implementations have optional implementation-specific
modes on top of NOT ENFORCED that mean, the constraint is not enforced,
but you can assume it holds for query planning purposes. This is
probably what the thinking here was, but that is not how PostgreSQL
currently works, so additional work would need to be done to add this
additional mode.
There are probably also multiple levels to this. It's one thing to use
an unenforced-but-probably-true constraint for things like selectivity
estimation, where you just get a worse plan if it's not quite true, but
it's another to assume something is unique when it might not be. So
there is some research to be done here.
