The right index speeds up a query 1000x. The wrong index wastes space and slows down writes.
Index Types¶
- B-tree: Default, equality and range queries
- Hash: Equality only (=)
- GIN: JSONB, array, full-text search
- GiST: Geometry, range types
- BRIN: Large tables with natural ordering
Strategies¶
– Composite index — column order matters! – Rule: equality columns first, then range CREATE INDEX idx_orders ON orders(status, created_at DESC); – Covering index — index-only scan CREATE INDEX idx_users_cover ON users(email) INCLUDE (name, role); – Partial index — only active records CREATE INDEX idx_active ON users(email) WHERE deleted_at IS NULL; – Analyze unused indexes SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE ‘%_pkey’;
Key Takeaway¶
Equality first in composite indexes. Partial for subsets, covering for index-only scans. Remove unused indexes.
databaseindexingpostgresqlperformance