Operator-friendly PostgreSQL snippets for performance work.
Inspect a query plan
EXPLAIN ANALYZE
SELECT id, email FROM users WHERE email = 'a@b.com';
Keep stats fresh
ANALYZE users;
Simple B-tree index
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
Composite index (order matters)
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);
Partial index
CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;
Find slow queries (outline)
Enable log_min_duration_statement or use pg_stat_statements in production for evidence-based tuning.
Handy introspection
\d+ users
SELECT * FROM pg_indexes WHERE tablename = 'users';