PostgreSQL DELETE Slow? Check for Unindexed Foreign Keys

PostgreSQL requires an index on the column a foreign key points to, but not on the column that holds the reference. So a child table can have a foreign key and no index on it. Nothing goes wrong on insert. The cost appears when a parent row is deleted: PostgreSQL has to find the child rows that reference it, and without an index, it reads the whole child table, once for every parent row.

The usual symptom is a delete that should touch a few rows and takes seconds: removing a customer, offboarding a tenant, a retention job. In this lab, deleting one customer took 1.7 seconds. With the right indexes it took under 1.1 milliseconds. The lab used PostgreSQL 18.6 in Docker on a laptop, with all tables in memory; the script and full output are public.

The schema

customers    100,000 rows
orders       2,000,000 rows, 115 MB  customer_id REFERENCES customers ON DELETE CASCADE
order_items  4,000,000 rows, 230 MB  order_id    REFERENCES orders    ON DELETE CASCADE
audit_notes    500,000 rows,  25 MB  customer_id REFERENCES customers   (NO ACTION)
                                     index on (created_at, customer_id)

Each customer has 20 orders and each order has 2 items. No referencing column is indexed. audit_notes has an index that contains customer_id, but as its second column.

Deleting one customer

BEGIN;
EXPLAIN (ANALYZE, COSTS OFF) DELETE FROM customers WHERE id = 42;
ROLLBACK;

 Delete on customers (actual time=0.612..0.613 rows=0.00 loops=1)
   ->  Index Scan using customers_pkey on customers (actual time=0.029..0.029 rows=1.00 loops=1)
         Index Cond: (id = 42)
 Planning Time: 0.119 ms
 Trigger for constraint orders_customer_id_fkey on customers: time=41.537 calls=1
 Trigger for constraint audit_notes_customer_id_fkey on customers: time=15.128 calls=1
 Trigger for constraint order_items_order_id_fkey on orders: time=1567.066 calls=20
 Execution Time: 1624.427 ms

The plan itself took 0.6 ms. The rest is in the Trigger for constraint lines at the bottom, which are easy to miss. Foreign keys are enforced by internal row-level triggers, and each line gives one constraint's total time and number of calls:

• orders_customer_id_fkey, 1 call: the cascade deleted the customer's 20 orders with one scan of orders.
• order_items_order_id_fkey, 20 calls: each of those 20 deleted orders cascaded into order_items, and each cascade scanned all 4 million rows. That is 1.57 of the 1.62 seconds.
• audit_notes_customer_id_fkey, 1 call: a check that no note still references the customer, which scanned all of audit_notes even though this customer had none.

The cost multiplies down the chain. Deleting 10 customers took 16.4 seconds, with 200 calls to the order_items trigger. A delete of a customer with no orders and no notes still took 81 ms, because both checks have to read the child tables to learn there is nothing there.

Because EXPLAIN ANALYZE really runs the delete, run it inside BEGIN and ROLLBACK, and on a copy of production rather than production itself when the delete is large.

Why it is hard to see in production

On a live system you rarely have the EXPLAIN output. You have monitoring, and there the cost is hidden in three ways.

pg_stat_statements shows only the parent statement. With the default pg_stat_statements.track = top, the lab showed one entry for the delete and nothing for the scans inside it. With track = all, the nested statements appear:

                                 query                                       | calls | total_ms
-----------------------------------------------------------------------------+-------+---------
 DELETE FROM customers WHERE id = $1                                         |     1 |     1617
 DELETE FROM ONLY "public"."order_items" WHERE $1 OPERATOR(pg_catalog.=) ... |    20 |     1562
 DELETE FROM ONLY "public"."orders" WHERE $1 OPERATOR(pg_catalog.=) "cust... |     1 |       44
 SELECT $2 FROM ONLY "public"."audit_notes" x WHERE $1 OPERATOR(pg_catalog... |     1 |       10

The FROM ONLY ... OPERATOR(pg_catalog.=) form is how the foreign-key triggers write their queries, so it is easy to search for. track can only be changed by a superuser; on RDS and Aurora, change it in the parameter group. Nested statements add entries, so turn it on for the investigation rather than leaving it on by default.

auto_explain logs only the parent, unless told otherwise. With auto_explain.log_nested_statements = on, each slow cascade is logged with its plan:

LOG:  duration: 78.015 ms  plan:
  Query Text: DELETE FROM ONLY "public"."order_items" WHERE $1 OPERATOR(pg_catalog.=) "order_id"
  Query Parameters: $1 = '60197'
  Delete on order_items (actual time=78.014..78.014 rows=0.00 loops=1)
    ->  Seq Scan on order_items (actual time=2.682..78.003 rows=2.00 loops=1)
          Filter: ('60197'::bigint = order_id)
          Rows Removed by Filter: 3999998

Two rows deleted, four million read. The 15 ms audit_notes check was under the 50 ms threshold and was not logged; a small child table can be scanned thousands of times without any single scan crossing the threshold.

The table statistics do show it. After one 1-customer delete, one 10-customer delete and one delete of a customer with no children:

SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables WHERE relname IN ('orders','order_items','audit_notes');

   relname   | seq_scan | seq_tup_read | idx_scan
-------------+----------+--------------+----------
 audit_notes |       12 |      6000000 |        0
 order_items |      220 |    879959820 |        0
 orders      |       12 |     23999100 |        0

880 million order_items rows read to find 440. A large child table with a growing seq_scan count and no application query that should scan it is worth checking against its foreign keys.

Find unindexed foreign keys

This query lists foreign keys with no index whose leading columns are exactly the key's columns, in any order. It skips partial indexes, which may not cover every row:

SELECT c.conrelid::regclass AS child_table,
       c.conname AS constraint_name,
       c.confrelid::regclass AS parent_table,
       array_agg(a.attname ORDER BY k.n) AS fk_columns,
       pg_size_pretty(pg_relation_size(c.conrelid)) AS child_size
FROM pg_constraint c
CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS k(attnum, n)
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.indpred IS NULL
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] <@ c.conkey)
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_relation_size(c.conrelid) DESC;

 child_table |       constraint_name        | parent_table |  fk_columns   | child_size
-------------+------------------------------+--------------+---------------+-----------
 order_items | order_items_order_id_fkey    | orders       | {order_id}    | 230 MB
 orders      | orders_customer_id_fkey      | customers    | {customer_id} | 115 MB
 audit_notes | audit_notes_customer_id_fkey | customers    | {customer_id} | 25 MB

It flagged audit_notes despite the index on (created_at, customer_id). A B-tree index helps a lookup on customer_id only when customer_id leads it, and the lab's checks on that table were all sequential scans. In a multi-tenant schema with composite keys such as (tenant_id, customer_id), the same rule applies to the pair.

Which ones to index

The list is a starting point, not a to-do list. Each index adds write cost and storage to the child table: the one on order_items was 77 MB on a 230 MB table. Index a foreign key when:

• Parent rows are deleted: customer deletion, tenant offboarding, retention purges, test-data cleanup.
• Parent key values are updated. That fires the same triggers as a delete. Rare with surrogate keys, common with natural ones.
• Queries join or filter on the column, for example loading a customer's orders. Then it was needed regardless.

A foreign key to a lookup table of statuses or countries that are never deleted can usually go without one. Put the large child tables of frequently deleted parents first; the pg_stat_user_tables counters and track = all show which ones the workload actually hits.

Build the index without blocking writes

CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);
CREATE INDEX CONCURRENTLY order_items_order_id_idx ON order_items (order_id);

A plain CREATE INDEX blocks inserts, updates and deletes on the table for the whole build. CONCURRENTLY does not, but it takes longer, waits for transactions already running, and cannot run inside a transaction block, which matters for migration tools that wrap each migration in one. If it fails, it leaves an INVALID index that still adds write cost; drop it and run it again. In the lab the builds took 0.5 s, 1.0 s and 0.09 s on tables in memory; on a production table, measure it on a copy first.

After the indexes

The trigger lines told the same story: the order_items cascade went from 1,567 ms to 0.4 ms for the same 20 calls. Absolute times come from a laptop with every table in memory. On larger tables, or when the scans read from network storage, each sequential scan costs more, so the gap is usually wider, not narrower.

The duration also matters for locking. While the delete runs, it holds row locks on every row it deletes, in every child table, until the transaction ends. Other sessions that update those rows wait. A schema change on any of these tables waits too, and every query behind it queues; see the lock queue lab. Splitting the delete into smaller transactions shortens each one but does not reduce the scans, because they run once per parent row.

Checklist

1. Run the unindexed foreign key query and sort by child table size.
2. Find the slow deletes or purge jobs in pg_stat_statements; for the investigation, set track = all to see the nested foreign-key statements.
3. Check seq_scan and seq_tup_read on the large child tables.
4. Confirm with EXPLAIN ANALYZE inside a rolled-back transaction, on a copy: read the Trigger for constraint lines.
5. Index the foreign keys whose parents are deleted or updated, with CREATE INDEX CONCURRENTLY, then check for INVALID indexes.
6. Run the same delete again and compare the trigger times.

For the general method, from ranking statements to validating a fix, see how to profile slow PostgreSQL queries. Delete-heavy tables also leave dead tuples behind; if they are not being cleaned up, see autovacuum not removing dead tuples.