Updated May 19, 2023
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 index is very important and useful in PostgreSQL for the fastest access of data from the table. 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. If we have doesn’t specify any index type while creating an index, then it will automatically create a B-Tree index.
Syntax
Below is the syntax of the PostgreSQL index:
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. 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 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 index will speed up operations on the select query. It will also support in where clause for fast retrieval of data. PostgreSQL index is slowing operations on insert and update statement, we can create an update and delete index with no loss of data.
Below are the types of index available in 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)
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-tree index query planner considers the below operator when the query involves comparison. <=, =, <, >=, 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.
Code:
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.
2. Hash index
Hash index in PostgreSQL will handle only simple equality comparison, i.e. (=). 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. The hash index in PostgreSQL is not transaction safe and will not be replicated in streaming or file-based replication mechanisms.
Below is the syntax and example of a hash index in 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.
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.
Below is the syntax and example of the GIN index in PostgreSQL.
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.
4. GiST index
GiST index is also known as the generalized inverted search tree index. 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. GiST index consists of multiple node values. The node of the GiST index will be organized in a tree-structured way.
Below is the syntax and example of a GiST index in PostgreSQL.
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.
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.
Below is the syntax and example of the SP-GiST index:
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.
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.
Below are the syntax and example of the BRIN index:
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.
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.
