PostgreSQL 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.

Phonebook analogy and index

Assuming that you need to look up for John Doe’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 Doe, then look for first name John, 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 John Doe’s phone number. This is called 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.

An index is a separated data structure e.g., B-Tree that speeds up the data retrieval on a table at the cost of additional writes and storage to maintain it.

PostgreSQL CREATE INDEX overview

A simple version of CREATE INDEX statement is as follows:

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

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.
  • Fourth, list one or more columns that 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-nulls. 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 example

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

address table

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

SELECT * FROM address WHERE phone = '223664661973';
Code language: JavaScript (javascript)

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';
Code language: JavaScript (javascript)

Here is the output:

PostgreSQL CREATE INDEX - sequential scan example

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';
Code language: JavaScript (javascript)

The following shows the output:

PostgreSQL CREATE INDEX - index scan example

In this tutorial, you have learned about indexes and how to use the PostgreSQL CREATE INDEX to define an index for a table.

Was this tutorial helpful ?