PostgreSQL Indexes

Indexes are a special table query that accelerates search engine data retrieval. Simply put, an index is a pointer to the data in the table. An index in a database is very similar to the index directory in a book.

Using the catalog page (index) of a Chinese dictionary as an example, we can quickly find the needed character by sorting the catalog (index) by pinyin, strokes, radicals, etc.

Indexes help speed up SELECT queries and WHERE clauses, but they will slow down data input when using UPDATE and INSERT statements. Indexes can be created or deleted, but they will not affect the data.

The CREATE INDEX statement is used to create indexes, which allow named indexes, specify tables and the columns to be indexed, and indicate whether the index is in ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, preventing duplicate entries on columns or column combinations.

CREATE INDEX Command

The syntax for CREATE INDEX (create index) is as follows:

CREATE　INDEX　index_name　ON　table_name;

Index Type

Single-column Index

Single-column indexes are indexes created based on a single column of a table, with the basic syntax as follows:

CREATE　INDEX　index_name
ON　table_name　(column_name);

Composite Index

Composite indexes are indexes created based on multiple columns of a table, with the basic syntax as follows:

CREATE　INDEX　index_name
ON　table_name　(column1_name,　column2_name);

Whether it is a single-column index or a composite index, the index must be a column that is used very frequently in the WHERE clause filter conditions.

If only one column is used, choose a single-column index, and if multiple columns are used, use a composite index.

Unique Index

The use of unique indexes is not only for performance but also for data integrity. Unique indexes do not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE　UNIQUE　INDEX　index_name
on　table_name　(column_name);

Local Index

Local indexes are indexes built on the subset of a table; the subset is defined by a conditional expression. Indexes only contain rows that meet the conditions. The basic syntax is as follows:

CREATE　INDEX　index_name
on　table_name　(conditional_expression);

Implicit Index

Implicit indexes are indexes automatically created by the database server when objects are created. Indexes are automatically created for primary key constraints and unique constraints.

Online Example

The following example creates an index on the SALARY column of the COMPANY table:

#　CREATE　INDEX　salary_index　ON　COMPANY　(salary);

Now, using 　\d company The command lists all indexes of the COMPANY table:

#　\d　company

The results obtained are as follows, company_pkey is an implicit index, which is created when the table is created:

w3codeboxdb=#　\d　company
　　　　　　　　　　　　　　　　　　Table　"public.company"
　Column　　|　　　　　Type　　　　　　|　Collation　|　Nullable　|　Default　
---------+---------------+-----------+----------+---------
　id　　　　　　|　integer　　　　　　　|　　　　　　　　　　　|　not　null　|　
　name | text | | | not null　
　age | integer | | | not null　
　address | character(|50) | | | |　
　salary | real | | |　
Indexes:
　　　　"company_pkey" PRIMARY KEY, btree (id)
　　　　"salary_index" btree (salary)

You can use the \di command to list all indexes in the database:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　List of relations
　Schema | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
　public | salary_index | index | postgres | company
(3　rows)

DROP INDEX (delete index)

An index can be deleted using PostgreSQL's DROP command.

DROP INDEX index_name;

You can use the following statement to delete the index created previously:

# DROP INDEX salary_index;

After deletion, you can see that salary_index has been removed from the list of indexes:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　List of relations
　Schema | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
(2　rows)

In what situations should indexes be avoided?

Although the purpose of indexes is to improve database performance, there are several situations where the use of indexes should be avoided.

When using indexes, the following criteria should be considered:

• Indexes should not be used on small tables.

• Indexes should not be used on tables that have frequent large-scale update or insert operations.

• Indexes should not be used on columns with a large number of NULL values.

• Indexes should not be used on columns that are frequently operated on.