PostgreSQL Chinese Learning Manual (INDEX)

I. Index type:

PostgreSQL provides a variety of index types: B-Tree, Hash, GiST, and GIN. Because they use different algorithms, each index type has a suitable Query type, saving time and resources, the create index Command creates a B-Tree INDEX.

1. B-Tree:
Create table test1 (
Id integer,
Content varchar
);
Create index test1_id_index ON test1 (id );
B-Tree indexes are mainly used for equals and range queries, especially when the index column contains the operator "<, <=, =,> =, and>" as the query condition, the query planner of PostgreSQL considers the use of B-Tree indexes. PostgreSQL can also use B-Tree indexes for queries using BETWEEN, IN, IS NULL, and IS NOT NULL. However, for queries based on pattern matching operators, such as LIKE, ILIKE, and ,~ And ~ * Only when the mode has a constant and the constant is at the beginning of the mode string, for example, col LIKE 'foo % 'or col ~ '^ Foo', the index will take effect. Otherwise, a full table scan will be performed, for example, col LIKE' % bar '.

2. Hash:
Create index name ON table USING hash (column );
Hash indexes can only process simple equals comparison. When an index column is compared with an equal operator, the query planner considers using a hash index.
It should be noted that PostgreSQL does not have a better performance than B-Tree indexes, but its size and construction time are worse. In addition, as the hash index operation does not currently record WAL logs, once the database crashes, we will have to re-create the hash index with REINDEX.

3. GiST:
GiST index is not a separate index type, but an architecture that can implement many different index policies. This allows GiST indexes to use specific operator types based on different index policies.

4. GIN:
GIN indexes are reverse indexes that can process values that contain multiple keys (such as arrays ). Similar to GiST, GIN also supports user-defined index policies, so that GIN indexes can use specific operator types based on different index policies. As an example, the standard release of PostgreSQL contains the GIN operator types used for one-dimensional arrays, such as <@, @>, =, and.

Ii. Composite Index:

Indexes in PostgreSQL can be defined on multiple fields in a data table, for example:
Create table test2 (
Major int,
Minor int,
Name varchar
}
Create index test2_mm_idx ON test2 (major, minor );
In the current version, only B-tree, GiST, and GIN support compound indexes. Up to 32 fields can be declared.
1. B-Tree composite indexes:
In B-Tree composite indexes, any subset of the index field can be used for query conditions. However, only when the first index field in the composite index (leftmost) when it is included, the highest efficiency can be obtained.

2. GiST-type composite index:
In GiST-type composite indexes, only when the first index field is included in the query condition can we determine how many index data will be scanned by the query, the condition on other index fields only limits the entries returned by the index. If most of the data in the first index field has the same key value, the application of GiST index will be inefficient.

3. GIN-type composite indexes:
Unlike B-Tree and GiST indexes, GIN composite indexes are not affected by which index field subsets are used in the query conditions. No matter which combination is used, the same efficiency is achieved.

Exercise caution when using composite indexes. In most cases, the index on a single field is enough, saving time and space. Unless the table usage mode is very fixed, the indexes of more than three fields are of little use.

1 2 3 Next Page