What kind of indexing can be done on a PostgreSQL table?

Indexing is used to speed up query execution in PostgreSQL, and any relational database in general. PostgreSQL tables primarily support several index types. Let us discuss 3 frequently user index types in brief −

Hash

These indexes can only handle equality comparisons. In other words, if I want to check if itemA = itemB, then hash indexes are useful. It is not suited for other types of operations, like >, <. >=, <=. Suppose I have a marks table, defined below −

Now, if I can add the hash index on the roll_no column as follows −

CREATE INDEX roll_no_index ON marks USING hash (roll_no);

For queries involving equal to comparisons on the roll_no column, this index will be used. An example is given below −

SELECT * from marks WHERE roll_no = 12

However, if you have other types of comparisons, like

SELECT * from marks where roll_no > 40

then this index will be useless, and PostgreSQL will organize the query plan assuming the index doesn’t exist.

B-tree

This is the default index used by PostgreSQL. In other words, if you don’t explicitly specify an index type, PostgreSQL will create a Btree index. This index is used by PostgreSQL for all the comparison operations (>=, <=, >, <, =). This makes it better than Hash index. It can be created using the following command −

CREATE INDEX index_name on table_name (columnName1, columnName2,…)

As you can see from the above syntax, you can also create the multi-column index. Whether to create single-column index or multi-column index depends on whether one or multiple columns will be frequently used in the WHERE part of the queries made to the table.

GiST

GiST stands for Generalized Search Tree. This index is used very frequently with a popular extension of PostgreSQL, called PostGIS. PostGIS is used for performing geo-spatial queries on geometry objects. Now operations like contains, intersects, etc. cannot benefit from B-tree index (which can only handle comparisons and equality checks). GiST index comes in very handy here. It is generally applied on the geometry column of the table like −

CREATE INDEX geom_index on myGeomTable USING gist(geom)

A very good explanation of GIS is given here − https://postgis.net/workshops/postgisintro/indexing.html

Apart from these index types, there are other index types as well: GIN, BRIN, etc. You can read more about them here − https://www.enterprisedb.com/postgres-tutorials/overview-postgresql-indexes

Yash Sanghvi

Published on 02-Feb-2021 12:28:08

• Related Questions & Answers
• What all constraints can be added to a PostgreSQL table?
• What can be done to control water pollution?
• What kind of SQL statements can be used to prepare statements?
• From a business perspective, what can be done with used plastic bottles?
• What kind of compound units can be used in MySQL EXTRACT() function?
• What could be some easy exercises that could be done sitting on a chair in office?
• What kind of string comparison, case-sensitive or not, can be performed by MySQL?
• Create Primary Key on an existing table in PostgreSQL?
• How can CONCAT() function be applied on columns of MySQL table?
• What should be done with the left/ right edges of the content on overflow with JavaScript?
• How to create a table in PostgreSQL?
• How can element wise multiplication be done in Tensorflow using Python?
• What kind of variables can we access in a lambda expression in Java?
• How to rename an existing column of a table in PostgreSQL?
• What are the techniques of Text Indexing?