Reading a Query Plan

Reading a Query Plan at 30,000 Feet

The one idea: A query plan is the database explaining how it intends to do the work, and you only need to spot three warning signs in it. You're not here to fix slow queries — just to recognize the problem, describe it precisely, and hand it to an engineer.

⚠️ The trap: The plan says "Seq Scan," so the query is inefficient and somebody needs to add an index. That feels right and it's wrong. **A sequential scan is perfectly healthy on a small table or when the query genuinely needs most of the rows; the real red flag is a full scan of a huge table to fetch just a handful of rows.**

You will never optimize a query, and this lesson won't try to teach you to. But you will be in the room when a dashboard takes four minutes to load, or when the data team says "that query is going to melt the warehouse," and you'll want to know whether that's true, whose problem it is, and how urgent. A query plan is the database explaining, before or after running a query, exactly how it intends to do the work. You need to recognize three patterns in it — that's the entire syllabus.

What EXPLAIN shows

Put EXPLAIN in front of any query and PostgreSQL returns its execution plan instead of results — the sequence of operations it will use, with estimates:

EXPLAIN SELECT * FROM orders WHERE customer_id = 4821;

Seq Scan on orders  (cost=0.00..48291.00 rows=12 width=64)
  Filter: (customer_id = 4821)

Reading right to left in that parenthetical: width…