This is more or less correct, I was looking for performance gains on the [possible] differences during DML and DDL. If Jim is correct, is there a particular reason that PostgreSQL does not behave like other RDBMs without a SET ALL DEFERRED? Or is this a discussion best left for -HACKERS? On 2/27/07, Jim C. Nasby <jim@xxxxxxxxx> wrote:
On Tue, Feb 27, 2007 at 06:43:51PM -0600, John Jawed wrote: > Is there any difference as far as when the "uniqueness" of values is > checked in DML between a unique index vs a unique constraint? Or is > the only difference syntax between unique indices and constraints in > PostgreSQL? Syntax only, AFAIK. I prefer using constraints if I actually want to constrain the data; it makes it clear that it's a restriction. In some databases if you know that an index just happens to be unique you might gain some query performance by defining the index as unique, but I don't think the PostgreSQL planner is that smart. There can also be some additional overhead involved with a unique index (vs non-unique), such as when two backends try and add the same key at the same time (one of them will have to block). -- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
