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

Re: constraint with reference to the same table

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Victor Yegorov <viy(at)nordlb(dot)lv>
Cc: Rudi Starcevic <rudi(at)oasis(dot)net(dot)au>,Postgres Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: constraint with reference to the same table
Date: 2003-05-15 00:49:42
Message-ID: 20030514174656.B52132-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 15 May 2003, Victor Yegorov wrote:

> * Rudi Starcevic <rudi(at)oasis(dot)net(dot)au> [15.05.2003 02:59]:
> > Hi,
> >
> > Can I confirm what this means then ..
> >
> > For large table's each column with ref. inegritry I should create an
> > index on those columns ?
>
> I think, that indicies are needed only at delete stage to decrease search
> time of possible referencing rows.
> Not only, of course, but when we speak about
> INSERT/UPDATE/DELETE data it is so.
>
> On the other side, indicies increases total query runtime, because for
> each row deleted/updated/inserted it'll be necessary to update each index.
>
> In my case, I at first drop "cyclic" constraints, do the job and then
> restore them.

That can be a win, but if you're actually dropping and adding the
constraint again it may not be on large tables since it'll still do a
whole bunch of index lookups to check the existing rows when the alter
table add constraint happens.  Disabling triggers and re-enabling them is
faster but breaks the guarantee of the constraint.


In response to

Responses

pgsql-performance by date

Next:From: Rudi StarcevicDate: 2003-05-15 00:53:09
Subject: Re: constraint with reference to the same table
Previous:From: Stephan SzaboDate: 2003-05-15 00:46:47
Subject: Re: constraint with reference to the same table

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