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.klaver@xxxxxxxxxxx
