Debug a Slow PostgreSQL Query Before Adding Another Index

A slow endpoint is not automatically a database-index problem. It may be returning too many rows, waiting for a lock, running the same query repeatedly, or spending most of its time outside the database. Start by locating the work.

Use a realistic project example: a job board listing published jobs by city and recency. The examples below are illustrative, not benchmark results or a schema prescription for every job board.

Capture the query that actually runs

Record the endpoint duration, query duration, query count, and relevant parameters. Avoid logging private search text or credentials unnecessarily. A page that executes fifty individually fast queries may have a different bottleneck from one expensive query.

Try the same parameter distribution seen in the application. A city with ten listings and a city with a million listings can produce very different workloads.

Read the plan before changing the schema

On a development copy with representative data, inspect a query such as:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, published_at
FROM jobs
WHERE published = true
  AND city = 'Pune'
ORDER BY published_at DESC, id DESC
LIMIT 20;

PostgreSQL’s EXPLAIN documentation describes plans and actual execution statistics. ANALYZE executes the statement: use care with expensive queries and especially data-changing statements. An execution plan is evidence about this query under these conditions, not a universal score.

Look for where rows are read and removed, whether a large sort occurs, and whether actual row counts differ sharply from estimates. A sequential scan can be sensible for a small table or a query returning much of it.

Form a specific index hypothesis

An illustrative candidate is:

CREATE INDEX jobs_city_recent_published_idx
ON jobs (city, published_at DESC, id DESC)
WHERE published = true;

The hypothesis is that this structure could help the city filter and requested ordering while indexing only published rows. Whether it helps depends on the real query, data distribution, and planner decisions. Consult PostgreSQL’s index documentation when choosing a design.

Compare the plan and representative latency before and after the change. Also measure the effect on inserts, updates, storage, and other important queries. An index that accelerates one endpoint still has maintenance costs.

Check pagination and payload size

If your query uses a very large OFFSET, adding an index may not fix the underlying work. For a recency-ordered feed, a cursor can express where to continue:

SELECT id, title, published_at
FROM jobs
WHERE published = true
  AND city = 'Pune'
  AND (published_at, id) < ($1, $2)
ORDER BY published_at DESC, id DESC
LIMIT 20;

This assumes non-null ordering fields and a unique ID used as a tie-breaker. The first page omits the cursor condition. Encode and validate the cursor in your API, and keep the same filters between pages.

Fetch the fields the screen needs. Large descriptions, attachments, or unused joins increase transfer and application-processing work even when the lookup is efficient.

Review the complete request

A practical investigation sequence is:

1. Reproduce the slow request with representative parameters.
2. Identify database time, query count, lock waits, and serialization time.
3. Inspect the relevant plan on a suitable environment.
4. Change one thing and compare measurable outcomes.
5. Test ordinary writes and other queries before rollout.

Keep a short record of the original symptom, hypothesis, change, and result. It helps future maintenance and gives you a concrete performance story to discuss in interviews.

Explore backend courses to practise API and database design together.