Proper indexes are key for database performance.
Types¶
- B-tree — default, equality + range
- Hash — equality only
- GIN — full-text, JSONB, arrays
- GiST — geometry, range
- BRIN — large tables with natural ordering
Examples¶
CREATE INDEX idx_email ON users (email);
CREATE INDEX CONCURRENTLY idx_date ON orders (created_at);
CREATE INDEX idx_composite ON orders (user_id, created_at DESC);
CREATE INDEX idx_partial ON orders (created_at) WHERE status='pending';
CREATE INDEX idx_expr ON users (lower(email));
CREATE INDEX idx_gin ON events USING GIN (data);
Analysis¶
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan=0;
- WHERE, JOIN, ORDER BY columns
- Most selective first
- Partial for subset
- CONCURRENTLY in production
Proper Indexes = Performance¶
EXPLAIN ANALYZE and targeted indexes.
postgresqlindexesperformance