Introduction to PostgreSQL CREATE INDEX statement

An index is a separate data structure that enhances the speed of data retrieval from a table, at the cost of additional writes and storage required to maintain it.

An index allows you to improve the query performance when using it appropriately, especially on large tables.

To create an index on one or more columns of a table, you use theCREATE INDEX statement.

Here’s the basic syntax of theCREATE INDEX statement:

CREATE INDEX [IFNOT EXISTS] index_nameON table_name(column1, column2, ...);

In this syntax:

• First, specify the index name after theCREATE INDEX clause.
• Second, use theIF NOT EXISTS option to prevent an error if the index already exists.
• Third, provide the table name to which the index belongs.
• Finally, list out one or more indexed columns inside the () after the table name.

Note that the syntax of theCREATE INDEX statement is more complex than this. We’ll cover additional features of theCREATE INDEX statement in the upcoming tutorials such asunique indexes,indexes on expressions,partial indexes, andmulticolumn indexes.

By default, theCREATE INDEX statement creates a B-tree index, which is appropriate for most cases. We’ll show you how to create otherindex types.

PostgreSQL CREATE INDEX statement example

We’ll use theaddress table from thesample database for the demonstration:

First,connect to the PostgreSQLdvdrentalsample database usingpsql:

psql -U postgres -d dvdrental

Second, execute the followingquery to find the address whose phone number is223664661973:

SELECT  address_id,  address,  district,  phoneFROM  addressWHERE  phone = '223664661973';

Output:

address_id |      address       | district  |    phone------------+--------------------+-----------+--------------         85 | 320 Baiyin Parkway | Mahajanga | 223664661973(1 row)

To find the row whose value in thephone column is223664661973, PostgreSQL must scan the entireaddress table.

Third, show the query plan using the followingEXPLAIN statement::

EXPLAIN SELECT  address_id,  address,  district,  phoneFROM  addressWHERE  phone = '223664661973';

Here is the output:

QUERY PLAN--------------------------------------------------------- Seq Scan on address  (cost=0.00..15.54 rows=1 width=45)   Filter: ((phone)::text = '223664661973'::text)(2 rows)

The output indicates that the query optimizer has to perform a sequential scan on theaddress table.

Fourth,create an index for the values in thephone column of theaddress table using theCREATE INDEX statement:

CREATE INDEX idx_address_phoneON address(phone);

When you run theCREATE INDEX statement, PostgreSQL scans theaddress table, extracts data from thephone column, and inserts it into the indexidx_address_phone.

This process is called an index build. By default, PostgreSQL allows reads from theaddress table and blocks write operations while building the index.

Fifth,show the indexes that belong to theaddress table from thepg_indexes:

SELECT  indexname,  indexdefFROM  pg_indexesWHERE  tablename= 'address';

Output:

indexname     |                                  indexdef-------------------+----------------------------------------------------------------------------- address_pkey      | CREATE UNIQUE INDEX address_pkey ON public.address USING btree (address_id) idx_fk_city_id    | CREATE INDEX idx_fk_city_id ON public.address USING btree (city_id) idx_address_phone | CREATE INDEX idx_address_phone ON public.address USING btree (phone)(3 rows)

The output shows that theidx_address_phone has been created successfully.

Two other indexesaddress_pkey andidx_fk_city_id were created implicitly when theaddress table was created.

More specifically, theaddress_pkey index was created for theprimary key columnaddress_id andidx_fk_city_id was created for theforeign key city_id column.

Fifth, execute the following query again:

EXPLAIN SELECT  address_id,  address,  district,  phoneFROM  addressWHERE  phone= '223664661973';

Output:

QUERY PLAN---------------------------------------------------------------------------------- Index Scan using idx_address_phone on address  (cost=0.28..8.29rows=1 width=45)   Index Cond: ((phone)::text ='223664661973'::text)(2 rows)

The output indicates that PostgreSQL uses the indexidx_address_phone for the lookup.

Summary

• Use theCREATE INDEX statement to create an index.
• Use theEXPLAIN statement to explain a query.

Last updated onFebruary 28, 2024

On this page

• Introduction to PostgreSQL CREATE INDEX statement
• PostgreSQL CREATE INDEX statement example
• Summary