Jeff Davis <pg...@j-davis.com> writes: > On Mon, 2009-07-06 at 18:27 +0100, Simon Riggs wrote: >> In many cases, people add unique indexes solely to allow replication to >> work correctly. The index itself may never be used, especially in high >> volume applications.
> Interesting. Maybe we should at least try to leave room for this feature > to be added later. I agree that, from a theoretical perspective, > requiring a UNIQUE constraint to use an index is wrong. For one thing, > you can't ensure the uniqueness without defining some total order > (although you can define an arbitrary total order for cases with no > meaningful total order). This seems a bit pointless. There is certainly not any use case for a constraint without an enforcement mechanism (or at least none the PG community is likely to consider legitimate ;-)). And it's not very realistic to suppose that you'd check a constraint by doing a seqscan every time. Therefore there has to be an index underlying the constraint somehow. Jeff's complaint about total order is not an argument against having an index, it's just pointing out that btree is not the only possible type of index. It's perfectly legitimate to imagine using a hash index to enforce uniqueness, for example. If hash indexes had better performance we'd probably already have been looking for a way to do that, and wanting some outside-the-AM mechanism for it so we didn't have to duplicate code from btree. Also, if hash indexes were a realistic alternative to btree for this, we'd already have come up against the problem that the CONSTRAINT syntax doesn't provide any way to specify what kind of index you want to use underneath the constraint. I think it might be interesting to turn around Jeff's syntax sketch and provide a way to say that a CONSTRAINT declaration should depend on some previously added index, eg something like ALTER TABLE tab ADD CONSTRAINT UNIQUE (col1, col2) USING index Not sure how that squares exactly with the question of variant definitions of uniqueness semantics (as opposed to purely implementation decisions like hash vs btree). But it's a different way to come at it. 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