How does a database index work, and when does adding one make things worse?

An index is a separate sorted structure, usually a B tree, mapping column values to row locations so the engine can seek instead of scanning. It speeds up reads that filter or sort on those columns. It costs you on writes, since every insert, update and delete has to maintain it, and it costs storage. Low selectivity columns and indexes nothing queries are usually a net loss.

An index is essentially a sorted copy of one or more columns with pointers back to the rows, usually a B tree, so the engine can binary search instead of reading every page. That turns a filter on a big table from a sequential scan into a handful of page reads. The cost is that every write has to update the index too, so a table with eight indexes pays eight times on insert, and bulk loads slow right down. The other thing I watch is selectivity. If a column is a boolean that is true for ninety percent of rows, the planner will usually just scan, because random access to that many rows costs more than reading sequentially. I had a case where somebody added an index on a status column with three values and nothing improved at all. What actually fixed it was a composite index on tenant id and created at, matching both the filter and the sort, so the database could seek and then read in order without a separate sort step.