Database Indexes

Imagine a users table containing millions of rows.

SELECT id, email
FROM users
WHERE email = 'student@example.com';

Without an appropriate index, PostgreSQL may need to inspect a large part of the table.

CREATE UNIQUE INDEX idx_users_email
ON users(email);

Indexes Have a Cost

Each index:

• consumes storage,
• must be updated during writes,
• adds maintenance work,
• may increase insert and update cost.

Composite Indexes

SELECT *
FROM orders
WHERE user_id = $1
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

A useful index may be:

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;

Look for expensive scans, sorts, poor row estimates and repeated loops.

Measure slow query → inspect plan → add targeted index → measure again.