1) there are 2 fields - varchar 'text' and int 'value'. An index has been created for them:

CREATE INDEX idx_index_value_and_text ON index_test (value, text)

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 he search by index if the first column is not specified? In terms of B-Tree - what is a composite index: a single tree, the values ​​of the nodes of which are concatenation of the rows of values ​​of the fields it contains?

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 does Bitmap Index Scan differ from Index Scan? Also, is there Postgres Index Range Scan?

  • > Question: how can he search by index, if the first column is not specified? So he does not look for - a scan is browsing pages in a row, not a search. - msi
  • This is Seq Scan, and here is Index Scan. - KutaBeach

1 answer 1

The composite index, in contrast to the "single" index, uses not one value as an indexed value, that is, if there are 2 fields A and B, then the composite index will look like this:

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

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

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

An index scan in your case actually means that since the first field is not set for you, the server scans all the leaves of the tree A to find the appropriate value for the field B.

A composite index is useful when information can be clustered: for example, dictionaries / encyclopedias are arranged according to this principle. You need an article on the letter "D" - you find a section on the letter "D" on the table of contents and then you are looking for the article you need. But if you need an article ending in "K" - then clustering will no longer help - you will have to rummage through the whole book (scan the index).

  • Thank you, the main question remained incomprehensible: why does he search by index, if the search term includes only the SECOND column, that's what's strange. - KutaBeach
  • one
    Index volume and data volume. Read less, respectively, faster. - alexlz
  • one
    If I understood the point correctly, here a full-index-scan takes place, when all the necessary request data is contained in the index. Even though it is impossible to search by it as by BTree, it is still easier to take values ​​from the index, and not from the table. - KutaBeach