X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B02D74764C8 for ; Wed, 14 May 2003 21:01:01 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 79101-03 for ; Wed, 14 May 2003 21:01:00 -0400 (EDT) Received: from shield.nordlb.lv (shield.pirmabanka.lv [195.114.57.4]) by postgresql.org (Postfix) with ESMTP id A39754764FA for ; Wed, 14 May 2003 21:00:59 -0400 (EDT) Received: from wall2.pirmabanka.lv (wall2-pub.nordlb.lv [195.114.57.14]) by shield.nordlb.lv (8.12.9/8.12.9) with SMTP id h4F112bg018902 for ; Thu, 15 May 2003 04:01:02 +0300 Received: from share.nordlb.lv ([10.0.0.10]) by wall2.pirmabanka.lv; Thu, 15 May 2003 04:01:01 +0300 (EET DST) Received: from pc311x1.nordlb.lv (localhost.localdomain [127.0.0.1]) by nordlb.lv (8.11.6/8.11.6) with SMTP id h4F111W21057 for ; Thu, 15 May 2003 04:01:01 +0300 Received: (qmail 2426 invoked by uid 100); 15 May 2003 01:03:41 -0000 Date: Thu, 15 May 2003 04:03:41 +0300 From: Victor Yegorov To: Stephan Szabo Cc: Rudi Starcevic , Postgres Performance Subject: Re: constraint with reference to the same table Message-ID: <20030515010341.GD1549@nordlb.lv> References: <20030515001239.GC1549@nordlb.lv> <20030514174656.B52132-100000@megazone23.bigpanda.com> Mime-Version: 1.0 Content-Type: text/plain; charset=koi8-r Content-Disposition: inline In-Reply-To: <20030514174656.B52132-100000@megazone23.bigpanda.com> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/164 X-Sequence-Number: 1943 * Stephan Szabo [15.05.2003 03:54]: > > 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. You're right. I thought of big tables after posting the reply. My solution is suitable for my case, i.e. not so big tables. Returning to the very first question I asked. May be it is usefull to implicitly create index on foreign key columns? Actually, untill you had pointed on seq. scans, I thought Postgres is using internal indicies - don't ask me why. -- Victor Yegorov