Easy Tutorial
Postgresql Like Postgresql Syntax Postgresql With Postgresql Null Postgresql Lock Postgresql View Postgresql Privileges Postgresql Limit Postgresql And Or Clauses Postgresql Autoincrement Postgresql Expressions Postgresql Data Type Postgresql Constraints Postgresql Where Clause Postgresql Drop Database Postgresql Select Database Windows Install Postgresql Postgresql Select Linux Install Postgresql Postgresql Join Postgresql Index Postgresql Tutorial Postgresql Schema Postgresql Trigger Postgresql Delete Postgresql Update Postgresql Alias Postgresql Union Postgresql Alter Table Postgresql Functions Postgresql Truncate Table Postgresql Group By Postgresql Sub Queries Postgresql Having Postgresql Insert Into Postgresql Create Table Postgresql Order By Postgresql Datetime Postgresql Create Database Postgresql Drop Table Postgresql Operators Postgresql Transaction Postgresql Distinct Mac Install Postgresql
❮ Postgresql Join Postgresql Tutorial ❯

PostgreSQL Indexes

An index is a special lookup table that the search engine uses to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to the index in the back of a book.

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all the topics alphabetically and then you turn to the page numbers listed in the index.

Indexes help speed up SELECT queries and WHERE clauses, but they slow down data input with the use of UPDATE and INSERT statements. Indexes can be created or removed without affecting the data.

The CREATE INDEX command is used to create indexes, which allows naming the index, specifying the table and which column or columns to index, 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 the column or combination of columns.

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 created based on only one column of the table. The basic syntax is as follows:

CREATE INDEX index_name
ON table_name (column_name);

Composite Index

A composite index is created based on more than one column of the 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 frequently used in the WHERE clause's filtering conditions.

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

Unique Index

A unique index is used 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 built on a subset of a table; the subset is defined by a conditional expression. The index contains only entries that satisfy the condition. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (conditional_expression);

Implicit Index

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

Example

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

# CREATE INDEX salary_index ON COMPANY (salary);

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

# \d company

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

tutorialprodb=# \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 list all indexes in the database using the \di command:

tutorialprodb=# \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 the DROP command in PostgreSQL.

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 the salary_index has been removed from the list of indexes:

tutorialprodb=# \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 the purpose of indexes is 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 smaller 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 manipulated columns.
❮ Postgresql Join Postgresql Tutorial ❯