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 225A29FA4EA for ; Mon, 15 May 2006 12:53:17 -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 00511-09 for ; Mon, 15 May 2006 12:53:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 36C709FA3B3 for ; Mon, 15 May 2006 12:53:05 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 4CD7556423; Mon, 15 May 2006 10:53:05 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Mon, 15 May 2006 10:53:03 -0500 Date: Mon, 15 May 2006 10:53:03 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: Enzo Daddario , PGADMIN Subject: Re: Constraint question Message-ID: <20060515155303.GL26212@pervasive.com> References: <1147659556.29084.26.camel@localhost.localdomain> <21805.1147661450@sss.pgh.pa.us> <20060515151249.GE26212@pervasive.com> <3260.1147706251@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3260.1147706251@sss.pgh.pa.us> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060515:tgl@sss.pgh.pa.us::3oEhxaivTSz52fzH:00000000000000000000 0000000000000000000000000djJ X-Hashcash: 1:20:060515:enzo@pienetworks.com::2/fwvGGO0lbP3NwR:00000000000000000 0000000000000000000000003Cla X-Hashcash: 1:20:060515:pgsql-admin@postgresql.org::IaXqnxxWnrZY35Jf:00000000000 000000000000000000000000Gitv X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/185 X-Sequence-Number: 21717 On Mon, May 15, 2006 at 11:17:31AM -0400, Tom Lane wrote: > "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 ... I'm assuming that in order for deferred constraints to work we must have a list of what's changed... couldn't that list be fed into an appropriate query and then planned accordingly? This would allow the backend to use the best validation method possible; merge join, hash join, etc. If the number of changed rows is small, the planner would probably pick nested loop (I'm assuming that's roughly how the IMMEDIATE case works); if the number of changed rows is large it'd favor something else, but at least it wouldn't be re-checking the entire source table. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461