Indexes in PostgreSQL

What are Indexes in PostgreSQL?

Indexes in Postgresql is the special feature that is used for data retrieval enhancement. The index in the database is just like the book index. Access to the values is created by the index. Database servers can easily and quickly find the rows by using indexes in comparison to searching without indexes. To get a significant result, we need to use indexes properly.

The CREATE INDEX Command

Syntax of creating indexes in Postgresql using the CREATE INDEX command is given below:

Here are the parameters of syntax

• CREATE INDEX:
  Firstly, the CREATE INDEX clause is written for creating the index.
• index_name:
  After CREATE INDEX clause we need to specify the name of the index. Always try to create easily memorable and meaningful names for the index.
• table_name:
  After the index name, the name of the table to which the index belongs is specified.
• USING method:
  After that index method will be written which can be brin, gin, spgist, gist, hash and btree. btree is used by Postgresql by default.
• column_name [ASC | DESC]:
  After the column names need to be specified. And then ASC and DESC define the order of sorting. By default order of sorting is ASC.
• [NULLS {FIRST|LAST }]:
  If a specified column also contains some null values. Then NULLS LAST or NULLS LAST can be written. With DESC, by default it is NULLS FIRST and With DESC is not written, by default it is NULLS LAST.

Type of Indexes in PostgreSQL

Multiple index types are there in Postgresql: GIN, SP-GiST, GiST, Hash and B-Tree. Different algorithms are used by every index type according to which is best for the query. B-tree indexes are used by the CREATE INDEX command by default, and it is suitable for most common queries.

Let us take an example of table employee, and with the attributes empId, empName, empAdd, empMail, empDob and empMob.

Single Column Index

The index that is created for the single column of a table is known as a single-column index. The syntax for creating single-column indexes in Postgresql is given below:

Syntax

Example: We want to create index on empName column of employee table then we need to write below query:

Now we will run below query to check whether index is created or not:

Output

Multi-Column Index

The index created for more than one table column is known as a multicolumn index. The syntax for creating multi-column indexes is given below:

Syntax

Example:

We want to create index on empName and empAdd column of employee table then we need to write below query:

Now we will run below query to check whether index is created or not:

Output

While creating both multi or single-column indexes, always take into consideration that the columns used frequently in the where condition of the queries as the filter condition. If a single column is frequently used in the where clause condition then a single-column index should be created and if more than one column is used frequently in the where clause condition, then the multi-column index should be created.

Unique Indexes

Unique indexes used for performance enhancement and data integrity also. Any duplicate value insertion is not allowed by the unique indexes. The syntax for creating unique indexes in Postgresql is given below:

Syntax

Example:

We want to create index on empMail column of employee table so that empMail can only accept unique values.

Now we will run below query to check whether index is created or not:

Output

What are Partial Indexes?

The index created over the table subset is known as a partial index. A conditional expression is used for defining the subset which is known as the predicate for the partial indexes. Only those rows of the table are added to the index which satisfies the provided conditional expression. The syntax for creating partial indexes is given below:

Example:

We want to create index on empDob column of employee table only for the rows having the empDob>01-01-2002.

Now we will run below query to check whether index is created or not:

Output

Implicit Indexes in PostgreSQL

The index created automatically by the database server at the time of creation of the object is known as an implicit index. Indexes are automatically created for the columns that have unique key or primary key constraints.

Example:

In our example table employee, the empId column is declared as a primary key, and PostgreSQL will implicitly create an index to speed up primary key lookups.

We will run below query to check implicit index:

Output

What is the DROP INDEX Command in PostgreSQL?

Postgresql index can be dropped using the DROP command. Always drop any index by taking care as dropping an index may slow down or improve the performance of the database. Syntax of using the DROP INDEX command is given below:

Example: we will run below query to check all the indexes of a table:

Output

Let us suppose we want to drop employee_name_index index, then we need to write below query:

Now we will run below query again to check whether index is deleted or not:

Output

What to Avoid Using Index in PostgreSQL?

Indexes are created to increase the performance of the database, but there are situations where we should avoid indexes. Below are some points to tell about the situations where index creation is not beneficial.

• Indexes should not be created on tables having small sizes.
• Table on which insertion and updations are needed to perform frequently.
• Indexes should not be created for the columns in which null values occur frequently.
• Indexes should not be created for the columns on which manipulation operations are performed frequently.