When a database query gets slow, adding an index is an appealingly concrete thing to do. I would still look at the query plan first. An index on the wrong column combination can leave the original problem intact while giving every write another bit of maintenance to perform.
PostgreSQL’s EXPLAIN guide shows how the planner expects to execute a query. EXPLAIN ANALYZE adds measurements from actually running it, which is useful but deserves care: it executes the statement, including changes made by write queries. Start with a safe query and representative data rather than casually experimenting against production.
I look for where the work grows: a join producing far more rows than expected, a filter discarding most of what was read, or a sort covering a large result that the application barely uses. A sequential scan is not automatically a mistake. Sometimes reading a small table directly is the sensible plan, and forcing an index would only satisfy a prejudice.
The useful outcome is a measured improvement to the query people run. Compare before and after with the same conditions, and keep the application behaviour intact. “We added three indexes” is an implementation detail. “This screen no longer makes people wait” is the result that matters.
