Re: Tips on troubleshooting slow DELETE (suspect cascades)

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,
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com