PostgreSQL Indexes

What are Indexes

Indexes are the special lookup tables that are used to speed up the retrieval of data from the databases.

A database index is similar like the index of a book. An index creates an entry for each value that appears in the indexed columns.

Important features of database indexes

• An index speeds up data output with SELECT query and WHERE clause but it slows down data input with INSERT and UPDATE statement.
• You can create or drop indexes without affecting the data.
• You can create an index by using CREATE INDEX statement by specifying the index name and table or column name on which index is created.
• You can also create a unique index, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

PostgreSQL Create Index

The CREATE INDEX statement is used to create a PostgreSQL index.

Syntax:

Index Types

There are several index types in PostgreSQL like B-tree, Hash, GiST, SP-GiST and GIN etc. Each index types use a different algorithm according to different queries. By default, the CREATE INDEX command uses B-tree indexes.

Single Column Indexes

An index is called a single column index if it is created based on only one table column.

Syntax:

See this example:

We have a table named "EMPLOYEES", having the following data:

Let's create an index named "employees_index" on the table "EMPLOYEES" on the basis of column "name"

Execute the following query:

Here, you can see that an index named "employees_index" is created on that table

Output:

Multi-column Indexes

If an index is created by using more than one column of a table then it is called multi-column index.

Syntax:

Let's create a multi-column index named "multicolumn_index" on that same table "EMPLOYEES"

Execute the following query:

Output:

Unique Indexes

Unique Indexes are created to get data integrity and enhance performance. It doesn't allow inserting duplicate values in the table.

Syntax:

PostgreSQL Drop Indexes

The DROP INDEX method is used to drop an index in PostgreSQL. If you drop an index then it can slow or improve the performance.

Syntax:

Let's take an example to drop the index we have previously created named "multicolumn_index".

Execute the following query:

Now, you can see that the index named "multicolumn_index" is deleted/ dropped.

Output:

When should you avoid Indexes

• You should avoid using indexes on small tables.
• Don't create indexes for tables that have frequent, large batch update or insert operations.
• Indexes should not be used on columns that contain a high number of NULL values.
• Don't create indexes for columns that are frequently manipulated.