postgresql
  1. postgresql-multi-column-indexes
PreviousNext

Multi-column Indexes - (PostgreSQL Indexes)

Indexes in PostgreSQL are used to quickly locate data without having to scan all the rows in a table. When we create an index on a column(s) of a table, PostgreSQL creates an efficient data structure that stores the indexed values in a sorted order. In this tutorial, we'll discuss multi-column indexes in PostgreSQL.

Syntax

CREATE INDEX index_name ON table_name (col1, col2, col3, ...);
  • index_name: The name of the index to be created.
  • table_name: The name of the table on which the index is created.
  • col1, col2, col3, ...: The columns on which the multi-column index is created.

Example

Let's consider the following example where we have a table 'students' with columns 'id', 'name' and 'age':

CREATE TABLE students (
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL,
   age INT NOT NULL
);

INSERT INTO students (name, age) VALUES ('John', 22);
INSERT INTO students (name, age) VALUES ('Jane', 23);
INSERT INTO students (name, age) VALUES ('Alice', 22);

Now, we want to create a multi-column index on columns 'age' and 'name':

CREATE INDEX age_name_idx ON students (age, name);

Explanation

In the above example, we have a 'students' table with columns 'id', 'name' and 'age'. We created a multi-column index 'age_name_idx' on columns 'age' and 'name'. This means that the index is sorted first by the 'age' column and then by the 'name' column.

Use

A multi-column index can be used to speed up queries that involve the indexed columns. For example, if we want to run the following query:

SELECT id, name, age FROM students WHERE age = 22 AND name = 'John';

With the multi-column index on 'age' and 'name', PostgreSQL can quickly locate the required rows by searching on both columns simultaneously.

Important Points

  • A multi-column index can be created on any number of columns in a table.
  • The order of columns in the index matters. PostgreSQL uses the leftmost column(s) in the index to perform the initial sort, then uses the remaining columns to break ties.
  • A multi-column index can only be used for queries that use the leftmost columns in the index. So, if we have a multi-column index on columns 'age' and 'name', It can be used to speed up queries that involve conditions on both 'age' and 'name', or only 'age', but not only 'name'.
  • Creating too many indexes (including multi-column indexes) can have a negative impact on database performance.

Summary

In this tutorial, we discussed multi-column indexes in PostgreSQL. We covered the syntax, example, explanation, use, and important points of creating a multi-column index in PostgreSQL. With this knowledge, you can now create multi-column indexes on tables to improve the performance of your PostgreSQL database.

ReindexConstraints
Published on: