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 16:58:08