Adding -general back in. On Tue, Feb 27, 2007 at 07:19:15PM -0600, John Jawed wrote: > 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? Well, currently only FK constraints support deferred. And IIRC it's not generally a performance gain, anyway. What I was trying to say is that if you're running a query (generally a SELECT) with certain conditions, the planner can make use of the knowledge that a column or set of columns is guaranteed to be unique. PostgreSQL currently can't do that. > John > > 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) > > > -- Jim C. Nasby, Database Architect jim@xxxxxxxxx 512.569.9461 (cell) http://jim.nasby.net
