PostgreSQL Create Indexes

In PostgreSQL, you can add indexes to a table using CREATE INDEX.

What are indexes

In a simple analogy, an index can be thought of as a directory of dictionaries. With the table of contents, you can find words faster. With indexes, you can retrieve rows from a table faster. An index is an ordered data structure that requires additional space for storage.

PostgreSQL CREATE INDEX syntax

Here is the simple syntax of PostgreSQL CREATE INDEX statement:

CREATE [ UNIQUE ] INDEX [ [ IF NOT EXISTS ] name ]
    ON table_name [ USING method ]
(
    column_name [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
    [, ...]
);

Explanation:

• The name is the name of the index to create. It is optional. If you do not specify an index name, PostgreSQL will automatically generate one.
• The table_name is the name of the table for which the index is to be created.
• The method is the name of the index method, including btree, hash, gist, spgist, gin, and brin. btree is the default method. You can check Index Types to learn more.
• The column_name is the name of the column to be indexed.
• The [ ASC | DESC ] specifies whether the sorting is ascending or descending. It is optional and the default value is ASC.
• The NULLS FIRST or NULLS LAST specifies that null values ​​come before or after non-null values ​​when sorting. When DESC specified, NULLS FIRST is the default, otherwise NULLS LAST is the default.
• The UNIQUE Instructs to create a unique index.
• The IF NOT EXISTS instructs the index to be created only if the specified index name does not exist.

PostgreSQL automatically creates indexes on primary key columns.

To check whether a query uses an index, use this EXPLAIN statement.

If you use more than one column in an index, the index is called multicolumn index or compound index.

PostgreSQL CREATE INDEX Examples

We will use the address table from PostgreSQL Sakila sample database to demonstrate.

The following select rows having postal code x:

SELECT * FROM address
WHERE postal_code = 'x';

Since no index is available for this postal_code column, PostgreSQL has to perform a full table scan. You can verify this by seeing the query plan.

To display the execution plan, use the following EXPLAIN statement:

EXPLAIN
SELECT * FROM address
WHERE postal_code = 'x';

                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on address  (cost=0.00..13.54 rows=1 width=161)
   Filter: ((postal_code)::text = 'x'::text)

To create an index on the postal_code column of the address table, use the following statement:

CREATE INDEX ON address (postal_code);

No index name is specified here, PostgreSQL will automatically generate an index name: address_postal_code_idx. The format is the table name followed by the column name and with the idx suffix.

Now, looking at the execution plan again, you will see that the postgresql uses the index for lookups:

EXPLAIN
SELECT * FROM address
WHERE postal_code = 'x';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161)
   Index Cond: ((postal_code)::text = 'x'::text)

Here, it is explained that PostgreSQL uses the using address_postal_code_idx index for search.

Conclusion

In PostgreSQL, you can add indexes to a table using the CREATE INDEX statement.