PostgreSQL Tutorial: CREATE INDEX

Summary: In this tutorial, you will learn about indexes and how to use the PostgreSQL CREATE INDEX statement to define a new index for a table.

Table of Contents

Phonebook analogy and index

Suppose you need to look up John Doe‘s phone number in a phone book. Assuming that the names on the phone book are in alphabetical order. To find John Doe‘s phone number, you first look for the page where the last name is Doe, then look for the first name John, and finally, get his phone number.

If the names on the phone book were not ordered alphabetically, you would have to go through all pages and check every name until you find John Doe’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.

By definition, an index is a separated data structure that speeds up the data retrieval on a table at the cost of additional writes and storage to maintain the index.

PostgreSQL CREATE INDEX overview

The following show the basic syntax of the CREATE INDEX statement:

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

In this 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 btree, hash, gist, spgist, gin, and brin. PostgreSQL uses btree by default.
• Finally, list one or more columns of the index. The ASC and DESC specify the sort order. The default sort order is ASC.

If a column contains NULL, you can specify NULLS FIRST or NULLS LAST option. The NULLS FIRST is the default when DESC is specified and NULLS LAST is the default when DESC is not specified.

To check if a query uses an index or not, you use the EXPLAIN statement.

PostgreSQL CREATE INDEX statement example

We will use the address table from the sample database for the demonstration.

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

SELECT * FROM address
WHERE phone = '223664661973';

It is obvious that the database engine has 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';

Here is the output:

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';

The following shows the output:

Summary

• An index is a separated data structure that speeds up the data retrieval on a table at the cost of additional writes and storage to maintain it.
• Use the CREATE INDEX statement to create an index.