Re: Tips on troubleshooting slow DELETE (suspect cascades)

On 1/16/24 09:45, Jim Vanns wrote:
> Hi,
>
> 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. I suspect this because the dry-run mode of the same query (a
> SELECT instead of DELETE) doesn't suffer the same fate. The statement is
> effectively;
>
> # Dry mode
> SELECT prune_function(timestamp);
> # Destructive mode
> DELETE FROM foobar p USING prune_function(timestamp) AS e WHERE p.id
> <http://p.id> = e.prune_id
>
> The logs seem to hold no information on the progress of the statement
> but the CPU is pegged at 100% for hours. The SELECT equivalent runs in
> under a minute.
>
> 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

It would also be helpful to reply with the table definitions for the
tables. If that is not possible then at least whether there is an index
on the FK reference in the child table(s)?

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com