An index is an alternate path to rows, not a universal speed switch. A case feed filtered by reviewer and sorted by creation time can use an index that begins with reviewer_id and continues with the order keys. The database planner may still choose a table scan for a tiny table or broad filter. Inspect a plan with representative row counts and parameters; compare rows examined, sorting, and I/O. Indexes consume storage and slow writes because each changed row updates index entries. Add one to support a measured query pattern, not every column that appears in a schema.
Make this comfortable
Indexes and Query Plans for Case Feeds
Working case
A reviewer feed for reviewer 29 returns the newest 25 inspections. Without a matching index, the server may scan many rows and sort them before applying the limit. An index on reviewer_id, created_at descending, and inspection_id descending can support the filter and stable order used by the cursor query. If the endpoint filters by case status from a different table, the plan may still need a join. Inspect that complete query rather than claiming the index alone solves every delay.
Implementation
CREATE INDEX inspections_reviewer_feed_idx
ON inspections (reviewer_id, created_at DESC, inspection_id DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT inspection_id, created_at, summary
FROM inspections
WHERE reviewer_id = 29
ORDER BY created_at DESC, inspection_id DESC
LIMIT 25;Cost and boundaries
A B-tree lookup is often O(log N) to find a position, followed by work for the P returned rows, but actual latency depends on cache, visibility checks, and I/O. Index maintenance adds write amplification and disk use as N grows. A covering index can avoid some table reads but becomes wider. Start with EXPLAIN on a representative query; use ANALYZE only when it is safe to execute the query in that environment. Recheck plans after data distribution changes.
Common Mistakes
- Do not assume every query uses a newly created index.
- Do not add a wide index without accounting for write and storage cost.
- Do not benchmark only an empty development table.
Connected lessons
Data Persistence; Relational Records and Database Constraints; Transactions and Concurrent Writes; Expand-and-Contract Schema Migrations; Form submission: validate on the server and return field errors; Authorization: check permission for this record on every request; HTTP caching: validate a changed representation with an ETag.
Failure trace
The feed query returns 25 rows in development but scans millions in production. A new index on created_at alone looks plausible, yet the query filters by reviewer and then orders by created_at plus ID. Inspect the actual plan with representative data and design an index for the filter and order together. An index that helps one read also adds space and write work.
Verification
- Capture a plan for a large realistic reviewer feed, including estimated and actual row counts.
- Compare the page-one and deep-cursor query against the same index.
- Measure insert latency and index size after adding the candidate index.
Decision note
An execution plan is evidence for one workload, not a universal verdict. Recheck when distribution, filters, or ordering change, and avoid adding overlapping indexes merely because each query has a different name.
Apply and check
Build Project: paginated inspection feed with safe writes; then check the boundary with Web Development: data and API contracts quiz.
Further connections
Search Index Freshness and Rebuilds; Search Query Normalization and Ranking.
Further connections
web-tech
web-development