CODEWITHSUNDEEP
sqldatabasepostgresqlindexing
lapots
lapots

Reputation: 13395

usage of b-tree index for multiple columns in the table

As far as I know one of the most common indexes in the database is the one that uses b-tree.

If I have the index for integer column

column1 column2
   2     name2
   3     name3
   1     name4

it might create the index that looks like this

 2
| |
1 3

If I use column2 it might create it like this

   name2
 |      |
name1 name3

But if I have a composite index with two fields, how it's gonna order it then? Will it just go with basic comparisons for two columns?

Is there some other additional optimizations that database does for index structure?

Upvotes: 1

Views: 1777

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The multiple keys are all used together in the index:

     2,name2
     |     |
1,name4  3,name3

The comparison is made on the first key. Only in the case of ties, does the next key get used. So, if all the numbers were the same you would have:

     2,name2
     |     |
2,name3  2,name4

Upvotes: 3

Related Questions