Add Indexes on WHERE and JOIN Columns

Why This Matters

Queries filtering or joining on unindexed columns cause full table scans, which become exponentially slower as tables grow. What takes 10ms on 1,000 rows can take 10 seconds on 1 million rows.

This is the single most common performance issue in Postgres databases. In production, you'll see this when:

• Dashboard queries that were fast during development become slow with real data
• API endpoints timeout under load
• Background jobs take hours instead of minutes
• Database CPU spikes to 100% during peak traffic

The Problem

Without an index, Postgres must scan every row in the table to find matches:

-- No index on customer_id causes full table scan
SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN shows:
-- Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)
--   Filter: (customer_id = 123)
--   Rows Removed by Filter: 999900

The Seq Scan indicates a full table scan. With 1 million rows, Postgres reads all of them just to find 100 matching orders.

The Solution

Create an index on columns used in WHERE clauses:

-- Create index on frequently filtered column
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN shows:
-- Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)
--   Index Cond: (customer_id = 123)

For JOIN columns, always index the foreign key side:

-- Index the referencing column (the FK side)
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Common Mistakes

• Indexing low-cardinality columns: An index on a boolean is_active column with 50% true values won't help much - Postgres will still scan half the table. Better to use a partial index: WHERE is_active = true.
• Forgetting to index foreign keys: Postgres doesn't automatically index FK columns. Every REFERENCES should have a corresponding index.
• Using functions on indexed columns: WHERE LOWER(email) = '[email protected]' won't use an index on email. Create a functional index: CREATE INDEX ON users (LOWER(email)).
• Too many indexes: Each index slows down writes. Only index columns actually used in WHERE, JOIN, or ORDER BY clauses.

How to Find Missing Indexes

Use this query to find tables being sequentially scanned:

SELECT 
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / seq_scan AS avg_rows_per_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

High seq_tup_read with low idx_scan indicates tables that need indexes.