X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2F62A9FA4E1 for ; Mon, 15 May 2006 12:17:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 98024-02-2 for ; Mon, 15 May 2006 12:17:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8A1F09FA226 for ; Mon, 15 May 2006 12:17:40 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k4FFHVAj003261; Mon, 15 May 2006 11:17:31 -0400 (EDT) To: "Jim C. Nasby" cc: Enzo Daddario , PGADMIN Subject: Re: Constraint question In-reply-to: <20060515151249.GE26212@pervasive.com> References: <1147659556.29084.26.camel@localhost.localdomain> <21805.1147661450@sss.pgh.pa.us> <20060515151249.GE26212@pervasive.com> Comments: In-reply-to "Jim C. Nasby" message dated "Mon, 15 May 2006 10:12:49 -0500" Date: Mon, 15 May 2006 11:17:31 -0400 Message-ID: <3260.1147706251@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/182 X-Sequence-Number: 21714 "Jim C. Nasby" writes: > On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote: >> Enzo Daddario writes: >>> I am now required the remove thousands of obsolete records from table >>> "X" and even though I have removed all related rows in all related >>> tables, the deletion of rows in table "X" is taking WAY TOO LONG. >> >> You need to put indexes on the referencing columns. Then start a fresh >> session for doing the actual deletes (FK check plans are usually cached >> per-session). > Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the > same machinery to do the checking, regardless of how much data there is > to check? It's the same machinery. We've speculated about having the thing switch over to doing a full-table recheck (comparable to what ADD CONSTRAINT FOREIGN KEY does) once the number of pending individual row checks exceeds some threshold, but that's not done yet --- and it's not clear how to do it in a concurrent fashion, so don't hold your breath ... regards, tom lane