A support employee opens a large customer's case history. The small test database responds immediately; production makes them wait. The team suggests a larger server. Before approving that expense, find out how the database locates the cases. It may be examining far more data than the screen needs.
An index is an additional lookup structure. It can locate records by selected attributes without scanning the entire table. Its value depends on the queries and the distribution of data, however. Merely having an index does not make a search cheap.
One real query is more revealing than table size
Suppose support needs a company's most recent open cases. The query includes a customer, a status, a time ordering and a result limit. An index on status alone may offer little help: there may be many open cases, with the customer's records still to be found among them.
A composite index is one candidate. The customer identifier can narrow the search while later fields support filtering and ordering. That does not justify putting every available field into one index. Field order, index type, query variants and the size of the selected population all matter. PostgreSQL documents the behaviour of multicolumn indexes.
One customer has a short history; another has years of correspondence. One employee wants unresolved cases; another exports an entire quarter. Testing a single convenient example can demonstrate an improvement that other users never receive.
A partial index may help when a frequently requested subset is small, such as unresolved cases within a much larger history. It stores only records meeting its predicate. That can reduce its size, but it only helps suitable queries, and status changes must update membership correctly.
A query plan tests the hypothesis
The database chooses an execution plan: where to read records and how to join, filter and sort them. An engineer can inspect that plan with EXPLAIN. A manager does not need to interpret every line. Ask which unnecessary work the proposed change removes.
Comparing estimated record counts with actual counts is useful. A large discrepancy can suggest stale statistics or an unusual data distribution. In that case, adding another index may miss the underlying problem. If the query returns most of a table, a sequential scan can also be a sensible choice rather than evidence of a broken database.
EXPLAIN and EXPLAIN ANALYZE are not interchangeable: the latter executes the query. Running it on a demanding operation or a statement that changes production data needs care. The execution-plan documentation explains a diagnostic tool, not a licence to experiment freely on customer records.
The application may also fetch customer details separately for every case in the list. Even a fast individual query is then repeated many times. An index can reduce each call's cost without removing the unnecessary round trips. It cannot reduce the transfer of a huge response when the screen only needs a few records, either. Trace the path from the employee's action to the finished screen; database execution is only part of the wait. Changing the loading pattern may deliver more value without another permanent structure to maintain.
Maintaining the structure has a cost
Indexes require storage and maintenance when relevant data changes. They compete for memory and disk resources and can increase logging work. A collection of indexes created for occasional reports may make bulk ingestion substantially more expensive.
But not every row change has an identical cost for every index. The details depend on the engine, index structure and changed fields. An index may also help an UPDATE or DELETE find its target rows faster. Measure the whole operation instead of assuming that every write must become slower by a fixed amount.
For the support example, test case creation as well as opening the case list. Faster browsing is not an unqualified success if incoming requests now accumulate. A different index or a separate reporting path may offer a better compromise.
Building an index is a production operation
Creating an index on a large table can take time and compete with customer traffic. A standard build may block writes; alternatives depend on the database. PostgreSQL provides CREATE INDEX CONCURRENTLY, but it has additional work and operational restrictions rather than making the change free.
The rollout needs enough storage, awareness of current load, a cancellation plan and a way to verify completion. If the build fails, check what state it left behind. A message saying “index added” does not replace monitoring latency and errors after deployment.
Keep unrelated changes out of the experiment. If a release simultaneously changes the query, the server size and several indexes, it becomes hard to tell what helped or what to undo when another operation deteriorates.
Rarely used does not necessarily mean unnecessary
Usage statistics help identify removal candidates, but an ordinary week may miss month-end reporting, seasonal activity or an incident investigation. Some indexes enforce uniqueness. A quiet read counter is not enough reason to remove them.
For each candidate, establish its purpose, the constraints it supports, the observation period and how it would be restored. Revisit the collection as the product changes: a retired screen or report can leave the database maintaining a structure that nobody needs.
A completed optimisation has a concrete outcome: an improved user workflow, measured write and storage overhead, and a safe deployment procedure. If the change did not improve its intended operation, do not retain it merely to preserve evidence that work was done. It will keep consuming resources long after the experiment is forgotten.