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 [ CONCURRENTLY ] [ [ 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 unless you specify IF NOT EXISTS, in which case it is required. Without IF NOT EXISTS, PostgreSQL can generate the name.
• 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.
• IF NOT EXISTS suppresses an error if the index name already exists, but it does not check whether the existing index has the requested definition.

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.

Create an index without blocking writes

A regular CREATE INDEX allows reads but blocks writes to the table while the index is built. For a table that must continue accepting writes, use CONCURRENTLY:

CREATE INDEX CONCURRENTLY address_postal_code_idx
ON address (postal_code);

CREATE INDEX CONCURRENTLY cannot run inside a transaction block and performs more work than a regular index build. It is not supported directly on partitioned tables; concurrently index each partition first, then create the parent partitioned index without CONCURRENTLY. A failed concurrent build can leave an invalid index. See PostgreSQL’s concurrent index build notes and SQLiz’s REINDEX and DROP INDEX guides for recovery options.

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.

The planner chooses a plan from estimated costs, so creating an index does not guarantee that every query will use it. For a selective lookup, an index scan may be cheaper than a sequential scan; small tables or queries that return many rows may still use a sequential scan. See PostgreSQL’s Using EXPLAIN guide. One possible plan for the sample query is:

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.