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 1pFqLq-0002yg-JW for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 05:42:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pFqLp-0004Xs-6b for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 05:42:09 +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 1pFqLo-0004XQ-ST for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 05:42:08 +0000 Received: from mail-qt1-x829.google.com ([2607:f8b0:4864:20::829]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pFqLm-000531-8t for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 05:42:07 +0000 Received: by mail-qt1-x829.google.com with SMTP id d16so2763065qtw.8 for ; Wed, 11 Jan 2023 21:42:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:from:content-language:to:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=l6W4oBurffoX7FekHeIynOAjXbxhgfD3ECFa1M1tghE=; b=EUQUqDiroh0rsg94o1KEviUdjg8fy+5XZ7WrACOzrAOTJOJBeLqzq5vjFkis9WuuYK jlJqdoNIVs4kmm0XyhvYgSylsFOg7hHvX1XwUXRXE2rcOYVwQp5dCqX22BqNg+9/9UF5 LH5fwO+n7khtYDlaYRunJmLBohY97D359h4+TQQ2ua2vM3GykN5TJRvceEbH47SsfZ6s xYMB+VScgmREZwAc+eq3iZK2vjhXumrqelvp3kt7voFzPqQ3i1PsYrhlNTAmrOGevr7j XA65DUjKpsyBUGHa1N/OCMvNhqeT+2soeBTnZ5TztudWBRfrFVwGzglYS9vLKPmBNDAt QzFw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=subject:from:content-language:to:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=l6W4oBurffoX7FekHeIynOAjXbxhgfD3ECFa1M1tghE=; b=dj143Dpb2BaxQTlZ18NXKJ3AWpMgab/dAeHlDSDWFl0sAdfngZi3Wd432lF2M18HlN uLEj4JjcthIZHhIX9RfQRvByPf5Q0aFWFLd6xc4Bzxt58UrWyetwQF8vwtAsGdo782W1 3ke99SzHTVUv1euVNO6d0saLPa/KAbi6GsK/VwTip/1CtvkJywS2bqZiZFJfrmARb6LM 2bUz2lhNbKZeywv9v4M99YDqckq1e0wV6Ah3BjIwKG47qXRUXXwO/COSbifk6MHIQ4cD gy5OpX/mkGo5eNobIMA00ppuyiNUS/oUL1ZuuDEAzWmuZMvI6IqahRRiZsO+NR38yJLz TnZA== X-Gm-Message-State: AFqh2kqSpD4kBfqmzi60Ez0RaBlmBisF1duGxqRSO9EekR9RoIBFvRFy 7uNtLoMIGy8Vmj9DZY1S5WAok3bfMgtmhg== X-Google-Smtp-Source: AMrXdXsI7njFDmt0gE7G084EBcQ3/IgM11n1fJDAUF4U0IYatq/kb8kiJQ09VnNAU2DKp2ErSBlDqw== X-Received: by 2002:ac8:648:0:b0:3ab:dcb9:bf4a with SMTP id e8-20020ac80648000000b003abdcb9bf4amr23414812qth.50.1673502125204; Wed, 11 Jan 2023 21:42:05 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id d5-20020ac851c5000000b003afbf704c7csm2994428qtn.24.2023.01.11.21.42.04 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 11 Jan 2023 21:42:04 -0800 (PST) Content-Type: multipart/alternative; boundary="------------ef70fJHVgC0QgPR9f9cBzBHN" Message-ID: <269a6794-aa20-283d-bbc4-8cddaa2038de@gmail.com> Date: Wed, 11 Jan 2023 23:42:04 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 To: pgsql-general Content-Language: en-US From: Ron Subject: EXPLAIN and FK references? List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ef70fJHVgC0QgPR9f9cBzBHN Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Pg 12.11 Deletes are slow in one table with many indices and FK references. That's not surprising, but it's *VERY* slow, and I'm trying to figure out why. Is there any EXPLAIN option which shows what "query plans" Pg is using when checking FK references (index scan, seq scan, etc) during deletes (and inserts and updates)? -- Born in Arizona, moved to Babylonia. --------------ef70fJHVgC0QgPR9f9cBzBHN Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Pg 12.11

Deletes are slow in one table with many indices and FK references.  That's not surprising, but it's VERY slow, and I'm trying to figure out why.

Is there any EXPLAIN option which shows what "query plans" Pg is using when checking FK references (index scan, seq scan, etc) during deletes (and inserts and updates)?

--
Born in Arizona, moved to Babylonia.
--------------ef70fJHVgC0QgPR9f9cBzBHN--