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 1rQVec-001kNe-IT for pgsql-general@arkaria.postgresql.org; Thu, 18 Jan 2024 16:54:11 +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 1rQVeb-004H8S-KM for pgsql-general@arkaria.postgresql.org; Thu, 18 Jan 2024 16:54:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQVeb-004H8K-AK for pgsql-general@lists.postgresql.org; Thu, 18 Jan 2024 16:54:09 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rQVeY-002UcJ-IE for pgsql-general@lists.postgresql.org; Thu, 18 Jan 2024 16:54:08 +0000 Received: by mail-pl1-x636.google.com with SMTP id d9443c01a7336-1d3f29fea66so75208075ad.3 for ; Thu, 18 Jan 2024 08:54:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705596844; x=1706201644; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=IZdBXTesPhHASlZiSmsWnlJDFxfGfBQFyfiCxJAdLzA=; b=VQCXZ8U8f3dlun0SGBSukrr6KNRnJtuGFVXHiWGkazhF603jw/cq8QLlYzBCuhRmbH Gcw9s8VN13kh5OuQIMoEP6HkUiBfdXM+gTeH3K/bffs44AxWI0yfTZrUTGHjGR3BvV0Z Pb43P0qrcmSAQMuxNUUtz/GxeaHRsdcVOn8dsljIwamI29a/b+870UShZG0Ht3QTjmhu Mxhr6xMvkvSYhhovxudfMP+jOj6DCs5V0PRf3R3kXKCY9BCMdPePrQs/IPA494hL+yk1 3BP/id62o9Y0QLNsUeOUsM+XNugfosTtn3KHakx2aUZS6D7RfsZMrQKhMjGz9SKACUEL jZDw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705596844; x=1706201644; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=IZdBXTesPhHASlZiSmsWnlJDFxfGfBQFyfiCxJAdLzA=; b=G0R2wPzKfLiwXeI3lP3Dfmwn54o4WnwUbA1EwUqROqS9WgK64t3UN/yS7bmAiFMOy2 r26G1UiYahJQBBMDSGQ87l6ZyDAzHvEiv0k9eCXocO8wKlu4qVADQZuwFMdgIyjKboKM NJNsFh9J7Wsg4LHQ9wbtaNeScmMKPy922UPaIDu5LAHYFst8d9pReykxj1ov0JTeetx6 +gVJBCyO9yIdVTKJvFF2XjfNgeI1a7CD/Vc4aeqozBTHJ9oLKREyqSQTCk51c6HNt8xu 9f7I0PmoRJBS0EiAJQT+79ObTXudTNI3dInA6mqn8R/XZQFbPa3A/HmwKPPaeWfyNPmF +g6g== X-Gm-Message-State: AOJu0YwKh4Nv1fdXspGkusfVvcHlZPmL8IceXt8/IPEqzlgl5ahONhJF r+B0K9DgXh/8zrKRZa6LqfKR9hP7X0ur4clPmxSOUAQ9oOGY2zAr X-Google-Smtp-Source: AGHT+IExuWITHN0vjFfa6Pj1AUZJHjdlnkRtXmz3ChxPDJ4lPlRqvCsSvzF/wXhLdE0vIZQ6PvhkXA== X-Received: by 2002:a17:902:ee8a:b0:1d5:f25a:3d43 with SMTP id a10-20020a170902ee8a00b001d5f25a3d43mr1035975pld.76.1705596844448; Thu, 18 Jan 2024 08:54:04 -0800 (PST) Received: from smtpclient.apple ([2601:681:5a00:f790:c826:e018:1b4d:1f26]) by smtp.gmail.com with ESMTPSA id w20-20020a170902d11400b001d717e64f0esm219885plw.87.2024.01.18.08.54.03 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 18 Jan 2024 08:54:04 -0800 (PST) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable From: Rob Sargent Mime-Version: 1.0 (1.0) Subject: Re: Tips on troubleshooting slow DELETE (suspect cascades) Date: Thu, 18 Jan 2024 09:53:52 -0700 Message-Id: References: <61a626c1-6b68-42f1-b108-26536fac9b06@aklaver.com> Cc: Jim Vanns , Tom Lane , pgsql-general@lists.postgresql.org In-Reply-To: <61a626c1-6b68-42f1-b108-26536fac9b06@aklaver.com> To: Adrian Klaver X-Mailer: iPhone Mail (21C66) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Jan 18, 2024, at 9:46=E2=80=AFAM, Adrian Klaver wrote: >=20 > =EF=BB=BFOn 1/18/24 08:37, Jim Vanns wrote: >> Hi Tom/Adrian. >> I should have already stated I did begin with EXPLAIN but given they >> don't easily work with (the internals) stored/procedures, it wasn't >> useful in this case. Also, I keep having to terminate the statement >> because it never runs to completion and produces the plan (at least in >> ANALYZE VERBOSE mode anyway). >> I have, however, pulled the function body code out and produced an >> isolated case that can be EXPLAINED. The table in question is a >=20 > I'm guessing the function is prune_function(timestamp)? >=20 > What is the function body code? >=20 >> curious one since it models a hierarchy as an adjacency list and so >> the fkey reference is back to itself (to a primary key - so is an >> additional index required?): >> CREATE TABLE tree ( >> ts TIMESTAMPTZ NOT NULL >> tree_id BIGINT NOT NULL, >> parent_id BIGINT NULL, >> -- >> CONSTRAINT cstr_tree_pky PRIMARY KEY (tree_id) INCLUDE (parent_id), >> FOREIGN KEY (parent_id) REFERENCES tree(tree_id) >> ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED >> ); >> CREATE INDEX ON tree USING BRIN (ts); >> The tree table has 95,915,630 rows. >> I've not yet got a complete or reliable plan :( I have made a DB copy >> and will be dropping the constraint to see what effect that has. >> Cheers, >=20 Have you tried making a table from prune_function() and using that in the de= lete statement? >=20 > -- > Adrian Klaver > adrian.klaver@aklaver.com >=20 >=20 >=20