Rillence
Login

← All bricks

PostgreSQL · Intermediate · Free

Reading a Query Plan

Learn to read PostgreSQL query plans on a real million-row stand — EXPLAIN vs EXPLAIN ANALYZE, the scan types, and BUFFERS — so you understand what a query does instead of guessing.

Most people add an index by superstition. This course fixes that from the ground up: we stand up a real Postgres with a million orders and learn to read what the planner is actually doing. We start with the shape of a plan — cost, rows, width — then turn on EXPLAIN ANALYZE to compare the estimate with what really happened. We walk every access type the planner can choose — sequential scan, index scan, index-only scan, and the bitmap pair — each on a query built to trigger it. We finish with BUFFERS, so you can tell a genuinely cheap plan from one that only looked fast because the data was already cached. Reading the plan and running it are taught as separate moves, so each idea lands on its own.

What you'll build

  • Read a query plan top-down and find the most expensive node
  • Tell EXPLAIN (estimate) apart from EXPLAIN ANALYZE (measured)
  • Recognise Seq Scan, Index Scan, Index Only Scan and the Bitmap pair
  • Read cost, rows, width, actual time, actual rows and loops
  • Use BUFFERS to separate a cheap plan from a warm cache

Contents

  1. A database to read plans on
  2. Wait for it to be ready
  3. One command to bring it up
  4. Start the server
  5. A clean slate when you need one
  6. A short way into the database
  7. The first table
  8. The table we will actually study
  9. A small lookup table
  10. Create the tables
  11. Seed the users, repeatably
  12. Seed the products
  13. Seed a million orders
  14. Load the data
  15. Fresh statistics, single-threaded plans
  16. Confirm what we are sitting on
  17. Ask for a plan, don't run the query
  18. Read the shape
  19. Now actually run it
  20. Compare the guess with the truth
  21. When there is no index, read everything
  22. A sequential scan
  23. Index a near-unique column
  24. A query the index can answer
  25. An index scan
  26. Index a column with repeats
  27. A handful of scattered rows
  28. The bitmap pair
  29. Carry a column inside the index
  30. Ask only for what the index holds
  31. An index-only scan
  32. Ask where the pages came from
  33. Cold: read from disk
  34. Warm: served from memory
  35. Your own plan-reading tool
  36. Reading a plan in sixty seconds