Hacker News Reader

Comment by takeda | Hacker News Reader

About indexes, they improve query performance, but they also decrease write performance (and of course take space) that's why it's good idea to remove indices that aren't used or rarely used. Especially indices that are placed on columns that have random data and/or are frequently updated (prevents HOT updates) can hurt write performance.

View on HN

One very powerful "trick" is to use partial indexes where useful. For example, a pretty common pattern is to have tables where most rows aren't actively used most of the time, eg when you have a status column, and most rows are in the final status which you usually don't touch, so most where conditions include statuses which are not the final one. If you create a partial index WHERE status IN ('s1', 's2' ...), the index becomes a lot smaller and a lot faster.

I never thought about that, but that's a neat trick.

AFAIK it's also proportional to the number of rows with data in the indexed columns. Inserting a row with a NULL in the indexed column does not cause an update to the index (at least experimentally it doesn't seem to contribute to index size on SQL Server).

I imagine this is/was a simple optimization for DBMS to implement, but I would love to be corrected otherwise...

If you change a value to null it’s still a row allocated in the index. The index size won’t shrink unless you rebuild it. Removing and deleting data will just end up with fragmentation.

Reply on news.ycombinator.com