Plan cache
- SQL course
- Expert
- Lesson 17/45
- 5 min
Learn the plan cache.
Plan cache
The plan cache stores execution plans for prepared statements. When the same query returns, the optimizer skips work and reuses the cached plan.
PREPARE get_order(text) AS
SELECT * FROM orders WHERE id = $1;
EXECUTE get_order('100');A plan cache speeds up repeats.
All columns are selected from orders. WHERE keeps only the rows where id = $1 holds.
Plan cache — details
Sometimes a parameterized plan misfires: a rare value on first run, then a common value later, makes the plan mismatch. The plan is then recompiled.
Practice Plan cache
Create a prepared statement get_order(text) that selects from orders where id = $1.
Starter code
-- plan cache
PREPARE get_order(text)Sample solution
PREPARE get_order(text) AS SELECT * FROM orders WHERE id = $1;Expected output
PREPARE
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Prepare and execute
PREPARE get_order(text) AS
SELECT * FROM orders WHERE id = ____;PREPARE get_order(text) AS
SELECT * FROM orders WHERE id = $1;Explanation: $1 is the first parameter of the prepared statement; its value is supplied when executing.
2 Plan cache: match the terms
| Term | Meaning |
|---|---|
prepared |
prepared |
cache |
cache |
plan |
plan |
reuse |
reuse |
3 What does the plan cache store?
- Trigger code
- User data
- The execution plan Correct answer
- Backup files
Explanation: The plan cache stores execution plans.