Are indexes with overlapping columns in the same order always redundant in Postgres?

Question

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?

Answer 1

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.