Stage 2 · Lesson 6 of 11
Reading query plans with EXPLAIN
shows the query plan, which is how intends to run your query. EXPLAIN ANALYZE actually runs it and reports what happened. Read the plan from the innermost step outwards.
A Seq Scan reads the whole table. An Scan or Index Only Scan uses an index. appear as Nested Loop, Join or Merge Join.
Compare the estimated rows with the actual rows. A large gap means the planner's statistics about your data are out of date, and ANALYZE refreshes them. This is how you prove an index is used, instead of hoping.
Common traptesting on a table of 50 rows: the planner rightly prefers reading the whole table there, so load realistic data before you judge a plan.
A question to check yourself, an AI practice session and 2 cheat sheet entries come with the full roadmap. Unlock
Resources for this stage
Start with· follow one of these
- CoursePaidGrokking Relational Database Design and Modeling
Design Gurus · Intermediate · $98 one-time
The modelling and normalisation decisions that stay with a codebase for years
- CourseFreeCS50's Introduction to Databases with SQL
Harvard University · Beginner
Free course from querying through schema design, indexes, transactions and scaling
Practise
Keep handy
Also worth your time
- Database Indexing Explained: B-Tree, Hash, Bitmap, R-Tree, and When to Use Each (opens in a new tab)Read
B-tree and composite indexes, plus the leftmost-prefix rule that trips people up
What sits under your queries: storage engines, transactions and isolation levels