PostgreSQL Index Classification and usage

1. Indexing method

The PostgreSQL database supports single index, multi-column composite index, part index, unique index, expression index, implied index, and concurrent index.

2. Indexing methods

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

3. Index Use Range

1). B-tree

B-tree can be used effectively when a query contains an equals sign (=) and a range operator (<, <=,, >=, between, and in).

2). Hash

An equals sign operator (=), which is not suitable for range operators.

3). Gist

Applies to custom complex types, including Rtree_gist, Btree_gist, Intarray,tsearch, Ltree, and Cube.

4). GIN

Gin occupies more than three times times more space than gist, and is suitable for complex like such as '%abc12% '.

4. Index usage Precautions

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

2). When retrieving data, it is important to select a good alternate column as the index, foreign key, or key with the maximum minimum value, and the selectivity of the column is critical to the validity of the index.

3). For better performance to remove unused indexes, rebuild all indexes every January in order to clear the unusable rows.

4). If there is a very large amount of data, use the table partitioning index.

5) When a column contains a null value, consider establishing a conditional index that does not contain null.

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1783945

PostgreSQL Index Classification and usage