Data Interview: Diagnosing Memory and I/O Bottlenecks with PostgreSQL 18 EXPLAIN

Prompt and context

The same query is fast in testing but slow in production. Design a PostgreSQL 18 diagnosis plan using EXPLAIN (ANALYZE, BUFFERS) and its added memory, disk, and I/O details to distinguish a bad plan, sort spill, cache miss, and storage latency.

This fits data engineering, backend, and database operations roles. PostgreSQL 18 extends EXPLAIN with memory and disk details for more nodes and shows execution buffer access details; the official EXPLAIN guide defines estimates, actual rows, loops, BUFFERS, and ANALYZE. This article is based on public documentation, not a claim about a company’s interview bank.

What the interviewer evaluates

The interviewer wants an evidence chain from the plan rather than an automatic index recommendation. A strong answer distinguishes estimation error from resource exhaustion, explains shared hit/read/dirtied/written, sort or hash memory, and I/O timing, and includes production sampling, permissions, and rollback.

Clarifying questions

• Can the query be replayed on a read replica or de-identified data?
• Is the regression average latency, tail latency, or parameter-specific plan selection?
• What execution overhead is acceptable for EXPLAIN ANALYZE in production?
• Are query fingerprints, statistics refreshes, and disk/cache metrics available for correlation?

A 30-second answer

“First preserve production parameters and the query fingerprint. On a replica, run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) and compare estimated versus actual rows, loops, buffer hit/read, and PostgreSQL 18 memory/disk fields. Large estimation error points to statistics; a sort or hash spill points to work memory, concurrency, or skew; high reads require cache and storage evidence. Validate index, statistics, or parameter changes on a replica, then canary while watching tail latency.”

Step-by-step solution

Fix the sample first. Record SQL, bound parameters, planning time, execution time, row count, and database version; different parameters can select different plans. EXPLAIN estimates without executing, while ANALYZE runs the statement. For writes, use a read replica, a read-only transaction where applicable, or a safe rollback so diagnosis does not change data.

Read the plan by comparing estimated and actual row orders of magnitude, then account for loops. BUFFERS separates shared hit, read, dirtied, and written pages. A high hit count does not prove a fast query; reads need data-volume and storage-latency context. PostgreSQL 18 adds memory and disk usage details to more nodes, helping identify the working set of sorts, window aggregates, CTEs, and Materialize nodes.

If sort or hash uses disk, determine whether work_mem is too small, concurrency is high, or data is skewed. Do not raise it globally because each operator and concurrent session consumes memory. If buffer reads and I/O latency are high, inspect cache capacity, table bloat, index selectivity, and storage. Reads with low latency may simply reflect a cold cache; confirm with stable replay and repeated samples.

Estimation errors often indicate stale statistics, missing extended statistics for correlated columns, parameter sensitivity, or changed data distribution. Test ANALYZE, extended statistics, or a query rewrite before forcing join order. An index change must be evaluated for write amplification, maintenance cost, and coverage; a better plan does not guarantee better overall throughput.

Production diagnosis needs sampling and permission boundaries. Cap EXPLAIN ANALYZE frequency and concurrency, de-identify literals and results, and aggregate fingerprints with pg_stat_statements. Correlate plans, memory, buffer, and I/O metrics with p95/p99 latency. Canary changes and immediately revert if lock waits, memory pressure, or tail latency regress.

Model answer

I would replay fixed parameters on a read replica and collect estimated/actual rows, loops, buffer hit/read/dirtied/written, and PostgreSQL 18 node memory/disk fields. Large estimation differences lead to statistics work; sort/hash spills lead to analysis of work_mem, concurrency, and skew; high reads lead to cache and storage evidence. Validate index, statistics, or parameter changes on the replica, then canary and monitor p99, lock waits, memory, and I/O.

Common mistakes

• Mistake → run EXPLAIN ANALYZE directly on the primary; Why it fails → it executes the real statement and adds load or side effects; Fix → use a replica, read-only transaction, or safe rollback.
• Mistake → add an index immediately after seeing buffer reads; Why it fails → reads may be a cold cache, statistics error, or storage latency; Fix → correlate repeated samples with I/O metrics.
• Mistake → set global work_mem very high; Why it fails → every operator and concurrent session consumes it; Fix → calculate a concurrency budget and canary session/query settings.
• Mistake → compare execution time only; Why it fails → tail latency, write amplification, and plan stability are hidden; Fix → evaluate p99, resource metrics, and regression samples together.

Follow-up questions

Why can a query be slow with a high shared hit count?

Hit means pages came from shared buffers; it does not make CPU, sorting, lock waits, or operator processing cheap. Combine loops, node memory/disk, execution-time distribution, and wait events to locate the bottleneck.

Why not set work_mem to half of physical memory?

One query can have several operators and many concurrent sessions, each consuming work_mem. A simple fraction can exceed the peak budget and trigger OOM. Calculate from concurrency, operator count, pool size, and node budget, then validate with monitoring.

When should you refresh statistics instead of rewriting SQL?

If estimates remain far from actual distribution because data changed or correlated columns lack statistics, refresh or add extended statistics first. Rewrite or index only after estimates are credible and the operator choice still fails.