EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Index Types
 

PostgreSQL Index Types

Priya Pedamkar
Article byPriya Pedamkar

Updated May 19, 2023

PostgreSQL Index Types

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

PostgreSQL Index Types1

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:

PostgreSQL Index Types2

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:

PostgreSQL Index Types3

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:

GiST index

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:

SP-GiST index

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:

BRIN index

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW