5.34. PostgreSQL index

5.34.1. CREATE INDEX command ¶

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

CREATE INDEX index_name ON table_name;

5.34.2. Index type ¶

单列索引

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

CREATE INDEX index_name
ON table_name (column_name);

组合索引

A composite index is an index created 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 combined index, the index must be in the WHERE A column that is used very frequently in the filter condition of the clause.

If only one column is used, select a single-column index, and use a combined index if there are multiple columns.

唯一索引

Unique indexes are used 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);

局部索引

A local index is an index built on a subset of a table; the subset is defined on a conditional expression. The index contains only rows that meet the criteria. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (conditional_expression);

隐式索引

An implicit index is an index created automatically by the database server when an object is created. Indexes are automatically created as primary key constraints and unique constraints.

5.34.3. Example ¶

The following example will be found in the COMPANY Tabular SALARY Create an index on the column:

# CREATE INDEX salary_index ON COMPANY (salary);

Now, use \d company Command list COMPANY All indexes of the table:

# \d company

The results are as follows company_pkey Is an implicit index that is created when the table creates the table:

runoobdb=# \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:

runoobdb=# \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)

5.34.4. DROP INDEX (delete index) ¶

An index can use PostgreSQL’s DROP Command to delete.

DROP INDEX index_name;

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

# DROP INDEX salary_index;

After you delete it, you can see salary_index Has been deleted from the list of indexes:

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

5.34.5. Under what circumstances should indexes be avoided? ¶

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

When using indexes, you need to consider the following guidelines:

• Indexes should not be used on smaller tables.

• Indexes should not be used on tables that have frequent bulk updates or inserts.

• Indexes should not be used when they contain a large number of NULL On the column of the.

• Indexes should not be used on columns that operate frequently.