【PostgreSQL】Introduction to PostgreSQL Index Types

Postgresql

PostgreSQL Index Types | 6 Types of Index available in PostgreSQL (educba.com)

PostgreSQL Index Types (postgresqltutorial.com)

Introduction to PostgreSQL Index Types

PostgreSQL index types have basically divided into six types, i.e., B-tree, hash, GIN, BRIN, SP-GIST, and GiST index, each of the index types has different storage structures and algorithm to retrieve data from the query.

PostgreSQL 的索引类型基本上分为六种，即 B 树索引、哈希索引、GIN 索引、BRIN 索引、SP-GIST 索引和 GiST 索引，每种索引类型都有不同的存储结构和算法来检索查询的数据。

PostgreSQL index is very important and useful in PostgreSQL for the fastest access of data from the table.

PostgreSQL 索引在 PostgreSQL 中非常重要和有用，能以最快速度访问表中的数据。

We have to create an index by using the create index statement in PostgreSQL, we need to specify the type of index when we have creating an index on the table column.

我们必须使用 PostgreSQL 中的 create index 语句创建索引，在为表列创建索引时，我们需要指定索引类型。

If we have doesn’t specify any index type while creating an index, then it will automatically create a B-Tree index.

如果我们在创建索引时没有指定任何索引类型，那么它会自动创建一个 B 树索引。

Syntax

Below is the syntax of the PostgreSQL index:

下面是 PostgreSQL 索引的语法：

CREATE INDEX (name_of_index) on (name_of_table) USING (index_type) (name_of_column);

OR

Create index (name_of_index) on (name_of_table) (column_name1, column_name2, column_name3)

Explanation: The syntax provided allows us to specify the index name as the desired name for the newly created index.

解释： 提供的语法允许我们指定索引名称，作为新创建索引的理想名称。

We have the flexibility to choose any desired name.

我们可以灵活选择任何所需的名称。

Furthermore, we define the table name as the name of the table on which we are creating the index.

此外，我们还可以将表名定义为创建索引的表的名称。

We can create single and multiple indexes in PostgreSQL.

我们可以在 PostgreSQL 中创建单索引和多索引。

When we create an index on a single column, it is termed a single-column index.

当我们在单列上创建索引时，它被称为单列索引。

Creating a multicolumn index in PostgreSQL is a common practice when we want to create an index on multiple columns.

在 PostgreSQL 中创建多列索引是一种常见的做法，因为我们要在多列上创建索引。

PostgreSQL Index Types

The PostgreSQL index facilitates the efficient retrieval of data from the table. The PostgreSQL index is the same as the index of the book.

PostgreSQL 索引有助于从表中高效检索数据。PostgreSQL 索引与书籍的索引相同。

PostgreSQL index will speed up operations on the select query.

PostgreSQL 索引将加快选择查询的操作速度。

It will also support in where clause for fast retrieval of data.

它还支持在 where 子句中快速检索数据。

PostgreSQL index is slowing operations on insert and update statement, we can create an update and delete index with no loss of data.

PostgreSQL 索引会减慢插入和更新语句的操作速度，我们可以在不丢失数据的情况下创建更新和删除索引。

Below are the types of index available in PostgreSQL:

以下是 PostgreSQL 中可用的索引类型：

• B-tree index.
• Hash index.
• Space partitioned GiST index (SP-GiST)
• Block range indexes (BRIN)
• Generalized inverted index (GIN)
• Generalized inverted search tree index (GiST)
• BTree 索引
• Hash 索引
• 空间分区 GiST 索引（SP-GiST）
• 块范围索引（BRIN）
• 广义倒排索引（GIN）
• 广义倒排搜索树索引（GiST）

1. B-tree index

The B-tree index in PostgreSQL is a self-balancing tree that maintains sorted data and enables access for insertions, deletions, and selections operations.

PostgreSQL 中的 B 树索引是一棵自平衡树，用于维护排序数据，并支持插入、删除和选择操作。

PostgreSQL B-tree index query planner considers the below operator when the query involves comparison. <=, =, <, >=, IN, Between, IS NOT NULL, IS NULL

当查询涉及比较时，PostgreSQL B 树索引查询规划器会考虑以下操作符。<=, =, <, >=, IN, Between, IS NOT NULL, IS NULL

Query planner also checks the pattern matching operator like and ~ if the pattern is constant in PostgreSQL. Below is the example and syntax of the B-tree index in PostgreSQL.

如果模式在 PostgreSQL 中是常量，查询规划器还会检查模式匹配运算符 like 和 ~。下面是 PostgreSQL 中 B 树索引的示例和语法。

ode:

CREATE INDEX btree_idx on test_idx USING BTREE (id);
\d+ test_idx;

Output:

Explanation: In the above example, we have created an index on the id column in the test_idx table. We have also defined the name as btree_idx to the newly created index.

解释： 在上面的示例中，我们在 test_idx 表的 id 列上创建了一个索引。我们还将新创建的索引定义为 btree_idx。

2. Hash index

Hash index in PostgreSQL will handle only simple equality comparison, i.e. (=).

PostgreSQL 中的散列索引只处理简单的相等比较，即 (=)。

It shows that whatever inequality operator is obtained, the query planner will consider it in the hash index.

这表明，无论获得什么不等式运算符，查询规划器都会在哈希索引中考虑它。

We need to create an index statement to create a hash index in PostgreSQL.

我们需要创建一条索引语句，以便在 PostgreSQL 中创建哈希索引。

The hash index in PostgreSQL is not transaction safe and will not be replicated in streaming or file-based replication mechanisms.

PostgreSQL 中的哈希索引不是事务安全的，不会在流式或基于文件的复制机制中复制。

Below is the syntax and example of a hash index in PostgreSQL:

下面是 PostgreSQL 中散列索引的语法和示例：

Code:

CREATE INDEX hash_idx on test_idx USING HASH (stud_id);
\d+ test_idx;

Output:

Explanation: In the above example, we have created an index on the stud_id column in the test_idx table. We have also defined the name as hash_idx to the newly created index.

解释： 在上面的示例中，我们在 test_idx 表中的 stud_id 列上创建了一个索引。我们还将新建索引的名称定义为 hash_idx。

3. GIN indexes

The GIN index is also called a generalized inverted index. It is commonly known as the GIN index. The GIN index is used when we have to store multiple values in the table column. An array, jsonb, and range types are examples of multiple values. The GIN index in PostgreSQL will be created on the text column.

GIN 指数又称广义倒排指数。它通常被称为 GIN 索引。当我们必须在表列中存储多个值时，就会使用 GIN 索引。数组、jsonb 和范围类型就是多值的例子。PostgreSQL 中的 GIN 索引将创建在文本列上。

Below is the syntax and example of the GIN index in PostgreSQL.

下面是 PostgreSQL 中 GIN 索引的语法和示例。

Code:

CREATE INDEX GIN_idx1 ON student USING GIN (to_tsvector('english', stud_name));
\d+ student;

Output:

Explanation: In the above example, we have created an index on the stud_name column in the student table. We have also defined the name as GIN_idx1 to the newly created index.

解释： 在上面的示例中，我们在学生表的 stud_name 列上创建了一个索引。我们还为新创建的索引定义了名称 GIN_idx1。

4. GiST index

GiST index is also known as the generalized inverted search tree index.

GiST 索引又称广义倒搜索树索引。

The PostgreSQL GIST index will make it possible to construct the overall tree structure.GiST index is useful for geometric data type and full search in PostgreSQL.

PostgreSQL GIST 索引可以构建整体树形结构。GiST 索引适用于 PostgreSQL 中的几何数据类型和完全搜索。

GiST index consists of multiple node values. The node of the GiST index will be organized in a tree-structured way.

GiST 索引由多个节点值组成。GiST 索引的节点将以树形结构的方式组织。

Below is the syntax and example of a GiST index in PostgreSQL.

以下是 PostgreSQL 中 GiST 索引的语法和示例。

Code:

CREATE INDEX gist_idx_test ON GIST_IDX USING gist(circle_dim);
\d+ GIST_IDX;

Output:

Explanation: In the above example, we have created an index on circle_dimcolumn in the GIST_IDX table. We have also defined the name as gist_idx_test to the newly created index.

解释： 在上面的示例中，我们在 GIST_IDX 表的 circle_dimcolumn 上创建了一个索引。我们还将新创建的索引定义为 gist_idx_test。

5. SP-GiST index

The SP-GiST index is alternatively known as the space partitioned generalized inverted search tree. It will support the partitioned search tree. The SP-GiST index is most useful for the natural clustering element. An SP-GiST index provides a partition search tree.

SP-GiST 索引又称空间分区广义倒置搜索树。它支持分区搜索树。SP-GiST 索引对自然聚类元素最有用。SP-GiST 索引提供了一种分区搜索树。

Below is the syntax and example of the SP-GiST index:

以下是 SP-GiST 索引的语法和示例：

Code:

CREATE INDEX spgist_idx ON spgist_table USING SPGiST (phone_no);
\d+ spgist_table;

Output:

Explanation: In the above example, we have created an index on the phone_no column in the spgist_table table. We have also defined the name as spgist_idx to the newly created index.

解释： 在上面的示例中，我们为 spgist_table 表中的 phone_no 列创建了一个索引。我们还为新创建的索引定义了 spgist_idx 名称。

6. BRIN index

BRIN index is also called the block range indexes. It is smaller and less costly to maintain the comparison with the Btree index. Using a BRIN index on a large table is a more practical approach than using a Btree index without horizontal partitioning.

BRIN 索引也称为块范围索引。与 Btree 索引相比，BRIN 索引更小，维护成本更低。在大型表上使用 BRIN 索引比不使用水平分区的 Btree 索引更实用。

Below are the syntax and example of the BRIN index:

下面是 BRIN 索引的语法和示例：

Code:

CREATE INDEX brin_idx ON test_idx USING BRIN(phone);
\d+ test_idx;

Output:

Explanation: In the above example, we have created an index on the phone column in the test_idx table. We have also defined the name as brin_idx to the newly created index.

解释： 在上面的示例中，我们为 test_idx 表中的电话列创建了一个索引。我们还将新建索引的名称定义为 brin_idx。

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Index Types” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

希望 EDUCBA 提供的 "PostgreSQL 索引类型 "信息对您有所帮助。您可以查看 EDUCBA 的推荐文章，了解更多信息。

1. PostgreSQL Inner Join
2. SERIAL PostgreSql
3. PostgreSQL Schema
4. PostgreSQL Views