ES009: Slow foreign-key trigger

• Signal: a foreign-key action trigger on the referenced side that takes at least 20% of the execution time. That is a RI_ConstraintTrigger_a_… trigger, or a constraint trigger of a DELETE when the plan does not name it (text plans without VERBOSE).
• Evidence: trigger, constraint, calls, total time and time per call.
• Action: an index on the referencing columns of the child table.
• Stays silent when: the trigger is an INSERT’s check trigger (RI_ConstraintTrigger_c_…), which looks up the referenced table’s unique index.

Example

The delete_fk_trigger scenario of the test corpus, captured on PostgreSQL 16: DELETE on a referenced table; the foreign-key trigger scans the unindexed order_items.order_id for every deleted row.

DELETE FROM orders WHERE id > 199980;

Delete on public.orders  (cost=0.42..8.77 rows=0 width=0) (actual time=0.098..0.098 rows=0 loops=1)
  Buffers: shared hit=47
  ->  Index Scan using orders_pkey on public.orders  (cost=0.42..8.77 rows=20 width=6) (actual time=0.009..0.013 rows=20 loops=1)
        Output: ctid
        Index Cond: (orders.id > 199980)
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=92
Planning Time: 0.333 ms
Trigger RI_ConstraintTrigger_a_16417 for constraint order_items_order_id_fkey: time=305.286 calls=20
Execution Time: 305.451 ms

explainsql reports:

HIGH  ES009 Slow foreign-key trigger
The foreign-key trigger for order_items_order_id_fkey took 100% of the execution time.
Trigger: RI_ConstraintTrigger_a_16417
Constraint: order_items_order_id_fkey
Calls: 20
Time: 305.3 ms (100% of the execution)
Time per call: 15.3 ms
→ Index the referencing columns of order_items_order_id_fkey on the referencing table: each deleted or updated row looks up the rows that refer to it, and without an index every lookup scans that table.

All rules