Re: Tips on troubleshooting slow DELETE (suspect cascades)

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

Jim

On Tue, 16 Jan 2024 at 22:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> > On 1/16/24 09:45, Jim Vanns wrote:
> >> I have a slow (CPU bound) DELETE statement I'm attempting to debug and I
> >> suspect that its actually the ON DELETE CASCADE on the foreign key thats
> >> causing it.
>
> 99% of the time, the cause is lack of an index on the foreign key's
> referencing columns. We make you have a unique index on the
> referenced columns, because otherwise the FK constraint's semantics
> are unclear. But you're not required to make one on the other side.
>
> >> What I need is a way to see into this statement as it executes to
> >> confirm my suspicion - does anyone have any tips on that?
>
> > Explain:
> > https://www.postgresql.org/docs/current/sql-explain.html
>
> Specifically, if EXPLAIN ANALYZE shows a lot of time spent in the
> enforcement trigger for the FK, this is likely what's happening.
>
> regards, tom lane

--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London