PPalabas APIJava field course

World 2A / Quest 2A.4

Indexes and EXPLAIN

Add indexes for known reads and capture the planner story without forcing a fake win.

Concept

Indexes are not merit badges. They should match the reads the API will perform, and EXPLAIN should tell the truth about whether the planner uses them at the current data size.

Task

  1. Add indexes for cinema bounding box, screens by cinema, and showtimes by screen/start.
  2. Seed enough showtime rows that planner behavior can be discussed.
  3. Capture EXPLAIN (ANALYZE, BUFFERS) for the cinema/day showtime query.

Run

docker compose up -d postgres
./gradlew flywayMigrate
psql postgresql://palabas:palabas@localhost:5432/palabas -f docs/transcripts/phase-2a-explain.sql

Expected Result

  • The transcript explains the chosen plan instead of blindly celebrating an index.

Common Traps

  • Adding indexes without naming the query they serve.
  • Forcing an index hit on tiny data.
  • Forgetting that showtimes by cinema travels through screens.

Hint Ladder

Hint 1

The query begins with cinema_id but showtimes only know screen_id.

Hint 2

A good transcript can say the planner chose a seq scan and why.

Hint 3

The artifact is the reasoning, not just the plan text.

Solution

See docs/transcripts/phase-2a-explain.sql and phase-2a-explain.txt.