Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ph9IW-0008Pw-Iz for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 13:23:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ph9IV-0006K5-HE for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 13:23:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ph9IV-0006Jw-6T for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 13:23:35 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ph9IS-0002XA-Tq for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 13:23:34 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 32SDNVbC3463922; Tue, 28 Mar 2023 09:23:31 -0400 From: Tom Lane To: Dominique Devienne cc: pgsql-general@lists.postgresql.org Subject: Re: Plans for ON DELETE CASCADE? Which index is used, if at all? In-reply-to: References: Comments: In-reply-to Dominique Devienne message dated "Tue, 28 Mar 2023 14:45:57 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3463920.1680009811.1@sss.pgh.pa.us> Date: Tue, 28 Mar 2023 09:23:31 -0400 Message-ID: <3463921.1680009811@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Dominique Devienne writes: > Hi. Given the classical parent/child schema below, with an ON DELETE > CASCADE FK constraint, I'd like to know which index is used (if any) to > delete child rows affected by the CASCADE. > But explain (analyze) does not give me that. Yeah, it will just report the time spent in the FK triggers, not what they were doing exactly. IIRC, you can see the CASCADE operations with contrib/auto_explain, if you enable auto_explain.log_nested_statements. regards, tom lane