X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8BB4E4764E7 for ; Wed, 14 May 2003 20:47:48 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 75841-10 for ; Wed, 14 May 2003 20:47:47 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id 361E3474E4F for ; Wed, 14 May 2003 20:47:46 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id KAA16672 for ; Thu, 15 May 2003 10:57:16 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3EC2E4F5.5020704@oasis.net.au> Date: Thu, 15 May 2003 10:53:09 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: Postgres Performance Subject: Re: constraint with reference to the same table References: <20030514162559.O51040-100000@megazone23.bigpanda.com> <3EC2D7D5.1010404@oasis.net.au> <20030515001239.GC1549@nordlb.lv> In-Reply-To: <20030515001239.GC1549@nordlb.lv> Content-Type: multipart/alternative; boundary="------------020406090500050303030907" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/162 X-Sequence-Number: 1941 This is a multi-part message in MIME format. --------------020406090500050303030907 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Victor, I see. Good point. Thank you kindly. Regards Rudi. Victor Yegorov wrote: >* Rudi Starcevic [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. > > > > --------------020406090500050303030907 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Victor,

I see.
Good point.

Thank you kindly.
Regards
Rudi.


Victor Yegorov wrote:
* Rudi Starcevic <rudi@oasis.net.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.


  

--------------020406090500050303030907--