The CREATE INDEX statement in PostgreSQL is used to create an index on one or more columns of a table. Indexes improve the speed of data retrieval operations, especially for queries that frequently use the WHERE, JOIN or ORDER BY clauses.
- Improve query performance and Speed up data retrieval.
- Optimize searches on frequently used columns.
- Enhance the performance of sorting and join operations.
Syntax
CREATE INDEX index_name
ON table_name (column_name);
Where:
- index_name: The name of the index.
- table_name: The table on which the index is created.
- column_name: The column to index.
Working
Consider the following customers table for the examples below:
Example 1: Create an Index on a Single Column
The following statement creates an index on the customer_name column.
Query:
CREATE INDEX idx_customer_name
ON Customers (customer_name);
Output:
Example 2: Create an Index on Multiple Columns
The following statement creates a composite index on the city and email columns.
Query:
CREATE INDEX idx_city_email
ON Customers (city, email);
Output: