Summary - Optimizer

Refresh statistics with ANALYZE.

Summary - Optimizer

The optimizer: EXPLAIN builds plans, statistics and histogram set the cost, a join strategy is picked, the plan cache speeds repeats. The right plan gives the right speed.

-- EXPLAIN -> ANALYZE -> plan cache
-- nested loop / hash join / merge join

The optimizer weaves four parts.

Exercise Summary - Optimizer

Summary task: compare the join plan of orders and customers with EXPLAIN ANALYZE.

Show example solution

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

Test yourself

See plan and time

EXPLAIN ____ SELECT * FROM orders;

Show answer

EXPLAIN ANALYZE SELECT * FROM orders;

Which join suits a large equality join?
- Cross join
- Nested loop
- Seq scan
- A hash join
Show answer

A hash join — A hash join suits a large equality join.
Summary - Optimizer: match the terms

EXPLAIN

plan
ANALYZE

stats
hash join

hash
plan cache

cache

Next lesson: Scenario design

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies