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 CFB6A535FA for ; Thu, 14 Apr 2005 13:11:53 -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 27414-06 for ; Thu, 14 Apr 2005 16:11:44 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id AE10D53B94 for ; Thu, 14 Apr 2005 13:11:43 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 8921435398; Thu, 14 Apr 2005 09:11:41 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 6FDEE35394; Thu, 14 Apr 2005 09:11:41 -0700 (PDT) Date: Thu, 14 Apr 2005 09:11:41 -0700 (PDT) From: Stephan Szabo To: Tom Lane Cc: Richard van den Berg , Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: Foreign key slows down copy/insert In-Reply-To: <742.1113491145@sss.pgh.pa.us> Message-ID: <20050414090931.P42874@megazone.bigpanda.com> References: <425E6045.4060201@trust-factory.com> <425E792B.4020500@familyhealth.com.au> <425E7D96.6020201@trust-factory.com> <20050414074154.M38465@megazone.bigpanda.com> <742.1113491145@sss.pgh.pa.us> 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/295 X-Sequence-Number: 11692 On Thu, 14 Apr 2005, Tom Lane wrote: > Stephan Szabo writes: > > ... 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. > > Not necessarily. It occurs to me that maybe what we need is "lossy > storage" of the trigger events. If we could detect that the queue of > pending checks for a particular FK is getting large, we could discard > the whole queue and replace it with one entry that says "run the > wholesale check again when we are ready to fire triggers". I'm not Yeah, but there's a potentially large middle ground where neither our current plan nor check the entire table is particularly good for that we might be able to handle better. It'd be nice to also fall back to check the entire table for even larger changes.