Get a real query plan for EXPLAIN DELETE

Question

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.

Answer 1

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.

Answer 2

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.

Running the actual DELETE query (with EXPLAIN ANALYZE wrapper or not) is considerably more expensive than checking with a SELECT whether any FK reference will prevent the operation.

Identify FK constraints pointing to your table:

SELECT c.conrelid::regclass::text AS referencing_table
     , pg_get_constraintdef(c.oid) AS fk
FROM   pg_constraint c
WHERE  c.confrelid = 'public.bigtype'::regclass
AND    c.contype  = 'f'
ORDER  BY 1, 2;

See:

• How to list table foreign keys

You get 0-n rows of the form:

referencing_tbl | FOREIGN KEY (referencing_col, ...) REFERENCES matches(referenced_col, ...) ...

Based on this, the fastest possible query would be:

SELECT EXISTS (
   SELECT FROM matches m
   WHERE  id = 1
   AND   (EXISTS (SELECT FROM referencing_tbl t WHERE (t.referencing_col, ...) = (m.referenced_col, ...))
    -- OR EXISTS ... -- one predicate per FK
         )
   );

true ... At least one row is being referenced. DELETE will fail.
false ... No references. DELETE will succeed.

With concurrent write access, there is a possible race condition. Probably unimportant for your case.

If your relational design is not stable, you might generate that SELECT query completely dynamically ...