2

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.

Ivan Sopov
  • 213
  • 3
  • 8

1 Answers1

3

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 05 '15 at 09: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 05 '15 at 10:27
  • @IvanSopov if entire index is already loaded in the memory then it probably does not matter much. – jkavalik Nov 05 '15 at 11:13