1.When to choose btree? B-tree indexes are suitable for columns with

Question

1.When to choose btree? B-tree indexes are suitable for columns with

better selection properties (value of n_distinct is very big, or =-1), or there is a high proportion of unique values.
2.When to choose gin?
Contrary to btree indexes, GIN indexes are more efficient if the columns have poor selection properties.
In addition, as GIN indexes are inverted indexes, they are very appropriate for queries on combinations of multi-value elements, e.g. array, full-text search, token, etc.
As interfaces for GIN indexes are open, you can customize your GIN indexes according to the individual characteristics of your data. It supports more data types, e.g.similarity between images, text similarity, etc.
3.When to choose gist?
GIST is a generalized index interface belonging to PostgreSQL. It is appropriate for various data types, particularly heterogeneous types, e.g. geometry index, spatial index, range index, etc.
Refer to the following for the principles of GIST indexes:
Getting started with complicated fuzzy search — PostgreSQL unique skills — I. Principles and technical background of GIN, Gist, SP-GiST, and RUM indexes
4.When to choose hash?
Hash indexes are useful if you are only dealing with equality queries, and the length of the indexed columns is very long, more than 1/3 of the database block length. PostgreSQL 10 hash indexes will generate WAL. To ensure reliability, it also supports stream replication.
Hash indexes are not recommended for PostgreSQL versions earlier than version 10, as rebuilding is required after a crash and prior versions do not support stream replication.
5.When to choose brin?
BRIN indexes can be used when the data and stack storage are linearly correlated.
A BRIN index is a block range index. It stores the atomic information (max value, min value, average value, null ratio, COUNT, etc.) of each data block (or consecutive data blocks).
It is particularly suitable for range scans.
https://medium.com/@Alibaba_Cloud/principles-and-optimization-of-5-postgresql-indexes-btree-hash-gin-gist-and-brin-4d133e7f1842

#backend #devops #pgsql #programming #russian

0

11.05.2020

1 ответов

2 просмотра

Rinat Karimov · Accepted Answer

Rinat Karimov

Класс! Для Марии какая конкретно рекомендация?

0

11.05.2020

172 похожих чатов

1.When to choose btree? B-tree indexes are suitable for columns with

1 ответов

Похожие вопросы