Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D2F65637241 for ; Wed, 10 Feb 2010 07:01:54 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 23180-07 for ; Wed, 10 Feb 2010 11:01:38 +0000 (UTC) X-Greylist: delayed 00:06:07.167827 by SQLgrey-1.7.6 Received: from smtp2b.orange.fr (smtp2b.orange.fr [80.12.242.146]) by mail.postgresql.org (Postfix) with ESMTP id EAEE7632202 for ; Wed, 10 Feb 2010 07:01:39 -0400 (AST) Received: from me-wanadoo.net (localhost [127.0.0.1]) by mwinf2b23.orange.fr (SMTP Server) with ESMTP id 34A84700036A for ; Wed, 10 Feb 2010 11:55:30 +0100 (CET) Received: from me-wanadoo.net (localhost [127.0.0.1]) by mwinf2b23.orange.fr (SMTP Server) with ESMTP id 24F7770019DE for ; Wed, 10 Feb 2010 11:55:30 +0100 (CET) Received: from axege.com (LNeuilly-152-22-23-133.w193-251.abo.wanadoo.fr [193.251.23.133]) by mwinf2b23.orange.fr (SMTP Server) with ESMTP id 066437003929 for ; Wed, 10 Feb 2010 11:55:30 +0100 (CET) X-ME-UUID: 20100210105530262.066437003929@mwinf2b23.orange.fr Received: from localhost (localhost [127.0.0.1]) by axege.com (Postfix) with ESMTP id AE9D943DE02 for ; Wed, 10 Feb 2010 11:55:29 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at myhostname="axege.com." Received: from axege.com ([127.0.0.1]) by localhost (axege.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id PuD+-84HJOgR for ; Wed, 10 Feb 2010 11:55:29 +0100 (CET) Received: from [10.0.0.39] (unknown [10.0.0.39]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by axege.com (Postfix) with ESMTPSA id 434B243DE01 for ; Wed, 10 Feb 2010 11:55:29 +0100 (CET) Subject: Deferred constraint and delete performance From: Franck Routier To: pgsql-performance@postgresql.org Content-Type: text/plain; charset="UTF-8" Organization: =?ISO-8859-1?Q?Ax=E8ge?= sarl Date: Wed, 10 Feb 2010 11:55:31 +0100 Message-ID: <1265799331.28404.14.camel@franck-laptop> Mime-Version: 1.0 X-Mailer: Evolution 2.28.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201002/155 X-Sequence-Number: 37576 Hi, I am trying to improve delete performance on a database with several foreign keys between relations that have 100M or so rows. Until now, I have manually disabled the triggers, done the delete, and re-enabled the triggers. This works, but I have to do that when I am sure no other user will access the database... I am wondering if deferring foreign key constraints (instead of disableing them) would increase performance, compared to non deferred constraints (and compared to disableing the constraints, but I guess no in this case). Thanks, Franck