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';