Database Management · Advanced level

Advanced level · Module 2: Performance Tuning · Lesson 1 of 4

Reading Query Plans

Understand how the planner chooses a plan and read EXPLAIN and EXPLAIN ANALYZE output.

In this lesson you will

  • Read plan nodes, costs and row estimates
  • Compare estimated and actual rows to find problems

Key concepts

query plannerEXPLAINEXPLAIN ANALYZEestimated vs actual rows

The lesson

The planner considers different ways to run a query — scans, index use, join methods — and picks the one with the lowest estimated cost, based on table statistics.

EXPLAIN shows the chosen plan as a tree of nodes such as Seq Scan, Index Scan, Hash Join and Sort. EXPLAIN ANALYZE runs the query and adds actual times and row counts.

Large differences between estimated and actual rows suggest out-of-date statistics or data the planner cannot estimate well — often the root cause of a bad plan. Read plans from the innermost nodes outward.

Step by step

  1. Run EXPLAIN on a simple and a join query.
  2. Run EXPLAIN ANALYZE and compare estimates with actual rows.
  3. Find the most expensive node.
  4. Run ANALYZE on a table and compare the plan again.
Code example

Watching an index change the query plan

The same query before and after adding an index: SQLite switches from scanning every row to searching the index.

SQLindex_plan.sql
CREATE TABLE visits (  id INTEGER PRIMARY KEY,  patient_phone TEXT NOT NULL,  visit_date TEXT NOT NULL,  reason TEXT); -- Before: how will SQLite find one patient's visits?EXPLAIN QUERY PLANSELECT visit_date, reason FROM visits WHERE patient_phone = '0912345678'; CREATE INDEX idx_visits_phone ON visits (patient_phone); -- After adding an index on the column in the WHERE clauseEXPLAIN QUERY PLANSELECT visit_date, reason FROM visits WHERE patient_phone = '0912345678';

Output

detail
-----------
SCAN visits

detail
------------------------------------------------------------
SEARCH visits USING INDEX idx_visits_phone (patient_phone=?)

How it works

Before the index, SQLite must SCAN the whole visits table, checking every row's phone number — fine for 100 rows, slow for a million. After CREATE INDEX on patient_phone, the plan becomes SEARCH … USING INDEX, jumping straight to the matching rows. EXPLAIN QUERY PLAN is SQLite's syntax; PostgreSQL uses EXPLAIN and shows Seq Scan versus Index Scan. Output produced by running the script in SQLite.

Key concepts

full table scanCREATE INDEXEXPLAIN QUERY PLANchecking that an index is used

Practice activity

Diagnose the five slow queries from the practical activity using their plans, without changing anything yet.

Expected result: A diagnosis for each query naming the expensive node and the likely cause.

Check your understanding

  1. What does EXPLAIN ANALYZE add to EXPLAIN?

    Show answer

    It runs the query and shows actual times and row counts.

  2. What does a big gap between estimated and actual rows suggest?

    Show answer

    Poor statistics or estimates, which can lead to a bad plan.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.