Skip site navigation (1) Skip section navigation (2)

Re: Difference between UNIQUE constraint vs index

From: "John Jawed" <johnjawed(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Difference between UNIQUE constraint vs index
Date: 2007-02-28 01:19:57
Message-ID: a9eb35850702271719v732c8c1ved4c1142a0653b03@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)nasby(dot)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(at)nasby(dot)net
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>

In response to

pgsql-general by date

Next:From: Jorge GodoyDate: 2007-02-28 02:37:03
Subject: Re: How to debug this crash?
Previous:From: Joshua D. DrakeDate: 2007-02-28 00:59:23
Subject: Re: Slony subscription problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group