>_ IzzyLab

Hands-on task

This task is step 12 of the course “PostgreSQL: from your first database to transactions”

PostgreSQL: seq scan → index scan

medium~10 min

In the shop database, the events table (100,000 rows) has no indexes — the query SELECT * FROM events WHERE user_id = 42 goes through a Seq Scan. Create an index on user_id so the planner switches to index access, and save the query plan (EXPLAIN) to /root/plan.txt.

What you'll practise:
  • explain
  • seq scan
  • index scan
  • analyze
  • postgresql

Topic breakdown

Without an index, a query filtering on a column forces Postgres to read the whole table row by row — that's a Seq Scan, whose cost grows linearly with the table size. An index on the filter column lets the planner jump straight to the right rows; after creating it you should refresh the statistics (ANALYZE), otherwise the planner may stick with a sequential scan out of inertia. You view the chosen plan with EXPLAIN: in the output, the sequential scan should be replaced by an index one (Index Scan or Bitmap Index Scan — both are fine).

What's checked

  1. index on user_id created
  2. query plan uses the index

What it looks like