7. The feed is slow · Your task

Your task

Save your work as migration 006_indexes.sql, plus notes on timings.

1. Measure first. Run each of these with EXPLAIN ANALYZE and write down the scan type and execution time:

  • the homepage feed (Level 5, query 2), with LIMIT 20
  • all posts by user 1, newest first
  • the comments on post 1, oldest first

2. Profile page. Add an index that makes "posts by one user, newest first" fast. Re-run and compare.

3. Post page. Do the same for "comments on one post".

4. Homepage. The feed filters on published and sorts by created_at. Add an index that lets Postgres read the newest 20 published posts without sorting the whole table. Re-run and compare.

5. Check your work. \di lists every index. Find the ones you didn't create and say where each came from.

6. Find the limit. Run the Level 5 title search for '%number 4242%' with EXPLAIN ANALYZE. Try to speed it up with an index on title. What happens?

7. Clean up. Drop the index from step 6.