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 029AC536EF for ; Thu, 14 Apr 2005 13:26:56 -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 30291-10 for ; Thu, 14 Apr 2005 16:26:44 +0000 (GMT) Received: from fep02-app.kolumbus.fi (fep02-0.kolumbus.fi [193.229.0.44]) by svr1.postgresql.org (Postfix) with ESMTP id F29BF53C00 for ; Thu, 14 Apr 2005 13:26:42 -0300 (ADT) Received: from [192.168.1.3] (really [84.230.96.65]) by fep02-app.kolumbus.fi with ESMTP id <20050414162641.TYDE8756.fep02-app.kolumbus.fi@[192.168.1.3]>; Thu, 14 Apr 2005 19:26:41 +0300 Message-ID: <425E99C1.6050506@kolumbus.fi> Date: Thu, 14 Apr 2005 19:26:41 +0300 From: Marko Ristola User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.6) Gecko/20050324 Debian/1.7.6-1 X-Accept-Language: en MIME-Version: 1.0 To: Christopher Kings-Lynne Cc: Richard van den Berg , pgsql-performance@postgresql.org Subject: Re: Foreign key slows down copy/insert References: <425E6045.4060201@trust-factory.com> <425E792B.4020500@familyhealth.com.au> <425E7D96.6020201@trust-factory.com> <425E7F94.4080602@familyhealth.com.au> <425E8766.8070901@trust-factory.com> <425E88B7.6000203@familyhealth.com.au> <425E8B70.2060006@trust-factory.com> <425E8C0B.8060408@familyhealth.com.au> In-Reply-To: <425E8C0B.8060408@familyhealth.com.au> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.424 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/298 X-Sequence-Number: 11695 About the foreign key performance: Maybe foreign key checks could be delayed into the COMMIT phase. In that position, you could check, that there are lots of foreign key checks for each foreign key pending, and do the foreign key check for an area or for the whole table, if it is faster. I have heard, that the database must be in consistent state after COMMIT, but it does not have necessarily to be okay inside a transaction. 1. COMMIT wanted 2. If there are lots of foreign key checks pending, do either an area foreign key check (join result must be 0 rows), or a full table join. 3. If all foreign key checks are okay, complete the COMMIT operation. 4. If a foreign key check fails, go into the ROLLBACK NEEDED state. Maybe Tom Lane meant the same. set option delayed_foreign_keys=true; BEGIN; insert 1000 rows. COMMIT; Regards, Marko Ristola Christopher Kings-Lynne wrote: >> My problem with this really is that in my database it is hard to >> predict which inserts will be huge (and thus need FKs dissabled), so >> I would have to code it around all inserts. Instead I can code my own >> integirty logic and avoid using FKs all together. > > > Just drop the fk and re-add it, until postgres gets more smarts. > > Chris > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org