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