SQ
SQL Performance
SQL query performance optimization using indexes, EXPLAIN ANALYZE, and join strategies
Details
Language / Topic
SQLCategory
Performance
Rules
balanced
- Run `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` on every slow query before adding indexes — identify `Seq Scan` nodes on large tables as the primary optimization targets.
- Create indexes on columns used in `WHERE`, `JOIN ON`, `ORDER BY`, and `GROUP BY` clauses — use `CONCURRENTLY` (`CREATE INDEX CONCURRENTLY`) to avoid table locks in production.
- Avoid functions on indexed columns in `WHERE` clauses: `WHERE DATE(created_at) = '2024-01-01'` prevents index use — use `WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'` instead.
- Use partial indexes for queries that always filter on a predicate: `CREATE INDEX idx_active_users ON users(email) WHERE active = true` is smaller and faster than a full-column index.
- Use `LIMIT` with `OFFSET` pagination only for small offsets — use keyset pagination (`WHERE id > last_seen_id ORDER BY id LIMIT n`) for large result sets as `OFFSET` scans are O(n).
- Prefer `EXISTS` over `COUNT(*) > 0` when checking for the presence of rows — `EXISTS` short-circuits on the first match.
- Use covering indexes (`CREATE INDEX ON orders(user_id) INCLUDE (status, total)`) to satisfy queries from the index alone without a heap fetch.