Tips on troubleshooting slow DELETE (suspect cascades)

[Jim Vanns <jvanns@xxxxxxx>]

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 = 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?

Cheers

Jim

--

Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London