> This page location: PostgreSQL Indexes > Create Indexes > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL CREATE INDEX Statement **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: ```sql CREATE INDEX [IF NOT EXISTS] index_name ON table_name(column1, column2, ...); ``` In this syntax: - First, specify the index name after the `CREATE INDEX` clause. - Second, use the `IF 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 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](https://neon.com/postgresql/postgresql-indexes/postgresql-unique-index), [indexes on expressions](https://neon.com/postgresql/postgresql-indexes/postgresql-index-on-expression), [partial indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-partial-index), and [multicolumn indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-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](https://neon.com/postgresql/postgresql-indexes/postgresql-index-types). ## PostgreSQL CREATE INDEX statement example We'll use the `address` table from the [sample database](../postgresql-getting-started/postgresql-sample-database) for the demonstration: ![address table](https://neon.com/postgresqltutorial/address.png) First, [connect to the PostgreSQL](../postgresql-getting-started/connect-to-postgresql-database) `dvdrental` [sample database](../postgresql-getting-started/postgresql-sample-database) using `psql`: ```bash psql -U postgres -d dvdrental ``` Second, execute the following [query](../postgresql-tutorial/postgresql-select) to find the address whose phone number is `223664661973`: ```sql SELECT address_id, address, district, phone FROM address WHERE phone = '223664661973'; ``` Output: ```text 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:: ```sql EXPLAIN SELECT address_id, address, district, phone FROM address WHERE phone = '223664661973'; ``` Here is the output: ```text 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](https://neon.com/postgresql/postgresql-indexes/postgresql-create-index) for the values in the `phone` column of the `address` table using the `CREATE INDEX` statement: ```sql 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](https://neon.com/postgresql/postgresql-indexes/postgresql-list-indexes) that belong to the `address` table from the `pg_indexes`: ```sql SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'address'; ``` Output: ```text 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](../postgresql-tutorial/postgresql-primary-key) column `address_id` and `idx_fk_city_id` was created for the [foreign key](../postgresql-tutorial/postgresql-foreign-key) city_id column. Fifth, execute the following query again: ```sql EXPLAIN SELECT address_id, address, district, phone FROM 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) ``` The output indicates that PostgreSQL uses the index `idx_address_phone` for the lookup. ## Summary - Use the `CREATE INDEX` statement to create an index. - Use the `EXPLAIN` statement to explain a query. --- ## Related docs (PostgreSQL Indexes) - [Unique Indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-unique-index) - [Indexes on Expressions](https://neon.com/postgresql/postgresql-indexes/postgresql-index-on-expression) - [Partial Indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-partial-index) - [Multicolumn Indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-multicolumn-indexes) - [Reindexing with REINDEX](https://neon.com/postgresql/postgresql-indexes/postgresql-reindex) - [Display Indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-list-indexes) - [Drop Indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-drop-index) - [Types of Indexes](https://neon.com/postgresql/postgresql-indexes/postgresql-index-types) - [Full Text Search](https://neon.com/postgresql/postgresql-indexes/postgresql-full-text-search) - [JSON Index](https://neon.com/postgresql/postgresql-indexes/postgresql-json-index)