How Do You Diagnose and Optimize a Slow PostgreSQL Query?

Prompt and Applicable Context

The orders table contains 200 million rows and sustains 3,000 inserts or status updates per second. About 2% of all orders are pending, although that share varies materially across tenants. An operations dashboard runs the following query 40 times per second. It asks for the 50 most recent pending orders for one tenant within the last 30 days:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

The table already has two single-column B-tree indexes, orders(tenant_id) and orders(created_at). Application monitoring shows that query p95 rose from 120 ms to 2.8 seconds while PostgreSQL CPU, memory, and connection counts remain below capacity. A plan captured on a production-scale replica estimates 8,000 rows but produces 420,000 rows before sorting, touches about 120,000 shared buffers, and finally applies a top-N sort to return 50 rows.

This question targets PostgreSQL 18. The table size, throughput, and plan figures are interview assumptions used to make the reasoning testable. The goal is to improve this high-frequency read while controlling index-build risk, storage, and write amplification. Sharding, caching, and hardware expansion are outside the first pass.

What the Interviewer Evaluates

The first signal is workload confirmation before SQL changes. A strong answer separates “slowest single execution” from “largest cumulative cost.” A query averaging 80 ms at 1,000 calls per second may deserve attention before an occasional five-second query. pg_stat_statements supplies calls, total execution time, and mean execution time. Application monitoring or tracing must still supply p95 and tenant-specific percentiles.

The second signal is reading an execution plan as a causal chain. Relevant evidence includes the 8,000-versus-420,000 cardinality gap, rows emitted by scan nodes, each node's loops, buffer activity, sort method, and whether a predicate appears in an index condition or a post-scan Filter. Seeing Seq Scan or seeing that an index was used does not establish whether a plan is good.

The third signal is deriving key order from the query shape. tenant_id is an equality predicate. created_at is both a range predicate and the requested ordering. status='pending' is a fixed, rare business state. A suitable index should enter one tenant's pending range, read in timestamp order, and stop as soon as 50 rows are found.

Finally, the interviewer looks for validation and rollout discipline. An index consumes storage and build I/O while adding work to inserts and status transitions. A complete answer compares plans on production-scale data, cold and warm caches, different tenant sizes, concurrent writes, and explicit rollback thresholds. One faster local execution is not sufficient evidence.

Questions to Clarify Before Answering

• Which latency measure regressed? Per-tenant p95, global p95, mean latency, and total database time imply different priorities. Establish when the regression began and whether it aligns with data growth, parameter distribution, a deployment, or statistics changes.
• What are the pending-order rate and tenant distribution? A partial index is attractive if pending remains 1% to 2% of rows. Its size advantage shrinks if half the table is pending. One average also hides skew between very large and small tenants.
• Does the query always contain the literal status='pending'? A partial index is usable only when the planner can prove that the query condition implies the index predicate. A generic status parameter may prevent that proof.
• Which columns and consistency guarantees are required? Returning large text, JSON, or ten joined tables would quickly bloat a covering index. Confirm the columns that this list endpoint actually needs.
• How write-heavy is the table, and what rollout is allowed? At 3,000 writes per second, index width and transition cost must be measured. Production may require CREATE INDEX CONCURRENTLY, together with a longer build window, extra scans, and a procedure for cleaning up an invalid index after failure.
• Can the actual plan run on a production-scale replica? EXPLAIN ANALYZE executes the statement. Even a SELECT can create material load, while data-changing statements perform their side effects. Use a replica, bounded parameters, or plain EXPLAIN first.

30-Second Answer Framework

“I would first correlate application p95 with pg_stat_statements calls, total database time, and slow tenants, while ruling out lock waits and external dependencies. Then I would run EXPLAIN (ANALYZE, BUFFERS) with representative parameters on a production-scale replica and inspect estimated versus actual rows, loops, buffers, and the sort node. Here, the two single-column indexes still produce 420,000 candidates before sorting. Because the query always targets a rare pending state, I would test a partial covering index on (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending', which can read the first 50 rows in order. If status must be parameterized, I would compare a full (tenant_id, status, created_at DESC) index. I would validate p95, buffer work, index size, and write latency across tenant sizes, cold and warm caches, and concurrent writes before building concurrently with rollback thresholds.”

Step-by-Step Deep Dive

Step 1: Prioritize from the real workload

Map the route, tenant, parameter range, and application p95 to a normalized database query. If pg_stat_statements is enabled, begin with cumulative resource consumption:

SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time finds database time accumulated through frequent calls, mean_exec_time highlights expensive individual executions, and calls shows the multiplier. The view does not expose p95 or explain why a particular tenant or parameter is slow, so retain application-side percentiles and parameter cohorts. If the latency is primarily lock waiting, connection queuing, networking, or a downstream call, changing the query plan alone will not repair end-to-end latency.

Step 2: Collect actual execution evidence safely

Inspect the shape with plain EXPLAIN first. Then, on a production-scale replica or controlled environment, run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Read upward from the deepest nodes with substantial actual time. Treat actual rows × loops as part of a node's total work. Buffers: shared read records blocks that had to be read from storage; shared hit means the blocks were already in shared buffers, but those hits still consume CPU and memory bandwidth. A disk-backed sort reports an external sort and temporary block I/O, prompting investigation of its input size and memory budget.

The estimate of 8,000 rows versus 420,000 actual rows is a 52.5-fold error. Statistics may be stale, or single-column statistics may fail to represent correlation between tenant_id and status. Run an appropriately scoped ANALYZE and measure again. If stable correlation materially affects the plan, test extended statistics for those columns. Extended statistics carry collection and planning costs, so create them only for strongly related columns that improve an important estimate.

Step 3: Derive the index from the query shape

The planner might combine the existing single-column indexes with BitmapAnd, but a bitmap result does not retain the ordering of one B-tree. It can still visit many heap pages and sort. Alternatively, the planner may choose one index and filter the other predicate afterward. Having both indexes only creates candidate paths; it does not produce a path shaped for WHERE + ORDER BY + LIMIT.

For a fixed and rare pending state, compare a partial index first:

CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);

The first index stores only pending orders and should therefore be smaller under the stated distribution. After locating the tenant equality, created_at both constrains the 30-day range and supplies descending order, allowing the scan to stop after 50 rows. id and total_cents are payload columns in INCLUDE; they do not participate in searching or ordering and merely make an index-only read possible.

The full multicolumn index suits a parameterized status or several statuses that use the same query pattern. Leading B-tree equality columns narrow the range before the time range and ordering. “Always put the most selective column first” is too crude; equality, range, ordering, and reuse across real query templates determine key order together.

Step 4: State the limits of partial and covering indexes

A partial index is usable only if the planner can prove that the query condition includes status='pending'. In a generic prepared statement written as status = $2, that parameter cannot imply the predicate for every possible value, so the planner may ignore the partial index. A dedicated operations query can preserve the literal, or the design can use the full multicolumn index. Verify the decision with the actual query template instead of inferring it from the index definition.

INCLUDE does not guarantee an Index Only Scan on every execution. PostgreSQL must still verify MVCC visibility. When a heap page lacks an all-visible bit, the scan visits the heap. Frequent inserts and status changes on a hot table make such visits more likely. If the plan still reports many Heap Fetches, compare a narrower index without payload columns. Wide indexes also increase disk and cache use and add maintenance work to every affected write.

Step 5: Validate benefit and cost

Compare before-and-after plans using the same representative parameters: very large, median, and small tenants; tenants with many pending rows and almost none; cold cache and warmed cache. Record latency distributions, actual rows, buffers, sort behavior, temporary I/O, Heap Fetches, and index size. A single elapsed time is sensitive to cache and concurrency, while plan work explains why the result changed.

Then load-test inserts and pending → paid transitions at the production ratio. A completed order removes an entry from the partial index; the full index updates its status key. Both impose write work. Acceptance criteria can require read p95 within target and no material p99 regression, a large reduction in candidate rows and shared-block work, and write p95, WAL volume, storage, and replication lag within budget.

Before rollout, confirm disk headroom and monitoring for concurrent builds. CREATE INDEX CONCURRENTLY allows inserts, updates, and deletes to continue, but it takes longer and can leave an invalid index after failure. Once built, verify that the production query template actually chooses the new path and observe a full peak cycle. If write or replication latency crosses its threshold, remove the new query path and drop the new index according to the operating procedure. Keep the old indexes until the replacement has passed a stability window and no other workload depends on them.

High-Quality Sample Answer

“I would first establish that this SQL deserves priority. Application monitoring gives me p95 and slow tenants, while pg_stat_statements gives calls, total execution time, and mean execution time. If it runs 40 times per second and ranks high in cumulative database time, I would capture the real query template and representative tenants, then run EXPLAIN (ANALYZE, BUFFERS) on a production-scale replica.

The central problem in this plan is the wide scan: the planner estimates 8,000 rows, 420,000 rows actually reach the top-N sort, and the query touches about 120,000 buffers. The single-column indexes may combine filters, but they do not directly create an ordered range for tenant_id + pending + created_at DESC. I would refresh statistics and measure again. If tenant and status correlation keeps causing the estimation error, I would test extended statistics.

Because the operations query always requests the rare pending state, I would test a partial index on (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending'. It limits the indexed population, enters one tenant's ordered time range, and can stop after 50 rows. If the application parameterizes status and queries several values, I would compare the full (tenant_id, status, created_at DESC) index. INCLUDE only enables a possible Index Only Scan; hot pages may still require heap visits, so I would inspect Heap Fetches.

Validation would cover large, medium, and small tenants, cold and warm caches, and concurrent writes. I would compare p95 and p99, candidate rows, buffers, temporary I/O, index size, WAL, write latency, and replication lag. I would build concurrently in production, confirm the real template uses the new plan, and watch a complete peak cycle. If the read gain is weak or the write path exceeds budget, I would withdraw the path and drop the new index instead of hiding an unexplained plan behind more hardware.”

Common Mistakes

• Adding an index as soon as SQL looks slow → Frequency, parameters, and wait type remain unknown, so the team may optimize a low-priority query → Correlate application percentiles, pg_stat_statements, and real parameters first.
• Treating every Seq Scan as a defect → A sequential scan may be cheaper for a small table or a query returning a large fraction of it → Compare actual rows, buffers, and total cost against alternatives.
• Checking only whether the plan says Index Scan → An index scan can still read hundreds of thousands of entries and repeatedly visit the heap → Inspect actual rows × loops, Filter, Buffers, and Heap Fetches.
• Ignoring estimated-versus-actual row gaps → Incorrect cardinality can drive poor joins, scans, and sorts → Refresh statistics and test extended statistics for stable correlated columns.
• Assuming multiple single-column indexes equal one multicolumn index → Bitmap combinations generally lose the required ordering and can visit many heap pages → Derive a key from equality, range, ordering, and LIMIT.
• Putting every returned column in INCLUDE → Index bloat reduces cache efficiency and amplifies writes → Cover only narrow columns required by a high-value query.
• Building a partial index without testing the query template → A parameterized predicate may not imply the index predicate at planning time → EXPLAIN the same prepared form used in production.
• Running EXPLAIN ANALYZE on arbitrary primary-database SQL → It executes the statement; a heavy read creates load and a write performs side effects → Use plain EXPLAIN first and obtain actual evidence on a replica or inside a controlled transaction.
• Reporting one run that fell from 2.8 seconds to a lower number → Cache, parameters, and concurrency can create an accidental win → Compare distributions, plan work, and a full peak cycle.

Follow-Up Questions and Responses

Follow-up 1: Why can a sequential scan be faster than an index scan?

When a query reads a large fraction of a table, sequential access avoids much of the random access involved in traversing an index and fetching scattered heap pages. A small table may occupy only a few pages, making a direct scan cheaper as well. Compare buffers and total elapsed time on real data rather than scoring a plan by node name.

Follow-up 2: Why are separate (tenant_id) and (created_at) indexes insufficient?

The planner can choose one index and filter afterward or combine both with BitmapAnd. A bitmap collects candidate tuple locations and does not preserve the B-tree ordering of created_at, so it commonly reads many heap pages and then sorts. The multicolumn index puts tenant equality, the time range, and ordering on one ordered access path, allowing LIMIT 50 to stop early.

Follow-up 3: Why might PostgreSQL never use the partial index?

The planner must recognize during planning that the query condition implies the index predicate. status='pending' matches directly; status=$2 cannot promise a match for every parameter. A differently written expression, changed data distribution, or a cost estimate that makes heap access look expensive can also select another path. EXPLAIN the actual prepared template, and use the full multicolumn index when status must remain generic.

Follow-up 4: What if the estimate remains wrong by 50-fold after ANALYZE?

Check sampling coverage, the per-column statistics target, and whether the data changed abruptly. If tenant_id and status are strongly correlated, single-column statistics approximate the predicates as independent. Test dependency or MCV extended statistics for that column group. Extended statistics improve estimation; they do not create a missing access path, so validate the index and query shape separately.

Follow-up 5: Reads improve, but write p95 rises. How do you decide?

Return to SLOs and total workload: quantify database time saved on reads, users affected by the write regression, and changes in WAL, storage, and replication lag. If only one rare fixed status needs acceleration, a narrow partial index may beat a full covering index. If payload columns cause bloat, remove INCLUDE and accept some heap access. When the write budget is exceeded, withdraw the index plan and seek a smaller access path through query scope, pagination guarantees, or the data model.