Get a real query plan for EXPLAIN DELETE

I have a delete query that runs a long time. I try to explain it:

EXPLAIN DELETE FROM matches
WHERE id = 1

And I get:
QUERY PLAN
Delete on matches (cost=0.43..2.65 rows=1 width=6)
-> Index Scan using matches_pkey on matches (cost=0.43..2.65 rows=1 width=6)
Index Cond: (id = 1)


But this couldn't possibly be the complete cost, because the this table is referenced in many foreign key constraints that need to be checked, and I know for a fact that this query takes 40 seconds to complete.

How can I see the total cost of such a delete query and all the foreign key checks that need to happen before it completes?

I tried EXPLAIN (ANALYZE, BUFFERS) but that does not show me this information when the query is aborted due to the foreign key constraint in another table. It just shows the error message ("violates foreign key constraint"), but does not explain how much time it spent to get to that error message.

It's OK for it to fail (and, for my logic, revert the whole transaction), but I don't want this failure to take 2ms instead of 40 seconds. I already solved my issue, but I'm still curious if it's possible to get costs of a failed query.

You need to run EXPLAIN (ANALYZE, BUFFERS).

Beware that that actually executes the DELETE, so run it in a transaction and ROLLBACK afterwards.

That will show you information about foreign key constraints unless they are deferred, in which case they are executed at commit time.

I'm still curious if it's possible to get costs of a failed query.

In this case, the costs will be somewhere between the costs of the index scan (after which the trigger for the foreign key starts) and the total costs. It depends if the conflicting row is found early or late.

StackExchange Database Administrators Q#291546, answer score: 4