Re: operator exclusion constraints [was: generalized index constraints]

On Sun, 2009-09-20 at 12:45 -0400, Tom Lane wrote:
> How often have we had requests to add or drop UNIQUE in an existing
> index? Maybe there were more than zero, but not by a lot.

Ok.

> As an example of why I don't believe the first item, consider something
> like
> create index ... (a = , b = )
> (or whatever the syntax is to exclude equality on each column
> separately). Yeah, it will work, but have you considered the efficiency
> implications? Searching such an index for b, independently of a, is
> going to suck to such an extent that you'd be *far* better off building
> two separate indexes. We do not have, and are unlikely ever to have,
> index types in which a search that doesn't constrain the first index
> column is efficient.

My use case was something else:

An index on (a, b, c) enforcing the constraints UNIQUE(a, b) and
UNIQUE(a, c).

UNIQUE(a, b) can be enforced efficiently. UNIQUE(a, c) might be less
efficient depending on the selectivity of "a", but as long as "a" is
selective I think it's useful. The alternative is updating two indices
on every insert.

You may still think this use case is too marginal to bother supporting,
but I never made an argument for the use case you described above.

If we move away from multiple constraints per index, are you suggesting
that I also move the constraints out of pg_constraint and back into
pg_index?

Regards,
Jeff Davis