YouTip

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

Take the directory page (index) of a Chinese dictionary as an example. We can quickly find the desired character through directories (indexes) sorted by pinyin, strokes, radicals, etc.

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

Use the CREATE INDEX statement to create an index. It allows naming the index, specifying the table and one or more columns to be indexed, and indicating whether the index is in ascending or descending order.

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

CREATE INDEX Command

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

CREATE INDEX index_name ON table_name;

Index Types

Single-Column Index

A single-column index is an index created based on only one column of a table. The basic syntax is as follows:

CREATE INDEX index_name ON table_name (column_name);

Composite Index

A composite index is an index created based on multiple columns of a table. The basic syntax is 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 on columns that are used very frequently in the filtering conditions of the WHERE clause.

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

Unique Index

Using a unique index is not only for performance but also for data integrity. A unique index does 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);

Partial Index

A partial index is an index built on a subset of a table; the subset is defined by a conditional expression. The index only contains rows that satisfy the condition. The basic syntax is as follows:

CREATE INDEX index_name ON table_name(column_list) WHERE condition;

Here, index_name is the name of the index you want to create, table_name is the name of the table containing the columns you want to index, column_list is the list of columns you want to index, and condition is a boolean expression that defines which rows will be included in the index.

Implicit Index

In PostgreSQL, an implicit index is an index automatically created by the database server when an object is created. This type of index is typically automatically created for primary key constraints and unique constraints. When a column is declared as a primary key, unique constraint, or foreign key when creating a table, PostgreSQL automatically creates an implicit index for that column. The benefit of doing this is that it simplifies index management and improves database performance.

For example, if a column named "userid" is declared as the primary key when creating a table named "users", PostgreSQL will automatically create an implicit index for the "userid" column. This means that when inserting new records, the database will automatically generate a unique index value for the "userid" column.

The creation and management of implicit indexes are handled automatically by PostgreSQL, and users do not need to intervene manually. This makes database management simpler and more efficient.

Examples

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

# CREATE INDEX salary_index ON COMPANY (salary);

Now, use the d company command to list all indexes of the COMPANY table:

# d company

The result is as follows, where company_pkey is an implicit index created when the table was created:

tutorialdb=# 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:

tutorialdb=# 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 previously created index:

# DROP INDEX salary_index;

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

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

When Should Indexes Be Avoided?

Although indexes are intended to improve database performance, there are several situations where indexes should be avoided.

When using indexes, consider the following guidelines:

• Indexes should not be used on small tables.
• Indexes should not be used on tables with 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 frequently operated columns.