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

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: operator exclusion constraints [was: generalized index constraints]
Date: 2009-09-20 17:01:14
Message-ID: 1253466074.6983.22.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.


> 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

Jeff Davis

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Davis 2009-09-20 17:08:26 Re: WIP: generalized index constraints
Previous Message Tom Lane 2009-09-20 17:01:11 Re: WIP: generalized index constraints