Write down the symptom
A dashboard query is slow for one tenant but fast for another. Before changing the schema, record the SQL shape, parameter values, expected row count, and the observed response time. Also separate database execution from connection waiting, application processing, and network transfer. A database change cannot fix time spent elsewhere.
PostgreSQL EXPLAIN describes the plan chosen by the optimizer. EXPLAIN ANALYZE executes the statement and adds observed execution information. For a suitable read query in a controlled environment, compare estimated and actual row counts and examine which nodes account for the work. Cost estimates are planning units, not milliseconds.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50;Use the plan to narrow the question
Suppose the query filters by tenant and sorts the remaining orders. A useful investigation asks whether the access path matches that filter and ordering. It does not begin with a rule that every sequential scan is bad. A sequential scan can be a sensible choice when much of a table is needed.
An index proposal should name the query it supports, the expected benefit, and its operational cost. Extra indexes consume space and add write maintenance. Compare representative tenant sizes and data distributions; a tiny development fixture can hide the behavior that matters. Keep the selected fields narrow when the caller does not need the full row.
Measure the change and keep the evidence
Retain the before and after plans, relevant data size, configuration, and test conditions. Repeat measurements enough to notice cache effects, and distinguish a cold start from a warmed workload. A changed plan is evidence of a changed strategy, not automatically an improvement.
EXPLAIN ANALYZE really runs its statement. Do not casually apply it to writes or expensive production operations. Use a controlled environment and account for side effects. The engineering result should be a verified improvement for the target workload, with a clear explanation of why the change helps and what it costs.