How to investigate a slow SQL query before adding indexes

Capture a baseline someone else can reproduce

Record the query, parameters, row count, runtime, environment, and concurrent workload. Include how the application measures the delay: database execution time is different from the time a user waits for a page. Compare a narrow period with the broad period that exposes the problem.

Do not publish sensitive results or connection strings in an incident ticket. Use approved logs and representative test data. If the issue only appears at production scale, coordinate a bounded investigation with the database owner instead of improvising a live load test.

Read the execution plan before changing the design

In PostgreSQL, plain EXPLAIN shows the planned execution without executing the statement. This illustrative query requests a plan:

EXPLAIN
SELECT team_id, COUNT(*) AS open_cases
FROM service_case
WHERE status = 'open'
GROUP BY team_id;

Inspect how rows are filtered, joined, and grouped. Look for large intermediate results or unexpectedly broad scans, but do not assume every sequential scan is a defect. PostgreSQL's EXPLAIN guidance is the reference for its output. Other engines use different tools and terminology.

Treat execution-based diagnostics as real work

EXPLAIN ANALYZE actually executes the statement. That matters for load and side effects: a modifying statement can change data. Do not copy an execution-based diagnostic into production without understanding it and having appropriate authorization.

Use a suitable test environment first. Compare estimated and observed rows when an authorized execution-based test is appropriate. Large differences may point toward statistics or distribution issues, but interpret a plan in context. Capture evidence instead of declaring a cause from one conspicuous line.

Check query shape and index tradeoffs

Does the report need every selected column and row? Check for accidental many-to-many joins, repeated calculations, or an aggregation after a join multiplies facts. Correctness comes before speed: a fast query returning duplicated totals is still wrong.

Indexes can improve selected access patterns, but they consume space and add maintenance work when data changes. PostgreSQL's index documentation discusses the tradeoff. Evaluate a candidate against the filtering, joining, and ordering the workload uses. Avoid a blanket rule to index every column.

Measure the change and keep the evidence

Change one factor at a time where practical. Repeat representative parameters under comparable conditions, reconcile results, and check effects on write-heavy operations. A single warm-cache result does not describe every user's experience.

Document before-and-after evidence, remaining risks, and how to reverse an unsuccessful change. AVIZ DATA's data and systems advisory work can help bound the investigation. If the problem is mixed transactional and reporting workloads, revisit the database versus warehouse decision.