I want to put an index on two columns to improve my query performance for each. I also want to perform queries where both columns are used.

From my understanding, if you had a BTREE index on each column, you could theoretically get the same performance by querying both indices and then combining the range you got - if that makes sense?


SELECT * FROM table WHERE f_name = 'first' and l_name = 'last'

Instead checking an index for ('first', 'last') you'd check the f_name index for first and the l_name index for last. Now, you can check if the results of both index queries are the same (the result of the initial query). Obviously, this is slightly slower than a composite index, but still much faster than no index.

With this idea, you get performance benefits not only when querying both columns together, but either column independently.

My main question is: if I have these 2 independent indices, would I need another index on both, to improve the performance of queries using both columns?

  • In general, index ANDING between two indexes is not as efficient as one index. You often end up with c1, c2 and c2 as indexes
    – Lennart
    Dec 25 '20 at 18:02
  • @Lennart c1, c2 and c2 as indexes? Does the performance still increase on the 2-column query, with independently indexed columns? Dec 25 '20 at 18:40
  • Lets call the index X1 (c1, c2) and X2(c2). For queries like WHERE c1=? and WHERE (c1, c2) =(?,?) X1 can be used. For queries like WHERE c2=? X2 can be used
    – Lennart
    Dec 25 '20 at 19:08
  • @Lennart so if I have the composite index, I don't need the individual indices? Dec 25 '20 at 19:34
  • 1
    If you have index like c1, c2 there is no reason to have an index like c1.
    – Lennart
    Dec 25 '20 at 19:45

Your question is a bit jumbled. No, you can't get theoretically the same performance, unless one of the two columns is unique or nearly unique. But yes, you can get performance that while slower than the composite is still much faster than no index at all. Will it be fast enough that you don't need the composite index? No one can answer that but you. We don't know either how fast you need it to be, nor how selective the condition against each column will be (both independently and jointly).

But if you are going to have indexes on (a) and on (b), you might as well just change the first one to be on (a,b) or change the second to be on (b,a). The extra cost will be nearly negligible (unless the added column contains wide data values) while the extra value at least has the potential to be large. The cost might be slightly higher in v13, because having the composite index might defeat the new deduplication logic compared to the one with just the prefix column.

Doing a detailed analysis of whether it is worthwhile is probably not worthwhile.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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