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

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

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> I would still really like to decouple this from CREATE INDEX because of
> two reasons:
> 1. Cannot support multiple constraints per index very easily. I think
> this is a significant feature.
> 2. Must decide to make constraint at the same time as making the
> index, and once it's there, you can't remove it without dropping
> the index.

I don't actually find either of those arguments to be credible in the
least. I don't think that people will find it useful to enforce
multiple constraints with one index, and I don't believe that they'll
design an index without knowledge of the constraint they will enforce
with it. The closest precedent we have is the UNIQUE constraint.
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
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.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2009-09-20 16:48:38 Re: FDW-based dblink (WIP)
Previous Message Jeff Davis 2009-09-20 16:44:00 Re: WIP: generalized index constraints