pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Sargent <robjsargent@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Jim Vanns <jvanns@ilm.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Tips on troubleshooting slow DELETE (suspect cascades)
Date: Thu, 18 Jan 2024 09:53:52 -0700
Message-ID: <D3BC9F79-9267-4F2A-94C1-18BD5B669D91@gmail.com> (raw)
In-Reply-To: <61a626c1-6b68-42f1-b108-26536fac9b06@aklaver.com>
References: <61a626c1-6b68-42f1-b108-26536fac9b06@aklaver.com>



> On Jan 18, 2024, at 9:46 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 
> On 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
> 
> I'm guessing the function is prune_function(timestamp)?
> 
> What is the function body code?
> 
>> 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,
> 

Have you tried making a table from prune_function() and using that in the delete statement?
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> 
> 
> 





view thread (9+ messages)  latest in thread

Message-ID: <D3BC9F79-9267-4F2A-94C1-18BD5B669D91@gmail.com>
Permalink:  ../D3BC9F79-9267-4F2A-94C1-18BD5B669D91@gmail.com/
Also on:    postgresql.org/message-id/D3BC9F79-9267-4F2A-94C1-18BD5B669D91@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: robjsargent@gmail.com, adrian.klaver@aklaver.com, jvanns@ilm.com, tgl@sss.pgh.pa.us
  Subject: Re: Tips on troubleshooting slow DELETE (suspect cascades)
  In-Reply-To: <D3BC9F79-9267-4F2A-94C1-18BD5B669D91@gmail.com>
  References: <61a626c1-6b68-42f1-b108-26536fac9b06@aklaver.com> <D3BC9F79-9267-4F2A-94C1-18BD5B669D91@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox