Shulou(Shulou.com)05/31 Report--

This article shares with you the content of the sample analysis of PostgreSQL index classification and usage. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Index mode

The PostgreSQL database supports single-column index, multi-column compound index, partial index, unique index, expression index, implicit index, and concurrent index.

two。 Index method

PostgreSQL supports B-tree, hash, GiST, and GIN index methods.

3. Index range of use

1) .B-tree

B-tree can be used effectively when a query contains the equal sign (=) and the range operator (=, BETWEEN, and IN).

2). Hash

An equal sign operator (=) that is not suitable for the range operator.

3) .GiST

Suitable for custom complex types, including rtree_gist, btree_gist, intarray,tsearch, ltree and cube.

4). GIN

GIN takes up more than three times more space than GiST and is suitable for complex like, such as like'% ABC12%'.

4. Matters needing attention in using index

1)。 When a table has many rows, it is important to index a table column.

2)。 When retrieving data, you should select a good alternative column as the index, foreign key, or the key with the maximum and minimum value. The selectivity of the column is very important to the validity of the index.

3)。 Remove unused indexes for better performance and rebuild all indexes every other month in order to clear unavailable rows.

4)。 If you have a very large amount of data, use a table partitioned index.

5) when a column contains a null value, consider creating a conditional index that does not contain NULL.

Thank you for reading! This is the end of this article on "sample Analysis of the Classification and use of PostgreSQL Index". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.