Re: Difference between UNIQUE constraint vs index

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@nasby.net> 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@nasby.net
> >EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
>

--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net