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 1kxnia-000768-6Y for pgsql-performance@arkaria.postgresql.org; Fri, 08 Jan 2021 09:06:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kxniZ-00031W-2M for pgsql-performance@arkaria.postgresql.org; Fri, 08 Jan 2021 09:05:59 +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 1kxniY-00031P-7R for pgsql-performance@lists.postgresql.org; Fri, 08 Jan 2021 09:05:58 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kxniW-00033n-4R for pgsql-performance@lists.postgresql.org; Fri, 08 Jan 2021 09:05:57 +0000 Received: by mail-ej1-x62d.google.com with SMTP id x16so13560632ejj.7 for ; Fri, 08 Jan 2021 01:05:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20150623.gappssmtp.com; s=20150623; h=message-id:subject:from:to:cc:date:in-reply-to:references :user-agent:mime-version:content-transfer-encoding; bh=cvtlYaiWF+PbTjR1zJQ42MQYz2EJRhcp/V5sdUEoG/Q=; b=m4OQEFyUX4av7uE3sKui9lhJctCnM+MNiPdx87hSf9hpt7W4LazsscfA/pzSJ4Ejsv y8ggt9JMjGUXyCG53Da7wQ+klOcOknV+d0vS8NIEv8n9q4iA80omwiCfLSRpitt/eyHx m9clE2rKFRe61L79mcOg51RqLEfCC7ocSCH1qeT64t3pm5GYzVqkvjkn5jFKXCPoZ07k EeCYdS0OhhiIr5LOBGS+CG6ejhiJRIMfIgk7IPbn/6TzuhmiMnsl1g2rGsl5aRzLM+2j s70E01LjP4LkYKpWxfdVNJrR+AT3mFP0BDy3oaz+zvzk9TKKpYyB6WMUxqlKj8+13Aed bqLw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:subject:from:to:cc:date:in-reply-to :references:user-agent:mime-version:content-transfer-encoding; bh=cvtlYaiWF+PbTjR1zJQ42MQYz2EJRhcp/V5sdUEoG/Q=; b=CnAszqKGjv8EtvCYedjZajzCoDZUdqvficLe1Wywuloq7vN+TN9C72AXrbc84brTAu FkuyHNP3OQsGJzrOj29FEhPhkrum7f9Wu6ru0k8vBC1yCNhlS3rV3TuY+39uTPgdWM9b fHt/KDU+IH+KHyn9kJSrcjldao5SGd1aEJ15/GlY2ki7qjR0+u2eyXS6ga4obTDJ8AWr eXLBP4qjSwZFQ+Bc9V6Ovh6atyiq/Q4iBI0XaWkUJWI46hHiWI2lmKeOCh+ZK2OkOKF7 5KG3u+h9+ZIHMcs7uJ3Ozt+J341pnIt07vjm0WkcBwjCYzxM8q7oPkiWZvS9l6zIHJOx B+xQ== X-Gm-Message-State: AOAM53148TI2Ls8mCcs1VKf8Xi2s5k4kg5YRHGi+g0IAp/jLabQlD5iK 9fbl6ayLFRhbcQtz4m1KcxuQAA== X-Google-Smtp-Source: ABdhPJzjXfNBw0k07R9UiUNfQMcRpQ6V7qV4LDbCnSn8qnUmlF1s7j/TEvNnsKd/CYNeYjo18ftmQg== X-Received: by 2002:a17:907:40f0:: with SMTP id nn24mr1977302ejb.233.1610096754819; Fri, 08 Jan 2021 01:05:54 -0800 (PST) Received: from localhost.localdomain (217-149-174-246.nat.highway.telekom.at. [217.149.174.246]) by smtp.gmail.com with ESMTPSA id e19sm3554873edr.61.2021.01.08.01.05.54 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 08 Jan 2021 01:05:54 -0800 (PST) Message-ID: <21dda7db608a2c454d25e799242948e655965df2.camel@cybertec.at> Subject: Re: High COMMIT times From: Laurenz Albe To: Craig Jackson , Don Seiler Cc: pgsql-performance@lists.postgresql.org Date: Fri, 08 Jan 2021 10:05:53 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) MIME-Version: 1.0 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, 2021-01-07 at 10:49 -0700, Craig Jackson wrote: > We had a similar situation recently and saw high commit times that were caused > by having unindexed foreign key columns when deleting data with large tables involved. > You might check to see if any new foreign key constraints have been added > recently or if any foreign key indexes may have inadvertently been removed. > Indexing the foreign keys resolved our issue. Were these deferred foreign key constraints? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com