Reading plans

Inspect the slow plan with EXPLAIN.

Read the plan

In the second step you read the query plan with EXPLAIN. The plan shows whether the database uses an index or scans the whole table.

EXPLAIN SELECT * FROM orders
WHERE customer_id = 5;

The plan shows Seq Scan or Index Scan.

All columns are selected from orders. WHERE keeps only the rows where customer_id = 5 holds.

Read the plan — details

If the plan shows Seq Scan, no index is used. Index Scan means an index is in play. The cost numbers show how cheap the plan is.

Exercise Read the plan

Run EXPLAIN SELECT * FROM orders WHERE customer_id = 5 on the previous orders table.

Show example solution

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT,
  amount NUMERIC,
  created_at TIMESTAMP
);
-- read plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

Test yourself

Plan command

____ SELECT * FROM orders WHERE customer_id = 5;

Show answer

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

What does Seq Scan indicate?
- An index scan
- A full table scan
- A hash join
- A merge join
Show answer

A full table scan — Seq Scan indicates a full table scan.
Reading plans: match the terms

EXPLAIN

plan
Seq Scan

full scan
Index Scan

index
cost

cost

Next lesson: Adding indexes

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies