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 index then it will automatically create a B-Tree index.
Syntax
Below is the syntax of 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: In the above syntax index name is defined as the name of the newly created index, we can give any name to the index. The table name is defined as the name of the table on which we have created an index. We can create single and multiple indexes in PostgreSQL. When we have to create an index on single column it is called a single-column index. When we have to create an index on multiple columns it is called a multicolumn index in PostgreSQL.
PostgreSQL Index Types
The index in PostgreSQL is used for fast retrieval of data from the table. 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
B-tree index in PostgreSQL defined as a self-balancing tree which was maintains sorted data and it will allows insertions, deletions, and selections access. PostgreSQL B-tree index query planner is considering the below operator when the query is involved 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 is obtained inequality operator the query planner will consider it in hash index. To create a hash index in PostgreSQL we need to use create an index statement. Hash index is not transaction safe, it won’t be replicated in streaming or file-based replication.
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
GIN index is also called a generalized inverted index. It is commonly known as the GIN index. GIN index is used when we have to store multiple values in the table column. An example of multiple values is an array, jsonb, and range types. 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. GIST index in PostgreSQL will allows to build the general tree structure. GiST index is useful in geometric data type and full data 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 also called as 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 as the block range indexes. It is smaller and less costly to maintain the comparison with the Btree index. BRIN index is used on a large table that is not practical using Btree 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 newly created index.
Recommended Articles
This is a guide to PostgreSQL Index Types. Here we discuss an introduction to PostgreSQL Index Types, appropriate syntax, 6 different types and query examples. You can also go through our other related articles to learn more –
