PostgreSQL Indexes

Overview

Indexes improve query performance by allowing faster data retrieval. They work like a book index.

Create Index

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

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

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

Index Types

B-tree Index

-- Default index type
CREATE INDEX idx_users_name ON users USING btree (name);

Hash Index

-- For equality comparisons only
CREATE INDEX idx_users_email ON users USING hash (email);

GIN Index

-- For arrays, full-text search, JSONB
CREATE INDEX idx_products_tags ON products USING gin (tags);
CREATE INDEX idx_content ON articles USING gin (to_tsvector('english', content));

GiST Index

-- For geometric data, full-text search
CREATE INDEX idx_locations ON locations USING gist (coordinates);

Partial Index

-- Index only active users
CREATE INDEX idx_active_users_email ON users(email) WHERE status = 'active';

Expression Index

-- Index on expression
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Use expression index
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

Managing Indexes

-- Rename index
ALTER INDEX idx_users_name RENAME TO idx_users_email_name;

-- Drop index
DROP INDEX IF EXISTS idx_users_name;

-- Reindex
REINDEX TABLE users;

Summary

Index key points:

• B-tree (default), Hash, GIN, GiST
• Partial and expression indexes
• Improve query performance
• Add overhead on writes