Re: Proposal: Add a UNIQUE NOT ENFORCED constraint

On Wed, Jan 7, 2026 at 8:39 PM Jacob Jackson <jej(dot)jackson(dot)08(at)gmail(dot)com> wrote:
> On Wed, Jan 7, 2026 at 5:22 AM Neil Chen <carpenter(dot)nail(dot)cz(at)gmail(dot)com> wrote:
> > 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?
>
> Setting n_distinct is less clear in describing the data (it isn't tied
> to the table schema itself and can be ambiguous in whether values are
> actually totally unique or just close enough, which can complicate
> things), and, because it only impacts statistics estimations, it still
> doesn't help in queries where uniqueness is required. Postgres will
> still add a node to ensure uniqueness in that case. There are also
> some other limitations with n_distinct (e.g. extended stats are
> required for multi-column pairs, which further complicates
> documentation and adds complexity/overhead).
>

Just thinking out loud here, but I wonder if you might be able to
modify pg_hint_plan to have a "NoUnique" (or "NoOpUnique") hint which
causes the planner to either skip any unique nodes, or have them
function as just a passthrough. I'm not entirely sure that's doable
actually; we don't currently have hints for other types of aggregation
nodes, and at the moment I don't think we have any hints that can
produce wrong answers, and this one feels like it could...

Robert Treat
https://xzilla.net