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 56DB1D1B179 for ; Tue, 20 Jul 2004 22:36:25 -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 32848-01 for ; Wed, 21 Jul 2004 01:36:25 +0000 (GMT) Received: from houston.familyhealth.com.au (fhnet.arach.net.au [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id 08BAAD1B16E for ; Tue, 20 Jul 2004 22:36:19 -0300 (ADT) Received: from [192.168.0.40] (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9p1/8.12.9) with ESMTP id i6L1aFbP094485; Wed, 21 Jul 2004 09:36:15 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <40FDC8F0.30102@familyhealth.com.au> Date: Wed, 21 Jul 2004 09:37:52 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707) X-Accept-Language: en-us, en MIME-Version: 1.0 To: andrew@pillette.com Cc: pgsql-performance@postgresql.org Subject: Re: Unbearably slow cascading deletes References: <200407201919.i6KJJBw08728@pillette.com> In-Reply-To: <200407201919.i6KJJBw08728@pillette.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/138 X-Sequence-Number: 7529 > I have (among other things) a parent table with 200 records and a child table with 20MM or more. I set up referential integrity on the FK with ON DELETE CASCADE. > > It appears that when a DELETE is done on the parent table, the child table deletion is done with a sequential scan. I say this because it took over four minutes to delete a parent record THAT HAD NO CHILDREN. The DB is recently analyzed and SELECTs in the child table are done by the appropriate index on the FK. Do you have an index on the foreign key field? Chris