Re: Tips on troubleshooting slow DELETE (suspect cascades)

Add an index to parent_id. What is likely happening is each time a row is
deleted, it has to scan the entire table to make sure it is not referenced
by any parent_id records.

On Thu, Jan 18, 2024 at 12:04 PM Jim Vanns <jvanns(at)ilm(dot)com> wrote:

> After dropping the constraint entirely the DELETE completes in 4
> minutes (the same time as the dry-run using SELECT against the
> function instead of a DELETE). A marked improvement on 3 hours
> followed by a pg_cancel_backend()!
>
> Jim
>
> On Thu, 18 Jan 2024 at 16:37, Jim Vanns <jvanns(at)ilm(dot)com> 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
> > 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
>
>
>
> --
> Jim Vanns
> Principal Production Engineer
> Industrial Light & Magic, London
>
>
>