Reading a query plan in SQL

The plan is the engine telling you what it is about to do. Read it before you guess.

EXPLAIN QUERY PLAN
SELECT
    c.name,
    COUNT(*) AS orders_count
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.id
WHERE c.country = 'GB'
GROUP BY c.name
ORDER BY orders_count DESC;

How it works

1. EXPLAIN QUERY PLAN prints the plan without running the query.
2. SCAN reads every row; SEARCH jumped in by index or primary key.
3. USE TEMP B-TREE FOR ORDER BY means the sort had no index to lean on.

Keywords and builtins used here

• AS
• BY
• COUNT
• DESC
• EXPLAIN
• FROM
• GROUP
• JOIN
• ON
• ORDER
• SELECT
• WHERE
• c

The run, in numbers

Lines

10

Characters to type

186

Tokens

46

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 31 seconds.

Type this snippet

Step 1 of 2 in Reading a plan, step 1 of 17 in Plans & performance.

Next →