Re: Tips on troubleshooting slow DELETE (suspect cascades)

> On Jan 18, 2024, at 9:46 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
>
>