Planned lessonMID-013-L02

Reading EXPLAIN ANALYZE

On This Lesson 4 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

read a plan tree and spot the problem node.

EXPLAIN shows the estimated plan; EXPLAIN (ANALYZE, BUFFERS) runs the query and adds actual rows, time, and page reads.
Read inner-most, most-indented nodes first; time is per loop, multiply by loops.
Compare rows= (estimate) with actual rows=; big gaps point to bad statistics.
Scan types: Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan.
ANALYZE really executes the statement; wrap INSERT/UPDATE/DELETE in a transaction and roll back.

Draft outline details

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4711 ORDER BY created_at DESC LIMIT 20;

Annotate node type, estimated vs actual rows, Buffers: shared hit/read, and execution time.

Why is plain EXPLAIN not enough to find a slow node?

Show answer

It shows only estimates, not actual rows or time.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.