Why does this DELETE take three minutes?

The symptom

DELETE FROM billing.invoices WHERE id = 48110273;

Run it. The Query status footer says it is running, and keeps saying it. invoices has 8,100,000 rows and invoices_pkey on id, so finding the row took no time. The three minutes are what happens after it is found.

What PostgreSQL does on a DELETE

Two tables point at billing.invoices: billing.payments through invoice_id, and billing.events through invoice_id. Before the delete can finish, PostgreSQL has to prove that no payment and no event still references invoice 48110273. It does that with a trigger on each foreign key, an internal one you did not write, which runs a query shaped like this:

SELECT 1 FROM billing.payments WHERE invoice_id = 48110273 FOR KEY SHARE;

On events the query uses events_invoice_idx and returns at once. On payments there is no index on invoice_id. The only way to answer "is there a payment for this invoice" is to read all 7,900,000 payments. That is the three minutes: one sequential scan of payments, for one deleted row.

With ON DELETE CASCADE it is worse. The scan finds the payments and deletes them, and each deleted payment fires its own checks against whatever points at payments. Cascades multiply the work by the depth of the tree.

What Seqora shows

The diagram

Open the ER diagram for billing, click invoices, and open the Inbound tab under Relationships in the inspector. Two cards, payments (invoice_id) and events (invoice_id), each with a Del: and Upd: rule. Del: No Action means a delete is checked and refused if anything still points here. Del: Cascade means it deletes through. Either way the check runs.

The Constraints page

Under billing.invoices in the explorer, open Constraints. The Points at this table column lists the same two tables with what a delete here does to each. Both constraints are correct, which is the point. The problem is not in invoices at all.

The Indexes page of the other table

Open billing.payments, then Indexes. In Matrix, the invoice_id row carries the note No index. followed by Deleting an invoice scans all payments. The page's verdicts say the same, No index for invoice_id, with Open CREATE INDEX next to it. That sentence is the whole diagnosis.

The schema-wide version is the Indexes report under Schema reports for billing. Other checks lists every foreign key without an index, and payments.invoice_id is the one on it.

The Plan tab

The Plan tab is Pro. Run the DELETE again and click Plan. For a write, the Plan tab shows the estimate only: two nodes, Delete on invoices fed by an Index Scan using invoices_pkey, at a tiny cost, because the planner does not cost triggers. To see the time, click Analyze in rollback. It asks Run this statement to analyze it?, then runs the statement inside BEGIN … ROLLBACK, so the row is deleted and put back. Run and roll back needs RUN typed on a production connection. The button is disabled on a locked connection, so unlock from the sidebar first.

Now the strip above the canvas, Where the time went, is almost one colour: a segment labelled Triggers, "Time inside trigger functions", holding nearly all of the three minutes. Hover it for the share. Foreign key checks are triggers as far as PostgreSQL is concerned, so that is where they land. The plan nodes themselves take a few milliseconds. When the time is in Triggers and the plan is small, stop reading the plan and open the Indexes page of every table that points at yours.

What to do

CREATE INDEX CONCURRENTLY payments_invoice_idx
  ON billing.payments (invoice_id);

This is what Open CREATE INDEX puts in the editor. A CREATE stops at the Review high-risk SQL panel before it runs, which Run, cancel, and transactions explains.

CONCURRENTLY buys you a payments table that stays writable while the index builds. It costs two scans of the table instead of one, a longer build, and the statement cannot run inside a transaction: if the editor shows Transaction open, commit first. If the build is interrupted, the index is left behind invalid, and the Indexes page says payments_invoice_idx is invalid with a Rebuild action.

After it exists, run the DELETE again with Analyze in rollback. The Triggers segment shrinks to nothing worth hovering and the statement takes milliseconds. The foreign key check is now one index lookup.

One more thing you will meet on the way. Type the DELETE without its WHERE and the same panel stops it, with the reason Mutation has no WHERE clause. That one is not slow. It is 8,100,000 invoices and every payment and event they own.

Caveats

• Foreign key checks run as internal triggers, and the Plan tab shows them under Triggers together with any trigger you wrote. invoices also has an audit trigger writing to audit.change_log, so the segment is both. The Triggers page lists only yours.
• Analyze in rollback runs the DELETE for real before rolling it back. It takes the same locks for the same time.
• Cascades multiply. With Del: Cascade from invoices to payments, the index above makes finding the payments fast, and deleting them still fires whatever points at payments.
• The new index is paid for on every INSERT into payments. For a foreign key column that is the right trade, and it is a trade.
• The downloadable sample is much smaller than 7,900,000 payments. Your DELETE will be slow in shape and quick in seconds.