5.34. PostgreSQL index

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

Taking the catalogue page (index) of the Chinese dictionary as an example, we can quickly find the words we need according to the catalogue (index) sorted by pinyin, strokes, partial radicals, etc.

Indexing helps speed up SELECT Query and WHERE Clause, but it slows down the use of UPDATE And INSERT The data input in the statement. Indexes can be created or deleted, but the data is not affected.

Use CREATE INDEX Statement, which allows you to name the index, specify the table and the column or columns to be indexed, and indicate whether the index is in ascending or descending order.

The index can also be unique, as opposed to UNIQUE Similarly, constraints prevent duplicate entries on a column or on a combination of columns.

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.