PostgreSQL – CREATE INDEX
The PostgreSQL CREATE INDEX statement is essential for improving database performance, allowing faster data retrieval by creating indexes on specified columns. Indexes in PostgreSQL act like pointers, significantly reducing the time required for query processing, especially on large tables.
In this article, we will cover how to create an index in PostgreSQL, with examples showing various types, such as single-column, multicolumn, and unique indexes, to enhance our understanding of efficient query optimization.
What is an Index in PostgreSQL?
In PostgreSQL, an index is a data structure that enhances query performance by allowing the database to retrieve data faster. The PostgreSQL CREATE INDEX statement is an important command for improving database performance by creating indexes on specified columns. Just like an index in a book, an index in PostgreSQL acts as a pointer to the data location in the table, allowing quicker data lookups.
Why Use Indexes?
Using indexes can greatly improve query speed, especially on large tables. Without an index, PostgreSQL has to perform a sequential scan, checking each row individually—a slow process for big datasets. However, with an index, PostgreSQL can quickly locate and retrieve data, which is ideal for frequently queried columns.
Syntax
CREATE INDEX index_name ON table_name [USING method]
(
column_name [ASC | DESC] [NULLS {FIRST | LAST }],
...
);
Key Terms
- Index Name: Choose a meaningful and memorable name for the index.
- Table Name: Specify the table to which the index belongs.
- Index Method: Choose an index method such as ‘btree‘, ‘hash‘, ‘gist’, ‘spgist’, ‘gin’, or ‘brin’. PostgreSQL defaults to ‘btree’.
- Columns: List one or more columns to include in the index.
- Sort Order: Specify the sort order with ‘ASC’ (default) or ‘DESC’.
- Nulls Order: Specify ‘NULLS FIRST’ or ‘NULLS LAST’. ‘NULLS FIRST’ is the default with ‘DESC’, and ‘NULLS LAST’ is the default with ‘ASC’.
Types of Indexes in PostgreSQL
1. Single-Column Indexes
A single-column index is created for just one column, which is efficient if that column is frequently used in WHERE clauses.
CREATE INDEX idx_address_phone
ON address(phone);
2. Multicolumn Indexes
A multicolumn index is an index on more than one column, which is beneficial if your queries frequently filter by multiple columns
CREATE INDEX idx_customer_city
ON customer(city, country);
3. Unique Indexes
A unique index ensures that all values in the index are unique. It’s often used on columns that require unique values, like an email or username.
CREATE UNIQUE INDEX idx_email_unique
ON users(email);
4. Partial Indexes
Partial indexes include only rows that meet specific criteria, which is helpful for indexing frequently queried subsets of data.
CREATE INDEX idx_active_users
ON users(status)
WHERE status = 'active';
Examples of PostgreSQL CREATE INDEX
For the purpose of demonstration, we will use the address table from the sample database for the demonstration. Let us look at an example of CREATE INDEX Statement in PostgreSQL to better understand the concept.
1. Querying a Phone Number Without an Index
Suppose we want to find an address with a specific phone number in the address table. The following query finds the address whose phone number is 223664661973.
Query:
SELECT * FROM address
WHERE phone = '223664661973';
It is obvious that the database engine had to scan the whole address table to look for the address because there is no index available for the phone column. To show the query plan, we use the EXPLAIN statement as follows.
EXPLAIN SELECT * FROM address
WHERE phone = '223664661973';
Output
Explanation:
This output shows a sequential scan on the table, which is slow for large tables.
2. Creating an Index on the Phone Column
Creating an index on the phone column allows PostgreSQL to store the phone numbers in a structured way, which significantly speeds up search queries on this column by avoiding a full table scan.
Query:
CREATE INDEX idx_address_phone
ON address(phone);
Once the index is created, PostgreSQL uses the idx_address_phone index for the lookup, making the query faster.
EXPLAIN SELECT * FROM address
WHERE phone = '223664661973';
Output
Explanation:
With the index, PostgreSQL performs an index scan instead of a sequential scan, resulting in a significant performance boost. This time, the output will show that PostgreSQL uses the index for a more efficient lookup, significantly speeding up the query.
PostgreSQL Indexing Tips
- Use Indexes on Frequently Queried Columns: Index columns that are often used in WHERE clauses, joins, or sorting.
- Avoid Indexes on Small Tables: For small tables, sequential scans may be faster than using an index.
- Choose the Right Index Type: Consider the type of data and query when selecting an index type. The btree index is the default and works well for most use cases.
- Be Cautious with Update-Heavy Tables: Indexes can slow down insert and update operations, so avoid creating too many indexes on frequently updated tables.
- Use Partial Indexes for Selective Queries: Partial indexes can reduce index size and improve query performance by indexing only a subset of rows.
Conclusion
In this PostgreSQL indexing tutorial, we’ve explained how to use indexes effectively to enhance PostgreSQL query performance optimization. By creating indexes on key columns, we can improve query speed with PostgreSQL index and streamline data retrieval. For large datasets, indexing is a powerful tool to make your queries faster and more efficient, ensuring optimal database performance.
FAQs
How to create an index in Postgres?
To create an index in PostgreSQL, use the
CREATE INDEXcommand with the syntax:CREATE INDEX index_name ON table_name(column_name);. Indexes help speed up data retrieval by reducing the amount of data scanned during queries.
What are indexes in PostgreSQL?
Indexes in PostgreSQL are special database structures that improve query performance by allowing faster search and retrieval of data. They function like a catalog or roadmap for the database, significantly reducing query response time.
How to create an index?
You can create an index in PostgreSQL using
CREATE INDEXfollowed by the index name, table name, and column(s) you wish to index. For example:CREATE INDEX idx_column ON table_name(column_name);
