PostgreSQL Indexes

Proper indexing dramatically improves query performance.

Index Types

• B-tree - Default, good for equality and range
• Hash - Equality only
• GIN - Full-text search
• GiST - Geometric data

Creating Indexes

-- Simple index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;