SQL from zero · Chapter 33 of 37
Reading the plan
By the end of this chapter you can ask Postgres how it runs a query, read the answer, and add the index that turns a slow count into a fast one. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Fast
- 5 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Meera forwards a complaint: the "views today" box on the creator dashboard takes ages. It counts rows in page_views, one row each time a member opened a post.
With what you know, you can only guess why it is slow: rewrite the query and time it again, or add indexes everywhere (each one slows down every insert). You cannot see whether Postgres reads the 615 rows of one day, or all 46,071 rows to find them.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
views-on-day.sql: page views on 15 August
Run setup.sql first. Count the page views on 2026-08-15, in India time (the lab's clock): from midnight that day up to, but not including, midnight on 2026-08-16.
Return one column: views.
- Task 2
day-index.sql: an index for one day's views
Create an index so that counting one day's page views (viewed_at at or after one midnight and before the next) is answered from the index, not by reading the whole page_views table.
Afterwards the plan for that count has no Seq Scan on page_views, and uses the index. Any index name.
- Task 3
explain-analyze.sql: the real plan of the day's count
Run your count from views-on-day.sql (the views on 2026-08-15) under EXPLAIN ANALYZE, so the file's output is the real plan, with actual times.
The Check creates an index on viewed_at first, and the plan must use it: compare viewed_at itself with the two midnights, with no cast or function around it.
- Task 4 · interview question
Interview: sargable.sql, a count that can use the index
This query reads every row of page_views, even though there is an index on viewed_at (the Check creates one):
SELECT count(*) AS views FROM page_views WHERE viewed_at::date = '2026-08-20';
Rewrite it so it returns the same count, in one column named views, and its plan has no Seq Scan. The file is a single SELECT.
- Task 5 · interview question
Interview: small-table.sql, prove the city index works
Run setup.sql first. It made an index users_city_idx on users (city), yet the plan for this query is a Seq Scan, because users fits on one 8 kB page:
EXPLAIN SELECT username FROM users WHERE city = 'Pune';
Prove the index can be used: in this file, turn sequential scans off for the session, then run that same EXPLAIN. The file's output is the plan, and it must name users_city_idx.
What you will learn
- EXPLAIN: the plan Postgres picks, without running the query.
- EXPLAIN ANALYZE: runs it and reports what really happened.
- Indexes: a sorted copy of a column that lets a filter jump straight to its rows.
- Index-friendly filters: compare the column itself, so the index can be used.
More in Fast
Build "Reading the plan" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.