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 180EFD1B211 for ; Tue, 20 Jul 2004 16:55:17 -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 96658-05 for ; Tue, 20 Jul 2004 19:55:16 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 846BCD1B1FD for ; Tue, 20 Jul 2004 16:55:14 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 1E65C35522; Tue, 20 Jul 2004 12:55:14 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 1C0F03551F; Tue, 20 Jul 2004 12:55:14 -0700 (PDT) Date: Tue, 20 Jul 2004 12:55:14 -0700 (PDT) From: Stephan Szabo To: andrew@pillette.com Cc: pgsql-performance@postgresql.org Subject: Re: Unbearably slow cascading deletes In-Reply-To: <20040720123547.J31189@megazone.bigpanda.com> Message-ID: <20040720125453.W31688@megazone.bigpanda.com> References: <200407201919.i6KJJBw08728@pillette.com> <20040720123547.J31189@megazone.bigpanda.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/135 X-Sequence-Number: 7526 On Tue, 20 Jul 2004, Stephan Szabo wrote: > > 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? To be clear, I mean prepare/explain execute an example select/delete from the fk.