X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6D66D536CB for ; Thu, 14 Apr 2005 11:45:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92161-01 for ; Thu, 14 Apr 2005 14:45:29 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id BD19C53613 for ; Thu, 14 Apr 2005 11:45:28 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 781F33527F; Thu, 14 Apr 2005 07:45:33 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 7694D35271; Thu, 14 Apr 2005 07:45:33 -0700 (PDT) Date: Thu, 14 Apr 2005 07:45:33 -0700 (PDT) From: Stephan Szabo To: Richard van den Berg Cc: Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: Foreign key slows down copy/insert In-Reply-To: <425E7D96.6020201@trust-factory.com> Message-ID: <20050414074154.M38465@megazone.bigpanda.com> References: <425E6045.4060201@trust-factory.com> <425E792B.4020500@familyhealth.com.au> <425E7D96.6020201@trust-factory.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/280 X-Sequence-Number: 11677 On Thu, 14 Apr 2005, Richard van den Berg wrote: > Hello Chris, > > Thanks for your answers. > > Christopher Kings-Lynne wrote: > > Deferring makes no difference to FK checking speed... > > But why then is the speed acceptable if I copy and then manually add the > FK? Is the check done by the FK so much different from when it is done > automatically using an active deffered FK? Yes, because currently the check done by the FK on an insert type activity is a per-row inserted check while the check done when adding a FK acts on the entire table in a go which allows better optimization of that case (while generally being worse on small number inserts especially on large tables). At some point, if we can work out how to do all the semantics properly, it'd probably be possible to replace the insert type check with a per-statement check which would be somewhere in between. That requires access to the affected rows inside the trigger which I don't believe is available currently.