2

This question already has an answer here:

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.

marked as duplicate by Erwin Brandstetter postgresql Nov 5 '15 at 11:57

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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

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