0

Say I add an index on column "foo" and another on columns "foo, bar". The indexes are identical in every way except for which columns they index.

My impression is that the former index will then be redundant – the "foo, bar" index can be used to look things up by just "foo" as well.

Is this correct? Are there nuances to it?

I can e.g. imagine that because the "foo, bar" index is bigger, the smaller "foo" index may be cheaper to load into memory or similar.

Are there any situations where I would benefit from having indexes both on "foo" and "foo, bar"? Conversely, are there downsides to having both?

1
  • 1
    I can't think of any situation where having both would make sense unless the one on foo is a unique index. The downside when keeping both is the overhead it takes to update the (supposedly unnecessary) index for each DML statement.
    – user1822
    Mar 8, 2022 at 10:31

1 Answer 1

2

Yes, the foo index would be redundant. While it would be smaller to load into memory than foo_bar, it also covers fewer scenarios. In addition, you're now storing foo on disk three times, and every insert, update or delete now has an additional index to maintain. This will slow down these operations. The added space of have two columns in memory for a read operation compared to one column is negligible. Also, take into account that now you'd potential have both indexes in memory at any given time, and thus three columns worth of data taking up memory space, compared to just two.

Keep your indexes simple. Look for indexes that can cover as many of your queries as possible. In this case, stick with foo_bar. It can work for queries only requiring foo, while also working for quires for foo and bar.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.