SQL Query Performance: Indexing Checklist

Indexes are powerful but misapplied very often. Follow a disciplined flow.

1. Capture the Slow Query

• Log exact text (no prettifying).
• Record average latency + P95.
• Count executions per minute.

2. EXPLAIN / EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT ...;
Copy code

Focus on:

• Sequential scans on large tables
• Rows vs rows removed by filter
• Join strategy (hash vs nested loop)

3. Check Existing Indexes

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';
Copy code

Guard against redundant indexes increasing write cost.

4. Filter & Join Columns First

Add composite indexes in left-to-right order of selectivity.

5. Cover the Query

Include needed columns (Postgres: INCLUDE clause) to avoid heap lookups.

6. Avoid Over-Indexing

Each index adds write amplification and memory use. Measure insert/update cost after adding.

7. Partial Indexes

Useful when most rows share a default and you query the minority:

CREATE INDEX CONCURRENTLY idx_users_active ON users (last_login) WHERE active = true;
Copy code

8. Vacuum / Analyze Health

Check bloat:

SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;
Copy code

9. Re-Measure

Compare P95 before vs after. Keep a changelog of index additions.

10. Remove Unused Indexes

Track usage:

SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;
Copy code

Intentional indexing = predictable performance.