Your queries return the right answers. The last hands-on skill is making sure they stay fast as data grows. That comes down to one idea (indexes) and one tool (EXPLAIN), and you can drive both right here.
The problem indexes solve
Without help, finding rows by value means the database reads every row and checks each one. That's a sequential scan. On six rows it's instant. On six million it's slow and gets slower as you grow.
An index is a separate, sorted structure the database keeps on the side, mapping values to the rows that have them. With an index on orders.customer_id, finding "customer 1's orders" is a quick jump to the right spot instead of scanning the whole table. It's exactly like a book's index: instead of reading every page to find a word, you flip to the entry and jump straight there.
Ask Postgres how it ran your query
You don't have to guess whether a query used an index. EXPLAIN ANALYZE runs the query and shows you the plan it chose, including whether it scanned or used an index. Run this on our (tiny) orders table:
Press Run to execute your query.
Read the output bottom-up. On our six-row table you'll see a Seq Scan, which is correct here: with so few rows, scanning is genuinely cheaper than maintaining and consulting an index. Postgres is smart enough to know that. The interesting part is what happens when we add an index and ask again.
Add an index, then look again
This query creates an index on orders.customer_id, then re-runs the same EXPLAIN ANALYZE.
Press Run to execute your query.
On six rows, the scan may still win — and that's the lesson
With only a handful of rows, Postgres might still choose a sequential scan even with the index, because for tiny tables scanning is faster than the index lookup. That's not a bug; it's the planner correctly judging cost. On a table with millions of rows, the same query would switch to an Index Scan and become dramatically faster. The takeaway isn't "indexes always win," it's "the planner picks based on cost, and EXPLAIN shows you what it picked."
What indexes cost
If indexes only helped, the database would index everything automatically. It doesn't, because indexes have a price: every INSERT, UPDATE, and DELETE has to update every index on the table too. So an over-indexed table has slow writes. The skill is indexing the columns your queries actually filter and join on, and no more.
Index this
Columns you filter by (WHERE status = ...), join on (foreign keys like customer_id), or sort by frequently. These turn scans into quick lookups where it counts.
Don't bother
Columns you rarely query, tiny tables, or adding an index "just in case." Each unused index slows every write and wastes space while helping no query.
This is the same machinery the reading-query-plans deep dive and the Postgres internals deep dive go deep on. For everyday work, the loop is simple: if a query feels slow, run EXPLAIN ANALYZE, look for a sequential scan on a big table, add the index its WHERE or JOIN wants, and check again.
Now make it yours
You've got the whole shop and a real Postgres. Write whatever you like: a different question, a new index, a tricky join. Break it, fix it, explore.
Press Run to execute your query.
The fast-query loop
A query is slow when it scans a big table for a few rows. An index turns that scan into a jump. Don't index everything (writes pay for every index); index what your queries filter, join, and sort on. And never guess: EXPLAIN ANALYZE tells you exactly what Postgres did, so you tune from facts, not hunches.
That's the hands-on SQL core: read, join, summarise, write, and make it fast. Next comes the Advanced SQL module (subqueries, CTEs, and window functions) for the queries that separate comfortable users from beginners, and after that the profiles of the popular databases so you know the whole landscape and when to reach for each.
Test yourself
Questions· say the answer out loud before you open it. If you can't, the chapter isn't done.
QWhat is a sequential scan, and when is it a problem?+
Reading every row in the table and checking each one. It's fine on small tables (and often the cheapest option there), but on a large table for a query that should match only a few rows, it's slow and gets slower as the table grows. That's the case an index fixes.
QWhat does an index actually do?+
It's a separate sorted structure mapping column values to the rows that have them, so the database can jump straight to matching rows instead of scanning. Like a book's index: flip to the entry rather than read every page. It makes filters, joins, and sorts on that column fast.
QWhat does EXPLAIN ANALYZE tell you?+
It runs the query and shows the plan Postgres chose, including whether it used a sequential scan or an index scan, with real timings. It lets you confirm from facts whether your query is using an index, instead of guessing.
QWhy does Postgres sometimes ignore an index even when one exists?+
Because the planner picks the cheapest plan by cost. On a tiny table, scanning is cheaper than consulting an index, so it scans even with the index present. On a large table the same query would switch to an index scan. It's choosing correctly based on table size and statistics.
QWhy not just index every column?+
Because every index must be updated on every insert, update, and delete, so over-indexing slows writes and wastes space. Index the columns your queries filter, join, and sort on; skip the rest. Unused indexes cost you on every write while helping no query.
Comments
Loading comments…