Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rAJWu-00623v-38 for pgsql-general@arkaria.postgresql.org; Tue, 05 Dec 2023 00:43:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rAJWq-001eAX-Jo for pgsql-general@arkaria.postgresql.org; Tue, 05 Dec 2023 00:43:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rAJWq-001eAO-8n for pgsql-general@lists.postgresql.org; Tue, 05 Dec 2023 00:43:12 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rAJWn-008piW-PC for pgsql-general@lists.postgresql.org; Tue, 05 Dec 2023 00:43:11 +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 3B50h7FM823513; Mon, 4 Dec 2023 19:43:07 -0500 From: Tom Lane To: Ping Yao cc: Merlin Moncure , pgsql-general@lists.postgresql.org Subject: Re: Help understand why DELETE is so slow In-reply-to: References: Comments: In-reply-to Ping Yao message dated "Mon, 04 Dec 2023 13:42:47 -0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <823511.1701736987.1@sss.pgh.pa.us> Date: Mon, 04 Dec 2023 19:43:07 -0500 Message-ID: <823512.1701736987@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ping Yao writes: > Thank you for the comment. I think we do. What I found odd here is the > Query Plan (with analyze) clearly shows the actual execution to be very > quick, that's why I don't think it's an index problem. What do you think? I wouldn't rule it out on that basis. Foreign key enforcement is done by AFTER triggers, which are not part of the query plan. Having said that, all modern versions of PG show trigger runtime as a separate EXPLAIN output line, so slow FK checks aren't that hard to diagnose. I agree with the other comments that this is likely a Citus-specific issue, and that you came to the wrong place for expertise on that. regards, tom lane