The Query Lifecycle: From Your Application Code to Disk and Back

Most engineers interact with a database the same way they interact with a vending machine. You put something in, you get something out, and as long as it works you do not think about the mechanism. The problem with treating a database as a vending machine is that when something goes wrong — when the query that ran in 8ms yesterday runs in 4 seconds today — you have no mental model to reason from. You start guessing: add an index, rewrite the query, restart the connection pool, hope something changes.

The engineers who diagnose database performance problems quickly are not the ones with better intuition. They are the ones who know exactly what happens between the moment the application calls db.query() and the moment results come back. With that knowledge, a slow query is not a mystery — it is a broken stage in a known pipeline, and the diagnostic tools (EXPLAIN ANALYZE, executionStats, pg_stat_statements) are not black boxes but readouts of that pipeline's internal state.

This article walks through the complete query lifecycle in both PostgreSQL and MongoDB. Not at a conceptual level — at the level where you understand what data structure is being built, what decision is being made, and what can go wrong at each stage. By the end, you will be able to read a query plan not as a collection of unfamiliar terms but as a precise description of how your database is going to physically retrieve your data.

The Pipeline Overview

Before going deep, it helps to see the full pipeline at once. In both systems, a query travels through four broad stages:

Application
    │
    │  SQL string / BSON document
    ▼
┌─────────────────┐
│    PARSE        │  Is this syntactically valid? Build an AST.
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│   REWRITE       │  Transform the AST: views, rules, CTEs       (PostgreSQL)
│   / VALIDATE    │  Validate field names, types, permissions     (MongoDB)
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│     PLAN        │  Which indexes? Which join algorithm?
│                 │  What order to access tables/collections?
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│    EXECUTE      │  Actually read pages, evaluate predicates,
│                 │  sort, aggregate, return rows/documents
└────────┬────────┘
         │
         ▼
Application receives results

The critical insight is that most performance problems originate in the PLAN stage but manifest in the EXECUTE stage. The planner made a decision — which index to use, which join algorithm to apply, in what order to access tables — and that decision turned out to be wrong for the actual data. Understanding why the planner made the decision it did is what lets you fix it.

Stage 1: Parsing

PostgreSQL: Lexer, Parser, and the Parse Tree

When PostgreSQL receives a query string, the first thing it does is tokenise it. The lexer (scan.l, generated by flex) reads the raw SQL character by character, converting it into a stream of tokens: keywords (SELECT, WHERE, JOIN), identifiers (table names, column names), literals (string values, numbers), and operators.

The parser (gram.y, generated by bison) takes the token stream and constructs a parse tree — an Abstract Syntax Tree (AST) that represents the grammatical structure of the query. The parse tree is purely syntactic: it knows that SELECT a FROM t WHERE b = 1 is a select statement with a target list, a from clause, and a where clause, but it does not yet know whether t is a real table or whether column a exists.

Parse tree for: SELECT name, salary FROM employees WHERE department = 'Engineering'

SelectStmt
├── targetList
│   ├── ResTarget: name
│   └── ResTarget: salary
├── fromClause
│   └── RangeVar: employees
└── whereClause
    └── A_Expr (=)
        ├── A_Const: 'Engineering'
        └── ColumnRef: department

Parse errors happen here — if you have a syntax error in your SQL, PostgreSQL reports it at this stage before touching the catalog or any data.

PostgreSQL caches parse trees for prepared statements. When you use parameterised queries ($1, $2), the parse tree is built once and reused for every execution with different parameter values. This is one of the reasons parameterised queries are faster than string-interpolated queries beyond just being safer — the parse step is skipped on repeated executions.

MongoDB: BSON Document Parsing and Command Validation

MongoDB does not receive SQL strings. It receives command documents — BSON-encoded documents that describe the operation. A find command looks like:

{
  find: "employees",
  filter: { department: "Engineering" },
  projection: { name: 1, salary: 1, _id: 0 },
  sort: { salary: -1 },
  limit: 20
}

The driver serialises this to BSON and sends it over the wire. MongoDB's command processing layer deserialises the BSON, validates the command structure (is find a valid command? does it have the required filter field?), and constructs an internal CanonicalQuery object — MongoDB's equivalent of PostgreSQL's parse tree.

MongoDB's parsing is structurally simpler than PostgreSQL's because there is no grammar to parse — the query is already structured as a document. The validation step, however, does more upfront work: it checks field name validity, resolves collection names, and validates operator usage ($gt is valid, $gtt is not). Errors at this stage are command validation errors, not execution errors.

One consequence of the document-based query model: there is no concept of SQL injection in MongoDB's native query interface, because queries are structured documents, not strings. Injection is still possible when queries are constructed by string interpolation before being parsed as JSON — a pattern that should never exist in production code, but occasionally does.

Stage 2: Rewriting and Semantic Analysis

PostgreSQL: The Analyzer and the Rewriter

After parsing, PostgreSQL runs the analyzer (analyze.c). This is where the parse tree becomes semantically meaningful. The analyzer:

• Resolves table names against pg_class (the system catalog of tables, indexes, and sequences)
• Resolves column names against pg_attribute
• Checks permissions against pg_acl
• Resolves type information — is salary an integer or a numeric? What does = mean for this type?
• Transforms the parse tree into a query tree (also called a Query node) that contains resolved object identifiers rather than names

The output is a query tree where every table reference is an OID (object identifier from the catalog), every column reference is an attribute number, and every operator is resolved to a specific function implementation.

After analysis, the rewriter (rewriteHandler.c) applies transformation rules. Two categories of rewrites happen here:

View expansion: If any table reference in the query is actually a view, the rewriter replaces it with the view's defining query. From this point on, the planner and executor see no distinction between a table and a view — a view is transparently inlined.

-- You write:
SELECT * FROM active_employees WHERE department = 'Engineering';

-- active_employees is a view: SELECT * FROM employees WHERE status = 'active'
-- After rewriting, the planner sees:
SELECT * FROM employees WHERE status = 'active' AND department = 'Engineering';

This is why views in PostgreSQL are not a performance abstraction barrier — they are expanded before planning, and the planner can apply its full optimisation logic to the combined query.

Rule application: PostgreSQL has a rule system (separate from triggers) that can rewrite queries based on patterns. Rules are rarely used in modern PostgreSQL code, but they are the mechanism underlying updatable views.

MongoDB: Plan Cache Lookup and Canonicalisation

MongoDB does not have a rewriter in the PostgreSQL sense — there are no views with the same transparent expansion semantics. MongoDB views are read-only and implemented as stored aggregation pipelines, not as inlined query text. A query against a MongoDB view goes through the view's pipeline, not a rewritten query.

What MongoDB does have at this stage is query canonicalisation — the process of normalising a query into a canonical form for plan cache lookup. Literal values are stripped and replaced with type placeholders. The canonical form of:

{ userId: 12345, createdAt: { $gt: ISODate("2024-01-01") } }

is something like:

{ userId: <int>, createdAt: { $gt: <date> } }

This canonical form is the cache key. Two queries with the same field names, operators, and field value types will hit the same cache entry regardless of the specific values. This is how MongoDB avoids re-planning every query — by recognising that queries with the same shape but different values likely have the same optimal plan.

The plan cache lookup happens here. If a winning plan is cached for this canonical query shape, execution jumps directly to Stage 4 with the cached plan. If not, the query proceeds to Stage 3 (planning).

Stage 3: Planning

The planning stage is where the most consequential decisions are made. The planner answers: given what I know about the data, what is the fastest way to retrieve the requested rows?

This is also where things most commonly go wrong in ways that are hard to diagnose without understanding the internals.

PostgreSQL: The Cost-Based Optimiser

PostgreSQL's planner is a cost-based optimiser. It enumerates possible execution plans, estimates the cost of each, and chooses the one with the lowest estimated cost. "Cost" is an abstract unit that combines estimated I/O operations and CPU operations.

The planner proceeds through three phases:

1. Scan path generation: For each table in the query, the planner considers every way it could be accessed:

• Sequential scan: read every page of the table
• Index scan: traverse a B-Tree (or other index) and fetch matching heap pages
• Index-only scan: serve results from the index without touching the heap
• Bitmap index scan: collect matching ctids from one or more indexes, sort them, then fetch heap pages in order

2. Join ordering and algorithm selection: If the query involves multiple tables, the planner considers the order in which to join them and which algorithm to use for each join. For queries with up to join_collapse_limit tables (default 8), the planner considers all possible join orderings. Beyond that, it switches to a genetic algorithm (GEQO) to avoid exponential planning time.

Join algorithms considered:

• Nested Loop: for each row in the outer relation, probe the inner relation. Efficient when the inner relation has an index on the join key and the outer relation is small.
• Hash Join: build a hash table from the smaller relation, probe it with each row from the larger. Efficient for large unsorted joins. The hash table is built in work_mem — if it exceeds work_mem, it spills to disk (a hash join spill).
• Merge Join: both relations must be sorted on the join key; then they are merged. Efficient when both sides are already sorted (e.g., both have an index on the join key).

3. Cost estimation: For each candidate plan, the planner estimates cost using:

• Row count estimates from pg_statistic (statistics collected by ANALYZE)
• Page count from pg_class.relpages
• Cost constants: seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_operator_cost

The row count estimate is the single most important input to the cost model, and it is also the most common source of bad plans. If the planner estimates that a predicate will match 100 rows when it actually matches 100,000, it will choose a plan optimised for 100 rows — likely an index scan — that performs catastrophically for 100,000 rows.

-- See the planner's estimates vs reality
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT e.name, d.name AS dept_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 80000
  AND d.location = 'Vancouver';

Reading the output:

Hash Join  (cost=125.43..892.17 rows=234 width=48)
           (actual time=2.341..18.432 rows=1847 loops=1)
  Hash Cond: (e.department_id = d.id)
  Buffers: shared hit=312 read=89
  ->  Seq Scan on employees e  (cost=0.00..654.21 rows=1203 width=36)
                                (actual time=0.021..8.234 rows=9423 loops=1)
        Filter: (salary > 80000)
        Rows Removed by Filter: 41577
  ->  Hash  (cost=98.12..98.12 rows=22 width=20)
            (actual time=1.234..1.234 rows=3 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on departments d  (cost=0.00..98.12 rows=22 width=20)
                                       (actual time=0.012..1.198 rows=3 loops=1)
              Filter: (location = 'Vancouver')
              Rows Removed by Filter: 47

The critical observation: the planner estimated rows=234 for the Hash Join but the actual result was rows=1847 — an 8× underestimate. The planner estimated rows=1203 for the employees scan but actual was rows=9423. And the departments scan estimated rows=22 but only 3 rows survived the filter — 47 removed.

This is a statistics problem. The planner does not know that 'Vancouver' matches only 3 out of 50 departments, because either the statistics are stale or the data distribution is too skewed for the default statistics target to capture accurately.

-- Increase statistics target for skewed columns
ALTER TABLE departments ALTER COLUMN location SET STATISTICS 500;
ANALYZE departments;

-- Or force immediate re-analysis
ANALYZE employees, departments;

Reading BUFFERS: The Buffers: shared hit=312 read=89 line is one of the most useful pieces of information in an EXPLAIN ANALYZE output. shared hit means pages served from PostgreSQL's shared buffer cache. read means pages read from disk (or OS page cache). A high ratio of read to hit on a table that should be cached indicates buffer cache pressure — the working set does not fit in shared_buffers, and you are paying disk I/O on what should be cached data.

The Planner's Secret Weapons: Statistics

pg_statistic stores several types of statistics per column:

• null_frac: fraction of rows where the value is NULL
• avg_width: average byte width of the column value
• n_distinct: estimated number of distinct values (negative values mean fraction of total rows)
• most_common_vals / most_common_freqs: the most common values and their frequencies — the MCV list
• histogram_bounds: bucket boundaries for estimating range predicate selectivity

For a column with highly skewed distribution — say, a status column where 99% of rows are 'completed' and 1% are 'pending' — the MCV list captures the skew. The planner knows that WHERE status = 'completed' matches 99% of rows (use sequential scan) while WHERE status = 'pending' matches 1% (use index scan).

For columns with many distinct values (user IDs, timestamps), the histogram buckets estimate what fraction of rows fall in a given range. The accuracy of this estimate depends on default_statistics_target (default 100 buckets). For columns with unusual distributions that affect query plans, increasing the statistics target captures finer-grained distribution information.

-- Inspect what the planner knows about a column
SELECT
  n_distinct,
  most_common_vals,
  most_common_freqs,
  histogram_bounds
FROM pg_stats
WHERE tablename = 'employees' AND attname = 'department';

MongoDB: Race-Based Planning and the Query Planner

MongoDB's planner takes a fundamentally different approach. Rather than building a cost model from statistics, it uses trial execution — it actually runs candidate plans against a small subset of the data and picks the winner.

When a query arrives with no cached plan:

1. The planner identifies candidate indexes — indexes where at least the first field matches a predicate in the query
2. For each candidate, it constructs a query solution tree — a physical execution plan
3. It runs all candidates simultaneously, interleaved, for up to 101 documents or 1000 work units
4. The plan that reaches 101 results (or exhausts its input) first wins
5. The winning plan is stored in the plan cache for this query shape

// See all candidate plans and why one won
db.employees.find(
  { department: "Engineering", salary: { $gt: 80000 } }
).explain("allPlansExecution")

The allPlansExecution verbosity shows:

• winningPlan: the plan that was selected
• rejectedPlans: the plans that lost the trial
• For each plan: nReturned, executionTimeMillisEstimate, totalKeysExamined, totalDocsExamined

The race-based approach has a fundamental limitation: it optimises for the first 101 documents, not for the full result set. A plan that finds 101 matching documents quickly during the trial may be slower overall if it requires examining many more documents to complete the full query. For queries that return large result sets, the winning plan from a 101-document trial can be significantly suboptimal.

Forcing a plan with hint: MongoDB's planner does not support query hints in the same flexible way PostgreSQL does (SET enable_hashjoin = off, SET enable_seqscan = off). MongoDB's hint() allows you to force a specific index:

// Force use of a specific index
db.employees.find({ department: "Engineering", salary: { $gt: 80000 } })
  .hint({ department: 1, salary: 1 })
  .explain("executionStats")

This is a blunt instrument — you are forcing the planner to use exactly that index regardless of whether it is appropriate. Use it for diagnosis, not permanently in production code, unless you have confirmed it is consistently optimal.

Stage 4: Execution

The executor takes the plan produced by the planner and runs it. Understanding how executors work changes how you interpret query plan output.

PostgreSQL: The Volcano Model

PostgreSQL's executor uses the Volcano model (also called the iterator model or pipeline model). Every node in the plan tree implements three operations: Init(), GetNext(), and End(). The top node calls GetNext() on its children, which call GetNext() on their children, pulling one row at a time up through the plan tree.

Result (top)
    │ GetNext() ──────────────────────────────┐
    │                                          │
    ▼                                          │
Sort Node                                      │
    │ GetNext()                                │
    ▼                                          │
Hash Join Node                                 │ one row
    │ GetNext()        GetNext()               │ at a time
    ▼                      ▼                  │
SeqScan (employees)   Hash (departments)       │
                                               │
                                          ┌────┘
                                          │
                                    returns row
                                    to application

The important characteristic of this model: rows flow through the plan tree one at a time. No intermediate materialisation is required unless a node specifically needs all input before it can produce any output. Sort nodes and Hash Join build nodes are blocking — they must consume all input before producing output. All other nodes are streaming — they produce output as they consume input.

This is why a query with an ORDER BY on a large result set takes longer to return the first row than the same query without ORDER BY. The Sort node must accumulate all matching rows before it can return the first sorted row.

BUFFERS in EXPLAIN ANALYZE: The (BUFFERS) option reveals exactly how many 8KB pages each node read from the buffer cache or disk. This is one of the most powerful diagnostic tools in PostgreSQL — it tells you not just whether the plan is fast or slow, but exactly where the I/O is being spent.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE user_id = 42 AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC;

-- Sample output fragment:
-- Index Scan using idx_events_user_date on events
--   (cost=0.56..892.34 rows=423 width=128)
--   (actual time=0.234..45.123 rows=892 loops=1)
--   Index Cond: ((user_id = 42) AND (created_at > ...))
--   Buffers: shared hit=234 read=658

shared hit=234 read=658 means 234 pages came from the buffer cache, 658 came from disk. For a query that runs frequently, 658 disk reads per execution is a problem — either the index is not selective enough (too many heap fetches), or the table data is too large to stay cached, or shared_buffers is too small for the working set.

work_mem and sort/hash spills: PostgreSQL allocates work_mem per sort operation and per hash table in a Hash Join. If a sort or hash table exceeds work_mem, it spills to disk — a temporary file written and read back, adding significant latency.

-- Detect hash join spills and sort spills in EXPLAIN output
-- Look for: "Batches: N" where N > 1 (hash join spill)
-- Look for: "Sort Method: external merge" (sort spill)

EXPLAIN (ANALYZE, BUFFERS)
SELECT e.*, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- Hash  (cost=...)
--   Buckets: 1024  Batches: 4  Memory Usage: 4096kB
--                  ^^^^^^^^
--                  Batches > 1 means the hash table spilled to disk

Spills are a common source of unexpected query latency that does not show up in index analysis. A query with good indexes but insufficient work_mem can be many times slower than the same query with adequate memory, because it is doing multiple passes over disk-resident temporary files.

Setting work_mem too high globally is dangerous — it is allocated per sort/hash operation per query, and with many concurrent connections each running a complex query, you can exhaust server memory quickly. The correct approach is to set work_mem conservatively globally and increase it per session for specific heavy queries:

SET work_mem = '256MB';  -- for this session only
SELECT * FROM large_table ORDER BY complex_expression;
RESET work_mem;

MongoDB: Stage-Based Execution

MongoDB's executor is also pipeline-based, but described differently. An execution plan is a tree of stages, each of which pulls documents from its child stage, applies some transformation, and passes results to its parent.

// Execution plan stages for:
// db.employees.find({ dept: "Eng", salary: { $gt: 80000 } }).sort({ name: 1 })

{
  stage: "SORT",                    // top: sort by name
  inputStage: {
    stage: "FETCH",                 // fetch full documents by RecordId
    inputStage: {
      stage: "IXSCAN",              // scan index to find matching RecordIds
      keyPattern: { dept: 1, salary: 1 },
      indexName: "dept_salary_idx",
      direction: "forward",
      indexBounds: {
        dept: ["[\"Eng\", \"Eng\"]"],
        salary: ["(80000, inf.0]"]
      }
    }
  }
}

The key stages to recognise:

• COLLSCAN: collection scan — reading every document. The MongoDB equivalent of a PostgreSQL sequential scan. Always investigate when you see this on a large collection.
• IXSCAN: index scan — traversing an index B-Tree
• FETCH: fetching the full document from the collection using a RecordId from an IXSCAN. This is the heap fetch equivalent — the step you eliminate with a covered query.
• SORT: in-memory sort. If you see this with a large nReturned, check whether an index can serve the sort.
• SORT_MERGE: merge of pre-sorted inputs — more efficient than SORT but requires index-sorted input
• OR: union of results from multiple index scans (for $or queries)

executionStats in depth:

db.employees.find(
  { department: "Engineering", salary: { $gt: 80000 } }
).explain("executionStats")

// Key fields:
{
  executionStats: {
    nReturned: 47,
    executionTimeMillis: 234,
    totalKeysExamined: 2341,    // index entries scanned
    totalDocsExamined: 2341,    // documents fetched
    executionStages: { ... }
  }
}

The ratio totalKeysExamined / nReturned is your index efficiency ratio. In this example: 2341 keys examined to return 47 documents — a ratio of ~50:1. The index is finding 50 candidates for every 1 document that survives all predicates. This means either the index is poorly selective, or there are predicates the index cannot evaluate (requiring a document fetch and in-memory filter for each candidate).

A healthy IXSCAN + FETCH has totalKeysExamined close to totalDocsExamined close to nReturned. Large gaps at any point indicate inefficiency.

Blocking stages in MongoDB: Like PostgreSQL's Sort node, MongoDB's SORT stage is blocking — it must accumulate all input documents before returning the first sorted result. MongoDB enforces a memory limit on in-memory sorts: 100MB by default (configurable with allowDiskUse: true for aggregations). A sort that exceeds this limit will fail without allowDiskUse, or spill to disk with it.

// Allow disk use for large aggregation sorts
db.events.aggregate(
  [{ $sort: { createdAt: -1 } }, { $limit: 1000 }],
  { allowDiskUse: true }
)

Stage 5: Result Transport

The final stage is often ignored in performance analysis but can be significant for large result sets.

PostgreSQL: The Wire Protocol

PostgreSQL uses a custom binary protocol (the frontend/backend protocol) to send results to clients. For a query returning many rows, PostgreSQL serialises each row as a DataRow message and streams them to the client. The client driver buffers these rows.

The interaction between the server's streaming and the client's buffering is where large result sets can cause memory problems on the client side. A naive SELECT * FROM large_table returns all rows into client memory. In application code, this manifests as memory spikes that look like application bugs but are actually the database client eagerly loading a full result set.

The fix is server-side cursors:

-- Declare a cursor — PostgreSQL holds the result set server-side
BEGIN;
DECLARE my_cursor CURSOR FOR
  SELECT * FROM large_events_table WHERE user_id = 42;

-- Fetch in batches — only 1000 rows in client memory at a time
FETCH 1000 FROM my_cursor;
FETCH 1000 FROM my_cursor;
-- ...
CLOSE my_cursor;
COMMIT;

In application frameworks, this is usually exposed as streaming query APIs. In Go's database/sql, rows.Next() fetches lazily. In Java's JDBC, setting fetchSize on the statement controls the batch size. Not using these correctly is one of the most common causes of OOM errors in services that run bulk processing against PostgreSQL.

MongoDB: Cursors and the getMore Command

MongoDB's result transport is cursor-based by default. A find() command returns a cursor ID and the first batch of documents (default 101 documents or 16MB, whichever comes first). The client must issue getMore commands with the cursor ID to retrieve subsequent batches.

// The driver handles this automatically, but understanding it matters
// First batch: cursor returned with cursorId
{ cursor: { id: Long("1234567890"), firstBatch: [...101 docs...] } }

// Subsequent batches via getMore
{ getMore: Long("1234567890"), collection: "employees", batchSize: 200 }

Cursors are held open server-side between getMore calls. An idle cursor that is never closed (because the application code fails to exhaust or explicitly close it) holds server-side resources — memory for the cursor state, and potentially a query plan. MongoDB has a cursor timeout (10 minutes by default) after which idle cursors are automatically closed, but in high-throughput systems, leaked cursors can accumulate before the timeout triggers.

In production, monitor db.serverStatus().metrics.cursor.open.total for unexpected cursor accumulation. A growing count of open cursors that does not correspond to active queries is a cursor leak.

Putting It Together: A Systematic Diagnostic Workflow

Understanding the pipeline gives you a systematic approach to any query performance problem. Here is the workflow I apply in production:

Step 1: Capture the actual execution

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) /* your query */;

-- MongoDB
db.collection.find({ /* filter */ }).explain("executionStats")

Never diagnose from EXPLAIN alone (without ANALYZE). The planner's estimates are the hypothesis; the actual execution stats are the evidence.

Step 2: Find the expensive node

In PostgreSQL, look for the node with the highest actual time — not cost, which is an estimate. The actual time is measured wall-clock time. Also look for the largest gap between estimated rows and actual rows.

In MongoDB, look at totalKeysExamined / nReturned and totalDocsExamined / nReturned. Look for COLLSCAN on large collections.

Step 3: Identify the root cause category

Step 4: Verify the fix before deploying

Test the plan change in a dev environment with production-scale data. Statistics-dependent plan changes that look correct on small datasets often behave differently at scale. The planner's decisions are data-dependent — a plan that is optimal for 10,000 rows may not be optimal for 10 million.

Conclusion

The query lifecycle is not a black box. It is a pipeline with defined stages, each with observable state and predictable failure modes. Parsing fails on syntax errors. Analysis fails on missing tables or columns. Planning goes wrong when statistics are stale or selectivity estimates are wrong. Execution goes wrong when memory is insufficient, indexes are missing, or the wrong join algorithm is chosen. Result transport goes wrong when large result sets overwhelm client memory.

EXPLAIN ANALYZE and explain("executionStats") are not magical debugging tools. They are readouts of this pipeline's internal state — and once you know what each field is measuring, they are straightforward to interpret.

The next article in this series goes into transactions and ACID: what isolation levels actually mean at the storage level, why READ COMMITTED and REPEATABLE READ behave differently in ways that matter for application correctness, and how PostgreSQL and MongoDB handle the same concurrency problems differently.