Loading published curriculum…

08 · SQL and JDBC / LESSON 31 OF 102

Indexes, query plans, and stable pagination

An index trades storage and write work for faster access along particular search paths.

Before this lesson: Joins, aggregation, and NULL semantics

Understand the topic

A composite index should match the filtering and ordering used by actual queries. Indexing every column increases write cost and does not guarantee useful plans. Small tables may legitimately use sequential scans.

EXPLAIN shows the planned execution. EXPLAIN ANALYZE actually runs the statement, so use care with writes. Pagination needs deterministic ordering, usually with a unique tie-breaker. Large offsets still require skipping rows; keyset pagination continues after a known key.

EXAMPLE · SQL

task-page.sql

CREATE INDEX work_items_owner_id_id ON work_items(owner_id, id);
EXPLAIN SELECT id, title FROM work_items
WHERE owner_id = 7 AND id > 100
ORDER BY id LIMIT 20;

What to expect

The query expresses keyset pagination scoped to one owner; index choice depends on data and planner estimates.

YOUR TURN

Put it into practice

  1. 01Populate a disposable table with realistic task counts.
  2. 02Compare plans before and after the index and compare offset with keyset pagination.
  3. 03Verify: Results have a stable order and you explain measured performance instead of assuming every index is used.

Pass a quiz or complete a lab to check today’s streak. Each lesson counts once per day, including when you review it later.

UNDER THE HOOD

Build the mental model

An index trades storage and write work for faster access along particular search paths.

  1. 01A composite index should match the filtering and ordering used by actual queries. Indexing every column increases write cost and does not guarantee useful plans. Small tables may legitimately use sequential scans.
  2. 02EXPLAIN shows the planned execution. EXPLAIN ANALYZE actually runs the statement, so use care with writes. Pagination needs deterministic ordering, usually with a unique tie-breaker. Large offsets still require skipping rows; keyset pagination continues after a known key.
  3. 03Trace the example: The query expresses keyset pagination scoped to one owner; index choice depends on data and planner estimates.

A mistake worth avoiding

Rows repeat between pages when titles are equal.

DEBUGGING ROOM

Something broke. Where do you look?

Rows repeat between pages when titles are equal.

Before revealing the diagnosis, write down your first investigation step.

CHECK YOUR UNDERSTANDING

Does EXPLAIN ANALYZE execute the statement?

Choose one answer

BUILD IT YOURSELF

Populate a disposable table with realistic task counts.

  1. 1.Populate a disposable table with realistic task counts.
  2. 2.Compare plans before and after the index and compare offset with keyset pagination.
  3. 3.Reproduce this failure, then repair it: Rows repeat between pages when titles are equal.

Acceptance checklist

Lab completion is your self-report. Knowledge checks do not certify project quality.

RETRIEVE IT FROM MEMORY

Does EXPLAIN ANALYZE execute the statement?

Revisit this lesson in three days after a successful check or lab.

Ready for the next step?

Results have a stable order and you explain measured performance instead of assuming every index is used.

Reading is separate from passing a knowledge check or completing a lab.

Go deeper · official references