Get a real query plan for EXPLAIN DELETE

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Method 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:

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

Of course, if there is 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 …

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply