How do you check whether an index helps a query?
Answer out loud or in your head first, then compare.
Answer
I look at the query, the data distribution and the execution plan. In PostgreSQL, EXPLAIN describes the chosen way of working, and EXPLAIN ANALYZE also runs the statement and shows measurements. That's why I don't blindly run ANALYZE for an operation that changes production data.
If I often fetch one tenant's jobs by status and time, I choose an index for those filters and that ordering. I don't assume an index on every column gives the same result. Very distant pages fetched with a large offset can be expensive; sometimes it's better to continue from the last seen key. In the end I compare the real plan and timing, not just whether an index exists.
Why it matters: An extension of database basics, useful for reports and large tables.
Possible follow-ups: How do you settle the order when several records have the same time?
How did it go? Be honest — “Not yet” puts the question on your review list.
Start with
- D-P01 · What is HTTP, and what do a request and a response look like? — Backend, data and system design
- D-P10 · What is a database transaction? — Backend, data and system design
- D-P13 · What is a message queue, and why do we use one? — Backend, data and system design
Further reading
- PostgreSQL: Transaction Isolation(opens in a new tab)Docs; accessed 22.10.2026. Isolation, locks and serialization anomalies; a good basis for working with an ORM.