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 D9246D1B1D1 for ; Tue, 20 Jul 2004 16:19:15 -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 79510-07 for ; Tue, 20 Jul 2004 19:19:15 +0000 (GMT) Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net [67.119.5.202]) by svr1.postgresql.org (Postfix) with ESMTP id E80A4D1B1FD for ; Tue, 20 Jul 2004 16:19:11 -0300 (ADT) Received: (from andrew@localhost) by pillette.com (8.11.6/8.11.6) id i6KJJBw08728; Tue, 20 Jul 2004 12:19:11 -0700 Date: Tue, 20 Jul 2004 12:19:11 -0700 From: andrew@pillette.com Message-Id: <200407201919.i6KJJBw08728@pillette.com> Subject: Unbearably slow cascading deletes To: pgsql-performance@postgresql.org Cc: X-Originating-IP: 192.168.1.208 X-Mailer: Webmin 0.940 MIME-Version: 1.0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Archive-Number: 200407/133 X-Sequence-Number: 7524 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? And by the way, if FK conditions like IN (1,3,4) could be handled in a single invocation of the trigger, so much the better.