**Single-Column Index:**

CREATE INDEX idx_email ON users(email);

-- For: WHERE email = 'user@example.com'

-- Simple, works for any query on email

**Composite Index (Column Order Matters):**

CREATE INDEX idx_user_status_date ON orders(

user_id, -- Most selective, equality

status, -- Equality

created_at -- Range (last)

);

-- For: WHERE user_id = 123 AND status = 'pending' AND created_at > '2024-01-01'

-- Can also use for: WHERE user_id = 123 (leftmost prefix)

-- Cannot use for: WHERE status = 'pending' (doesn't start with user_id)

**Covering Index (Includes SELECT Columns):**

CREATE INDEX idx_orders_covering ON orders(

user_id, -- WHERE

status, -- WHERE

created_at, -- WHERE

total, -- SELECT

product_id -- SELECT

);

-- For: SELECT total, product_id WHERE user_id = 123 AND status = 'pending'

-- Index-only scan (no table access)

-- Fastest possible

**Partial Index (Filtered Subset):**

CREATE INDEX idx_active_users ON users(email, created_at)

WHERE active = true; -- Only index active users

-- 80% smaller if only 20% are active

-- Faster to scan

-- Lower write overhead

**Unique Index (Enforces Constraint):**

CREATE UNIQUE INDEX idx_username ON users(username);

-- Prevents duplicate usernames

-- Faster lookups (optimizer knows unique)

**Function-Based Index:**

CREATE INDEX idx_email_lower ON users(LOWER(email));

-- For: WHERE LOWER(email) = 'user@example.com'

-- Enables case-insensitive search

**Monitoring Index Usage (PostgreSQL):**

SELECT