# 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 −

nameroll_noperc_marks
Aniket1224
Siddhi4565
Yash2642
Isha5687

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