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?
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.