One query, three plans in SQL

The same question asked three ways, with the plan after each — the difference is the lesson.

-- 1. no index: the engine reads every order, then sorts
EXPLAIN QUERY PLAN
SELECT
    id,
    total
FROM orders
WHERE customer_id = 7
ORDER BY placed_at DESC;

-- 2. an index on the filter alone: search, but the sort is still temporary
CREATE INDEX tune_customer
    ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT
    id,
    total
FROM orders
WHERE customer_id = 7
ORDER BY placed_at DESC;

-- 3. filter plus sort key: the index answers both halves
DROP INDEX tune_customer;

CREATE INDEX tune_customer_date
    ON orders (customer_id, placed_at DESC);

EXPLAIN QUERY PLAN
SELECT
    id,
    total
FROM orders
WHERE customer_id = 7
ORDER BY placed_at DESC;

How it works

1. Start from the slow version and read what the planner does with it.
2. The index changes the plan from a scan to a search; check that it did.
3. Measure the plan, not your intuition about the plan.

Keywords and builtins used here

• BY
• CREATE
• DESC
• DROP
• EXPLAIN
• FROM
• INDEX
• ON
• ORDER
• SELECT
• WHERE

The run, in numbers

Lines

34

Characters to type

631

Tokens

82

Three-star pace

95 tpm

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

Type this snippet

Step 3 of 3 in Writing it faster, step 10 of 17 in Plans & performance.

← Previous Next →