Summary
: in this tutorial, you will learn how to use the PostgreSQL
CREATE INDEX
statement to define a new index for a table.
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 the
CREATE INDEX
statement.
Here’s the basic syntax of the
CREATE INDEX
statement:
CREATE INDEX [IF NOT EXISTS] index_name
ON table_name(column1, column2, ...);In this syntax:
CREATE INDEX
clause.
IF NOT EXISTS
option to prevent an error if the index already exists.
Note that the syntax of the
CREATE INDEX
statement is more complex than this. We’ll cover additional features of the
CREATE INDEX
statement in the upcoming tutorials such as
unique indexes
,
indexes on expressions
,
partial indexes
, and
multicolumn indexes
.
By default, the
CREATE INDEX
statement creates a B-tree index, which is appropriate for most cases. We’ll show you how to create other
index types
.
PostgreSQL CREATE INDEX statement example
We’ll use the
address
table from the
sample database
for the demonstration:
dvdrental
sample database
using
psql
:
psql -U postgres -d dvdrental
Second, execute the following
query
to find the address whose phone number is
223664661973
:
SELECT
address_id,
address,
district,
phone
address
WHERE
phone = '223664661973';Output:
address_id | address | district | phone
------------+--------------------+-----------+--------------
85 | 320 Baiyin Parkway | Mahajanga | 223664661973
(1 row)
To find the row whose value in the
phone
column is
223664661973
, PostgreSQL must scan the entire
address
table.
Third, show the query plan using the following
EXPLAIN
statement::
EXPLAIN SELECT
address_id,
address,
district,
phone
address
WHERE
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 the
address
table.
Fourth,
create an index
for the values in the
phone
column of the
address
table using the
CREATE INDEX
statement:
CREATE INDEX idx_address_phone
ON address(phone);
When you run the
CREATE INDEX
statement, PostgreSQL scans the
address
table, extracts data from the
phone
column, and inserts it into the index
idx_address_phone
.
This process is called an index build. By default, PostgreSQL allows reads from the
address
table and blocks write operations while building the index.
Fifth,
show the indexes
that belong to the
address
table from the
pg_indexes
:
SELECT
indexname,
indexdef
pg_indexes
WHERE
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 the
idx_address_phone
has been created successfully.
Two other indexes
address_pkey
and
idx_fk_city_id
were created implicitly when the
address
table was created.
More specifically, the
address_pkey
index was created for the
primary key
column
address_id
and
idx_fk_city_id
was created for the
foreign key
city_id column.
Fifth, execute the following query again:
EXPLAIN SELECT
address_id,
address,
district,
phone
address
WHERE
phone = '223664661973';Output:
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using idx_address_phone on address (cost=0.28..8.29 rows=1 width=45)
Index Cond: ((phone)::text = '223664661973'::text)
(2 rows)