One important query was taking about 2.7 minutes to run against large production tables. For a particular use-case, the query was taking unacceptable processing time. For GitLab, with millions of users, it meant slowing down processes drastically.
Diagnose the root cause and improve the query response time without bloating the schema with indexes.
Used Postgres' EXPLAIN tools to see exactly where the query was spending its time. I found the conditions that mattered most and added targeted partial index built around how the query was really being used, instead of broad, wasteful ones. Then I adjusted the filtering and indexing so the database could pull results straight from the index.
The query went from ~2.7 minutes to ~2 milliseconds, about 99.9% faster. A heavily-used part of the system became dramatically more responsive, with no change in behaviour and almost no extra overhead from the new partial index.
Key takeaways
- Read the plan before touching the schema
- EXPLAIN ANALYZE shows where time really goes
- Partial indexes beat broad indexes when queries are selective
- A single hot-path query can dominate system-wide latency
- ~99.9% latency reduction (2.7 min → 2 ms)