Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C178F633032 for ; Mon, 2 Aug 2010 09:56:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 50922-02-3 for ; Mon, 2 Aug 2010 12:56:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id D602D632BF6 for ; Mon, 2 Aug 2010 09:56:36 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 02 Aug 2010 07:56:34 -0500 Message-Id: <4C567A2E0200002500033FF1@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Mon, 02 Aug 2010 07:56:30 -0500 From: "Kevin Grittner" To: , Subject: Re: performance problems on Delete Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.51 tagged_above=-10 required=5 tests=BAYES_50=0.8, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201008/9 X-Sequence-Number: 35258 Silvio Brandani wrote: > we have a performance issue on deleting data from a table. > > the delete is on the index key so should be very fast but the table > have 13 contraints of type foreign keys and the slowness seems due > to all the works done with the constraint. > Trying to drop the constraint and execute the delete the result is > immediate. The usual reason for this is that you don't have indexes on the referencing columns, so to check whether the delete is OK it has to scan the related tables. Think about what rows need to be checked to determine whether the delete is allowed and index the columns which will allow fast access to them. -Kevin