Jeff Davis <pg...@j-davis.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 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. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers