Indexes & SQL Tuning
What an index actually is
Without a useful index, finding rows may require a sequential scan. Its cost depends on row width, storage, caching, and parallelism, but it grows with the table; a selective index lookup can avoid reading almost all of it.
A B-tree is the usual general-purpose index: a balanced structure with a high branching factor, so a lookup navigates a small number of pages. Because keys stay sorted, one B-tree can support equality lookups, range scans, and compatible ORDER BY operations. Other index types serve text, spatial, hash, and append-oriented access patterns.
The catch is maintenance. Every insert, update, and delete may update the table, write-ahead log, and affected index entries. Indexes make selected reads faster by adding write work and disk usage.
no index 10,000,000 row reads B-tree index ~4 page reads but every write: table/WAL work + affected index entries 5 indexes = 5 additional structures to maintain
An unused index adds write and storage cost without serving reads. Audit usage before dropping one, because rare constraints, maintenance jobs, or periodic queries may still depend on it.
3 components2 connections0:00
Recording…