Suppose I have a table with several column, among them - columns a and b.

Is there any use case for which having both multi-column index on (a,b) and a single-column index on (a) is beneficial?

AFAIU for simple equality check using first column of multi-column index is equally good as using the single-column index.


1 Answer 1


You are right. The index on (a) is not necessary if there is another one on (a,b).

The only situation where this might (!) be helpful is, if a lot of index only scans are done on the indexes (something like select count(*) from foo where a = 42) and b is e.g. a large text column.

The index on (a) will obviously be a lot smaller, so an index only scan on that index will be faster (whether or not that "faster" is actually measurable depends on the data in the column b)

But I would consider this is an edge case with a very specific workload.

  • 1
    The smaller indexes might be useful when your queries often use index merges, but thats only another edge case.
    – jkavalik
    Nov 5, 2015 at 9:16
  • How large should be the second column? I tried 332 length varchars (9 concatenated UUIDs separated with '-') but have not seen any difference.
    – Ivan Sopov
    Nov 5, 2015 at 10:27
  • @IvanSopov if entire index is already loaded in the memory then it probably does not matter much.
    – jkavalik
    Nov 5, 2015 at 11:13

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