An index that's barely pulling its weight

The junior course "ActiveRecord migrations: indexes" taught the syntax of where:, with examples this app didn't need. This course asks the harder mid-level question. Not "how do you write a partial index", but "how do you tell whether the index you already have is the wrong shape for how it's queried".

A real index, and the queries around it

db/schema.rb declares this for the submissions table:

create_table "submissions", force: :cascade do |t|
  # ...
  t.integer "status", default: 0, null: false
  t.boolean "success", default: false, null: false
  t.index ["challenge_id"], name: "index_submissions_on_challenge_id"
  t.index ["created_at"], name: "index_submissions_on_created_at"
  t.index ["status"], name: "index_submissions_on_status"
  t.index ["success"], name: "index_submissions_on_success"
  t.index ["user_id", "challenge_id", "status"], name: "index_submissions_on_user_challenge_status"
  t.index ["user_id", "challenge_id"], name: "index_submissions_on_user_id_and_challenge_id"
  t.index ["user_id"], name: "index_submissions_on_user_id"
end

index_submissions_on_success covers every row, indexed on one boolean column. When this course was first written, three places in the app filtered by success, and every one of them also filtered by user and challenge. On 19 September 2026 the app added a status column, an enum with pending, passed, failed and errored, plus the three-column index_submissions_on_user_challenge_status. The per-learner queries moved to status:

# app/policies/execution/solution_access_policy.rb
::Execution::Submission
  .where(user_id: user_id, challenge_id: challenge_id, status: :failed)
  .count

# app/controllers/catalog/lessons_controller.rb
@failed_attempts = ::Execution::Submission.where(user: current_user, challenge: @challenge, status: :failed).count

# app/queries/admin/stats_query.rb
::Execution::Submission
  .where(created_at: since..)
  .group(:success)
  .count

success still exists, kept in step with status, and the admin dashboard still groups by it. But that query filters on a created_at range and counts both values, so an index on success alone doesn't help it either. No query in the app asks "which submissions succeeded, across the whole table", the one question a plain index on success is built to answer fast.

Watching the planner skip it

This course uses the success version of the per-learner question as its worked example: a boolean is the clearest case for a partial index. Lessons 5 and 7 come back to what the app chose with status.

The development submissions table is empty, far too small for Postgres to make an interesting choice. So the experiments run on a temporary table shaped like submissions before status existed: 60,000 rows, 200 users, 30 challenges, and about one success in five. It has the same two indexes, on success and on (user_id, challenge_id). Here's the failed-attempts question as it was written then:

EXPLAIN SELECT * FROM scratch_submissions
  WHERE user_id = 27 AND challenge_id = 1 AND success = false;

Bitmap Heap Scan on scratch_submissions  (cost=4.40..44.88 rows=9 width=29)
  Recheck Cond: ((user_id = 27) AND (challenge_id = 1))
  Filter: (NOT success)
  ->  Bitmap Index Scan on scratch_idx_user_challenge  (cost=0.00..4.40 rows=11 width=0)
        Index Cond: ((user_id = 27) AND (challenge_id = 1))

The boolean-only index never appears in this plan. The planner reached for the (user_id, challenge_id) index, which narrows 60,000 rows to about ten before success comes into it. It then checked success as a plain Filter on what came back. An index on a column with two possible values can at best narrow a search to one side of the split. Against an index that narrows it to a handful of rows, it loses whenever a query combines it with anything more selective.

Key takeaways

• index_submissions_on_success is a plain index on a boolean column, and it's still in the schema.
• The app's per-learner queries now filter on status with user and challenge, served by the three-column index. The one query still reading success groups a date range by it, so no query uses the boolean index the way it was built to be used.
• On a 60,000-row scratch table, the failed-attempts query never touched the boolean-only index. It used the (user_id, challenge_id) index and filtered success afterwards.
• An index on a two-valued column can only split a table in two, so it loses to a more selective index whenever a query combines it with one.