1) there are 2 fields – varchar ‘text’ and int ‘value’. An index was created for them:

CREATE INDEX idx_index_value_and_text
ON index_test (value, text)

I am writing:

select * from index_test r where
r.text = ‘some value’

The analyzer says:

Index Scan using
idx_index_value_and_text on index_test

Question: how can it search by index if the first column is not specified? In terms of a B-Tree, what is a composite index: a single tree whose node values ​​are the concatenation of strings of values ​​of its fields?

2) This search, depending on the distribution of column values, has different types: Bitmap Heap Scan and Bitmap Index Scan, or Index Scan if the distribution is sufficiently even. What do Bitmap types mean, how is Bitmap Index Scan different from Index Scan? Also, does Postgres Index Range Scan have?

Answer 1, authority 100%

A composite index, unlike a “single” index, uses not one value as an indexed value, but several values, that is, if there are 2 fields A and B, then the composite index will look something like this:

| A | B |
---------
| 1 | 1 |
| 1 | 2 |
| 1 | 4 |
| 2 | 2 |
| 2 | 3 |
| 2 | 5 |

Roughly speaking, this is sorting by two fields, not one field. That is, it is not indexing the concatenated A + B values.

At the BTree level, it looks like the BTree leaves responsible for field A refer to the nested BTree tree responsible for field B.

Scanning the index in your case actually means that since the first field is not specified for you, the server scans all the leaves of tree A to find a suitable value for field B.

A composite index is useful when information can be clustered: let’s say dictionaries / encyclopedias are arranged according to this principle. You need an article with the letter “D” – you find the section with the letter “D” by the table of contents and then look for the desired article. But if you need an article ending with “K” – then clustering will no longer help – you have to rummage through the entire book (scan the index).