1---
2title: Index Foreign Key Columns
3impact: HIGH
4impactDescription: 10-100x faster JOINs and CASCADE operations
5tags: foreign-key, indexes, joins, schema
6---
7 
8## Index Foreign Key Columns
9 
10Postgres does not automatically index foreign key columns. Missing indexes cause slow JOINs and CASCADE operations.
11 
12**Incorrect (unindexed foreign key):**
13 
14```sql
15create table orders (
16  id bigint generated always as identity primary key,
17  customer_id bigint references customers(id) on delete cascade,
18  total numeric(10,2)
19);
20 
21-- No index on customer_id!
22-- JOINs and ON DELETE CASCADE both require full table scan
23select * from orders where customer_id = 123;  -- Seq Scan
24delete from customers where id = 123;          -- Locks table, scans all orders
25```
26 
27**Correct (indexed foreign key):**
28 
29```sql
30create table orders (
31  id bigint generated always as identity primary key,
32  customer_id bigint references customers(id) on delete cascade,
33  total numeric(10,2)
34);
35 
36-- Always index the FK column
37create index orders_customer_id_idx on orders (customer_id);
38 
39-- Now JOINs and cascades are fast
40select * from orders where customer_id = 123;  -- Index Scan
41delete from customers where id = 123;          -- Uses index, fast cascade
42```
43 
44Find missing FK indexes:
45 
46```sql
47select
48  conrelid::regclass as table_name,
49  a.attname as fk_column
50from pg_constraint c
51join pg_attribute a on a.attrelid = c.conrelid and a.attnum = any(c.conkey)
52where c.contype = 'f'
53  and not exists (
54    select 1 from pg_index i
55    where i.indrelid = c.conrelid and a.attnum = any(i.indkey)
56  );
57```
58 
59Reference: [Foreign Keys](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK)
60