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@xxxxxxx> 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@xxxxxxx> 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@xxxxxxxxxxxxx> wrote:
> >
> > Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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
