Foreign Key Trigger Cost in Deletes #

Deleting 5,000 old rows from customers takes 38 minutes. The DELETE’s own plan is a 4 ms index scan. The time is in a line below the plan: Trigger for constraint invoices_customer_id_fkey on customers: time=2280412.6 calls=5000. For each deleted customer, PostgreSQL checked invoices for referencing rows — with a sequential scan, because nobody indexed invoices.customer_id.

Write plans in general are covered in data modification query plans. This page covers the most common hidden cost in them.

The Planner Condition #

A foreign key invoices.customer_id REFERENCES customers(id) is enforced by internal triggers on both tables:

PostgreSQL creates an index on the referenced side automatically (the key must be unique) but never on the referencing column. Without an index on invoices.customer_id, each check is a sequential scan of invoices. The cost is deleted parent rows × referencing table scan, for every referencing foreign key, and cascades repeat the pattern at every level.

These checks run as AFTER triggers. They do not appear in the plan tree; EXPLAIN ANALYZE reports them after the plan, one line per constraint, with total time and number of calls. Their queries are planned internally and are not shown.

What deleting one customer triggers Four steps per deleted parent row. The DELETE removes a customer row. The foreign key trigger fires after the row. The trigger queries invoices for rows referencing that customer. Without an index on invoices customer_id that query is a sequential scan of the whole invoices table. DELETE customer one parent row FK trigger AFTER, per row check invoices WHERE customer_id = $1 no index → Seq Scan whole table per row repeated for every deleted row and every referencing constraint

Annotated EXPLAIN Evidence #

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM customers WHERE closed_at < '2020-01-01';
ROLLBACK;
Delete on customers  (actual time=4.2..4.2 rows=0 loops=1)
  Buffers: shared hit=15210
  ->  Index Scan using customers_closed_at_idx on customers  (actual time=0.03..3.1 rows=5000 loops=1)
        Index Cond: (closed_at < '2020-01-01'::date)
Planning Time: 0.4 ms
Trigger for constraint invoices_customer_id_fkey on customers: time=2280412.6 calls=5000
Trigger for constraint sessions_customer_id_fkey on customers: time=1204.3 calls=5000
Trigger for constraint addresses_customer_id_fkey on customers: time=48.2 calls=5000
Execution Time: 2281670.4 ms
-- plan tree: 4 ms. Triggers: 38 minutes.
-- invoices check: 456 ms per call → a sequential scan of invoices per deleted customer
-- addresses check: 0.01 ms per call → an index exists on addresses.customer_id

After indexing the referencing column:

CREATE INDEX CONCURRENTLY invoices_customer_id_idx ON invoices (customer_id);
Trigger for constraint invoices_customer_id_fkey on customers: time=62.1 calls=5000
Trigger for constraint sessions_customer_id_fkey on customers: time=1204.3 calls=5000
Execution Time: 1318.9 ms
Reading the constraint trigger lines Three trigger lines are annotated. The invoices constraint shows 2.28 million milliseconds over 5,000 calls, 456 milliseconds per call, a table scan per row. The sessions constraint shows 0.24 milliseconds per call, a small or partly indexed table. The addresses constraint shows 0.01 milliseconds per call, an indexed check. invoices…fkey: time=2280412.6 calls=5000 456 ms per call: Seq Scan sessions…fkey: time=1204.3 calls=5000 0.24 ms per call addresses…fkey: time=48.2 calls=5000 0.01 ms per call: indexed divide time by calls; anything above a millisecond per call needs an index

Step-by-Step Resolution #

  1. Run the delete under EXPLAIN ANALYZE in a rolled-back transaction and read every Trigger for constraint line. Divide time by calls.

  2. List foreign keys without a supporting index on the referencing side:

    SELECT c.conrelid::regclass AS referencing_table,
       c.conname,
       array_agg(a.attname ORDER BY k.ord) AS columns
FROM pg_constraint c
CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS k(attnum, ord)
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.attnum
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
  )
GROUP BY c.conrelid, c.conname;

    The query treats an index as supporting when its leading columns cover the foreign key columns.

  3. Create the missing indexes concurrently.

    CREATE INDEX CONCURRENTLY invoices_customer_id_idx ON invoices (customer_id);

  4. Check cascades at every level. An ON DELETE CASCADE from customers to invoices to invoice_lines needs indexes on both invoices.customer_id and invoice_lines.invoice_id.

  5. Batch large parent deletes so the per-statement trigger queue stays small and locks are short.

  6. Weigh index cost on write-heavy referencing tables. An index on a large, insert-heavy referencing table costs every insert; if parent rows are almost never deleted or keys never updated, skipping it can be a deliberate choice — document it.

Per-row check cost The invoices foreign key check takes 456 milliseconds per deleted customer without an index and 0.012 milliseconds with one. Sessions takes 0.24 milliseconds per call. Addresses, already indexed, takes 0.01 milliseconds. invoices, no index 456 ms per row invoices, indexed 0.012 ms per row sessions 0.24 ms per row addresses, indexed 0.01 ms per row a sequential scan per deleted row turns a small delete into hours

Before and After #

-- BEFORE: invoices.customer_id not indexed
Delete on customers (4.2 ms)  Trigger for constraint invoices_customer_id_fkey: time=2280412.6 calls=5000   Execution Time: 2281670.4 ms

-- AFTER: CREATE INDEX CONCURRENTLY invoices_customer_id_idx
Delete on customers (4.2 ms)  Trigger for constraint invoices_customer_id_fkey: time=62.1 calls=5000        Execution Time: 1318.9 ms

The same cost in updates and truncation #

Updating a referenced key — UPDATE customers SET id = … — fires the same checks, which is one reason surrogate primary keys should never change. Updates to non-key columns do not fire them. TRUNCATE skips row-level triggers entirely, but refuses to truncate a referenced table unless the referencing tables are truncated in the same command or with CASCADE, so it is not a way around the checks for partial deletes.

The locking side matters too. The check on the referencing table takes FOR KEY SHARE locks on any referencing rows it finds, and concurrent inserts into invoices referencing a customer being deleted block until the delete commits or rolls back. A slow, unindexed check holds those locks longer, so the symptom on a busy system is often blocked inserts elsewhere rather than just a slow delete — the investigation approach is in lock waits that look like slow plans.

Monitoring for the problem before it happens #

Missing foreign key indexes are invisible until someone deletes parent rows, which in many systems happens rarely: a retention job, a GDPR erasure request, an account closure. The first time it runs against production volumes, it can lock referencing tables for hours. Running the catalog query above as part of schema review — or as a check in continuous integration against a migrated schema — catches new foreign keys without indexes when they are introduced, long before a delete exercises them. Treat every exception as an explicit, documented decision rather than an oversight.

Common Pitfalls #

Tuning the delete’s scan. The plan tree is usually trivial. Diagnostic signal: node time in milliseconds, execution time in minutes. Fix: read the trigger lines.

Assuming foreign keys create indexes. Only the referenced key is indexed. Diagnostic signal: referencing columns with no index. Fix: create them explicitly.

Composite foreign keys with partial index coverage. An index on only the first column may still leave a large range to filter. Diagnostic signal: moderate per-call times on composite keys. Fix: index all foreign key columns in order.

Cascades several levels deep. Each level repeats the pattern. Diagnostic signal: trigger lines for tables two levels down. Fix: index every referencing column in the chain.

Frequently Asked Questions #

Why is DELETE slow when the query plan is fast? #

Foreign key checks on tables that reference the deleted rows run as triggers after each row and are reported as Trigger for constraint lines below the plan. Without an index on the referencing column, each check scans the referencing table.

Does PostgreSQL automatically index foreign key columns? #

No. The referenced key must have a unique index, but the referencing column in the child table is not indexed automatically. You need to create that index yourself.

Where do foreign key checks appear in EXPLAIN ANALYZE? #

After the plan tree, as lines like Trigger for constraint name on table: time=… calls=…. Their time is included in Execution Time but not in any plan node.

Up: Data Modification Query Plans