If i index two columns (indepentently) do i get the same performance as putting and index on the two

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?

e.g.

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 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 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
• If you have index like c1, c2 there is no reason to have an index like c1. Dec 25 '20 at 19:45