liveBook · Manning

EXPLAIN

As explained in Fast by Default

EXPLAIN is a database command that reports the execution plan a database expects to use for a query.

EXPLAIN reports the execution plan a database expects to use for a query. Use it when an individual query is slow, after separating that problem from excessive query counts such as N+1 fetching. The plan shows the steps used to find data, including how many rows the database expects to examine. section 7.2.2 section 7.2.4

Focus first on scan type. A sequential scan reads every row and tests it against the WHERE clause; an index scan uses an index to jump to matching rows. A sequential scan on a table with more than a few thousand rows, despite a selective filter, commonly signals a missing index. The cost can grow from milliseconds to seconds as the table grows. section 7.2.2

Read a plan as an estimate of unnecessary database work:

  • Compare rows returned with rows expected to be examined. Returning 10 rows after scanning 2 million exposes work that a suitable index could eliminate. section 7.2.2
  • Check whether each filtering column is covered. An index on only one column may not support a query filtered by two columns. section 7.2.2
  • Fix both query shape and access path when needed. Batch per-item lookups into one query rather than adding indexes while retaining an N+1 pattern. section 7.2.2
  • Run EXPLAIN on queries exceeding 100ms while tracing a realistic API request, then compare database time, application logic, response construction, connection-pool use, and the latency budget. section 7.2.4

Inventory checks across 25 items

An inventory query scans a 2-million-row table because its index covers product_id while the query filters by product_id and warehouse_id. Add an index covering both columns, then replace one inventory query per item and separate discount lookups with batched queries. After these changes, the endpoint handles 25 items in 85ms. section 7.2.2

Listing 7.2 Using EXPLAIN ANALYZE to understand query execution
EXPLAIN ANALYZE SELECT * FROM orders #A
WHERE customer_id = 12345 #B
AND created_at > '2026-01-01';

Use EXPLAIN ANALYZE on a filtered orders query to inspect how execution behaves for a customer and date constraint. section 7.2.2

How Fast by Default covers it

How deep it goes
Explains scan types, row estimates, missing indexes, and batching, without detailing database-specific plan formats.
Examples
Uses an orders filter and a 25-item inventory check on a 2-million-row table.
What it assumes
Assumes a query and timing data, then teaches how to interpret scans and row estimates.
What you can do after
Diagnose/debug slow queries by identifying excess scans, missing index coverage, and inefficient per-item fetching.
How it connects
Connects EXPLAIN to N+1 queries, indexes, query logging, connection pools, and latency budgets.

Also explained in

Compare how 4 books explain EXPLAIN →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Fast by Default. The section links point to the parts of the book it draws on.