Add indexes from the slow query

An index is justified by a query you have seen, not by a feeling that a column looks important. The slow log is the specification.

Read the plan

When a page is slow, capture the SQL and ask the database for the plan. A sequential scan on a growing table is the usual story. The index should match the WHERE and the ORDER BY you actually run, including the tenant or status that every query carries.

Write is not free

Every index slows inserts and makes vacuums heavier. Do not index every foreign key by reflex if nothing filters on it. Drop indexes that the planner has stopped using after a redesign.

Measure after

Deploy the index and watch the same page. If it is still slow, the problem may be a chatty loop of queries rather than a missing index. Fixing that is a code change, and another index will not hide it.

Index the filter you actually run

Take the query from the slow log, not from a guess. The index should match the columns in the WHERE and the ORDER BY that the page uses. An index on a column nobody filters is a write cost. An index that starts with a column you do not constrain will not be chosen for the lookup you care about.

After you add it, run the same query and read the plan. If it still scans the table, the index does not match the predicate. Shipping the migration without that check is how a slow page stays slow.

SQL · The lookup behind the request list

EXPLAIN SELECT id, reference, status
FROM portal_servicerequest
WHERE account_id = 42 AND status = 'quoted'
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX portal_request_account_status_created
ON portal_servicerequest (account_id, status, created_at DESC);

What to do this week

1. Capture one slow SQL statement from a real page.
2. Match the index to that statement’s filter and order.
3. Check the page again after the index exists.

On the site

• database administration
• custom software development