PostgreSQL – CREATE INDEX

To better understand the concept behind indexes in PostgreSQL assume that you need to look up for Raju Kumar’s phone number on a phone book. With the understanding that names on the phone book are in alphabetically order, you first look for the page where the last name is Kumar, then look for the first name Raju, and finally get his phone number.

Suppose the names on the phone book were not ordered alphabetically, you would have to go through all pages, check every name until you find Raju Kumar’s phone number. This is called a sequential scan which you go over all entries until you find the one that you are looking for.

Similar to a phonebook, the data stored in the table should be organized in a particular order to speed up various searches. This is why indexes come into play.

In PostgreSQL, the CREATE INDEX statement to define a new index for a table.

Syntax:
CREATE INDEX index_name ON table_name [USING method]
(
    column_name [ASC | DESC] [NULLS {FIRST | LAST }],
    ...
);

Let’s analyze the above syntax:

• First, specify the index name after the CREATE INDEX clause. The index name should be meaningful and easy to remember.
• Second, specify the name of the table to which the index belongs.
• Third, specify the index method such as a binary tree, hash, gist, spgist, gin, and brin. PostgreSQL uses a binary tree by default.
• Fourth, list one or more columns to be stored in the index. 
• The ASC and DESC specify the sort order. ASC is the default. 
• NULLS FIRST or NULLS LAST specifies nulls sort before or after non-null. The NULLS FIRST is the default when DESC is specified and NULLS LAST is the default when DESC is not specified.

For the purpose of demonstration, we will use the address table from the sample database for the demonstration.

Example:

The following query finds the address whose phone number is 223664661973:

SELECT * FROM address
WHERE phone = '223664661973';

It is obvious that the database engine had to scan the whole address table to look for the address because there is no index available for the phone column.

To show the query plan, you use the EXPLAIN statement as follows:

EXPLAIN SELECT *
FROM address
WHERE phone = '223664661973';

This will result in the following:

To create an index for the values in the phone column of the address table, you use the following statement:

CREATE INDEX idx_address_phone 
ON address(phone);

Now, if you execute the query again, you will find that the database engine uses the index for lookup:

EXPLAIN SELECT *
FROM address
WHERE phone = '223664661973';

Output: