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 00359D1B3A4 for ; Tue, 20 Jul 2004 16:45:16 -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 94029-03 for ; Tue, 20 Jul 2004 19:45:15 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 8A440D1B38B for ; Tue, 20 Jul 2004 16:45:12 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 02A143514B; Tue, 20 Jul 2004 12:45:11 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 012F535127; Tue, 20 Jul 2004 12:45:11 -0700 (PDT) Date: Tue, 20 Jul 2004 12:45:10 -0700 (PDT) From: Stephan Szabo To: andrew@pillette.com Cc: pgsql-performance@postgresql.org Subject: Re: Unbearably slow cascading deletes In-Reply-To: <200407201919.i6KJJBw08728@pillette.com> Message-ID: <20040720123547.J31189@megazone.bigpanda.com> References: <200407201919.i6KJJBw08728@pillette.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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/134 X-Sequence-Number: 7525 On Tue, 20 Jul 2004 andrew@pillette.com wrote: > 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. > > Let me guess, the cascade trigger's query plan is decided at schema load > time, when the optimizer has no clue. Is there a way to fix this without > writing my own triggers, using PL/PGSQL EXECUTE to delay the planner? The query plan should be decided at the first cascaded delete for the key in the session. However, IIRC, it's using $arguments for the key values, so it's possible that that is giving it a different plan than it would get if the value were known. What do you get if you prepare the query with an argument for the key and use explain execute?