The Desi Architect
Notes / Docker Sandboxes / Database Indexing, Query Optimization & Slow Query Profiling
Docker Sandboxes · Tier 1: Building Blocks · Lab 15

Database Indexing, Query Optimization & Slow Query Profiling

Find out why a query is slow by reading the plan Postgres chose. Add an index and watch it skip the table, order results without sorting, answer from the index alone, shrink with a partial index, and then see the price every index charges on writes.

Time45 minutes
StackPostgreSQL 16Ports 15450
MemoryAbout 80 MB
Before thisLab 04SQL Basics with PostgreSQL
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Read an EXPLAIN (ANALYZE, BUFFERS) plan: the scan type, blocks touched and time.
  • Say what a Seq Scan, Index Scan, Bitmap Heap Scan and Index Only Scan are.
  • Explain why column order matters in a composite index.
  • Explain a covering index and why VACUUM affects an Index Only Scan.
  • Explain a partial index, and the write cost of every index.

Before you start

Finish Lab 04 (SQL Basics with PostgreSQL) first. You should be able to read a SELECT ... WHERE ... ORDER BY ... LIMIT. Docker Desktop must be running.