Fix the index review for a write-heavy table

Review the AI's codeEasy12 min

Your task

To speed up one dashboard query, an AI added five single-column indexes to an events table that takes 30,000 inserts a second. Every index adds work to every write, and most of the five were on columns with a handful of values, which the planner would not use anyway. Your team now runs proposed indexes through a review that keeps only the useful ones and builds them without blocking writes.

An assistant wrote build_index_plan(table, index_lines, row_count). It is in the editor. It has a bug. Find it and fix it.

Input and output:

  • table: the table name.
  • index_lines: the proposed indexes, each written index_name|column|distinct_values, where distinct_values is how many different values the column holds.
  • row_count: how many rows the table holds.
  • Return one statement per index kept, in proposal order: CREATE INDEX CONCURRENTLY <index_name> ON <table> (<column>);.

Rules the tests check:

  • Skip a proposal unless it splits on | into exactly 3 parts, the name and the column are not empty, and distinct_values is 1 to 15 digits from 0 to 9.
  • Reject an index with fewer than 2 distinct values.
  • Keep an index only when distinct_values × 100 is at least row_count, so a single value matches at most about 1% of the rows.
  • Reject an index on a column that an index kept earlier already covers.

Examples

build_index_plan(table: str, index_lines: list[str], row_count: int) -> list[str]
Example 1
Inputbuild_index_plan("events", ["idx_events_user|user_id|4000000", "idx_events_status|status|3"], 100000000)
Output["CREATE INDEX CONCURRENTLY idx_events_user ON events (user_id);"]
Example 2
Inputbuild_index_plan("events", ["idx_events_user|user_id|4000000", "idx_events_type|event_type|12", "idx_events_region|region|8", "idx_events_status|status|4", "idx_events_source|source|6"], 100000000)
Output["CREATE INDEX CONCURRENTLY idx_events_user ON events (user_id);"]
Example 3
Inputbuild_index_plan("events", ["idx_a|user_id|5000000", "idx_b|user_id|5000000"], 1000000)
Output["CREATE INDEX CONCURRENTLY idx_a ON events (user_id);"]