Index Foreign Key Columns

Why This Matters

Postgres does not automatically create indexes on foreign key columns. Without indexes, JOINs and CASCADE operations perform full table scans, becoming exponentially slower as tables grow.

• JOIN performance: JOINs on unindexed FK columns scan the entire child table
• CASCADE deletes: Deleting a parent row triggers sequential scans on all child tables
• Referential integrity checks: INSERT/UPDATE on FK columns requires checking the parent table
• Lock contention: Unindexed FKs hold locks longer during CASCADE operations

The Problem

-- FK column has no index
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id) ON DELETE CASCADE
  -- No index on customer_id!
);

-- JOIN causes sequential scan on orders
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 123;

-- EXPLAIN shows Seq Scan on orders (slow!)
-- CASCADE delete scans entire orders table
DELETE FROM customers WHERE id = 123;  -- Very slow

The Solution

-- Create index on FK column
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id) ON DELETE CASCADE
);

-- Always index FK columns
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

-- JOIN now uses index scan (fast!)
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 123;

-- CASCADE delete uses index to find child rows
DELETE FROM customers WHERE id = 123;  -- Fast with index

Best Practices

• Index every FK column: Even if you don't JOIN on it now, CASCADE operations need it
• Create index immediately: Add the index when you create the FK constraint
• Composite foreign keys: Index all columns in composite FKs: CREATE INDEX ON orders (customer_id, product_id);
• Naming convention: Use {table}_{column}_idx for clarity: orders_customer_id_idx

-- Composite FK needs composite index
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  FOREIGN KEY (order_id, product_id) REFERENCES order_products(order_id, product_id)
);

-- Index both columns together
CREATE INDEX order_items_fk_idx ON order_items (order_id, product_id);

Finding Missing FK Indexes

Use this query to find foreign keys without indexes:

SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
LEFT JOIN pg_indexes pi
  ON pi.tablename = tc.table_name
  AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND pi.indexname IS NULL;

Common Mistakes

• Assuming auto-indexing: Postgres doesn't auto-index FKs like some databases. Always create indexes manually.
• Only indexing JOIN columns: Even if you never JOIN on an FK, CASCADE operations need the index.
• Forgetting composite FKs: Multi-column foreign keys need composite indexes on all columns.
• Indexing the wrong side: Index the referencing column (child table), not the referenced column (parent table).