A slow API response does not automatically mean the database needs another index. The endpoint might be waiting on a downstream service, returning too many rows, or issuing one extra query for every item. First isolate the time spent in the database and capture the actual query shape.
Keep the workload representative
A query that is fast on a tiny fixture may behave differently on a large tenant. Use realistic row counts and parameter values. Record response latency, query duration, and returned rows so you can compare changes against the same workload.
A useful first investigation is an orders list filtered by tenant and sorted by creation time. Its access pattern matters more than the table name.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 'example-tenant'
ORDER BY created_at DESC
LIMIT 50;EXPLAIN ANALYZE actually executes the statement. Use a safe environment for experiments, especially with writes. Plain EXPLAIN shows estimates without running the query.
Look for the expensive work
Compare estimated and actual row counts, look at loop counts, and follow where filtering and sorting occur. A large mismatch can make stale statistics or uneven data distribution worth investigating. Buffer information helps show the page activity behind a plan.
A sequential scan is not automatically bad: reading much of a small table can be cheaper than using an index. Likewise, an index scan is not automatically fast. The useful question is how much work the plan performs for this request.
Match the index to the access pattern
For the example above, a composite index on (tenant_id, created_at DESC) is a candidate to test. It combines the equality filter with the requested order. Check the resulting plan and elapsed time rather than assuming that its existence guarantees use.
CREATE INDEX orders_tenant_created_idx
ON orders (tenant_id, created_at DESC);This is an illustrative schema, not a production migration instruction. Every extra index consumes storage and adds write work. Compare the read benefit against the table’s insert and update workload before keeping it.
Check the application around the query
- Count queries per request to catch repeated per-row lookups.
- Select the fields the interface needs rather than a full record by habit.
- Bound result sizes and investigate deep pagination separately.
- Recheck both common and unusually large tenants after a change.
The plan is evidence about a workload, not a score for a query.
A useful optimization report states the query, representative inputs, before-and-after measurements, and any added write cost. That makes the improvement reviewable and easier to revisit as the data grows.