Your dashboard query is slow because the index is in the wrong order

When a dashboard query is slow, the first instinct is usually to add an index. Sometimes that helps. Sometimes it does nothing, and the reason is almost always the same: the index exists, but its columns are in the wrong order for the query that is supposed to use it.

Start with the plan, not with a guess. Run the query with EXPLAIN ANALYZE and look for the signal that matters: a sequential scan over a large table, with most rows discarded by a filter. That tells you the database is reading everything to find the few rows you asked for. If it is already using an index, adding another one will not fix the problem — look elsewhere first.

For a composite index, the order rule is straightforward: equality columns first, then range or sort columns. Take the dashboard query every orders table eventually gets:

SELECT order_id, total, status
FROM orders
WHERE customer_id = 42
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

The index that serves this query is (customer_id, status, created_at DESC). The equality filters narrow the rows first, and the trailing sort column means the results already arrive in the order the query asked for, so no separate sort step is needed. Reverse that order and the index becomes far less useful, because of the leftmost-prefix rule: an index on (created_at, customer_id) helps a query filtering on created_at alone, but it does not help a query that only filters on customer_id.

Two refinements are worth knowing once the basics are in place. A partial index only indexes the rows you actually query — for example, only pending orders — which keeps it smaller and faster than indexing the whole table. A covering index adds extra columns with INCLUDE so the query can be answered from the index alone, without a trip back to the table for each row. Use INCLUDE for columns that only appear in the SELECT list, and composite columns for anything in WHERE or ORDER BY.

A few guardrails, because indexes are not free. Every index slows writes a little and costs storage, so an index nobody uses is pure overhead — check usage and drop the dead ones. In production, create indexes concurrently so you do not lock the table while users are working. And re-run EXPLAIN ANALYZE after you create one. An index you never verified is a hope, not a fix.

The point is not to index everything. It is to index the queries your dashboard actually runs, in the order those queries actually use them. Get that right, and the same query that scanned the whole table starts seeking straight to the 20 rows it needs.