Example of Index in PostgreSQL

Let’s create an example table, insert some data into it, and then create some indexes.

Create a table

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    publication_year INT
);

Insert some data into it.

INSERT INTO books (title, author, publication_year) VALUES
    ('To Kill a Mockingbird', 'Harper Lee', 1960),
    ('1984', 'George Orwell', 1949),
    ('Pride and Prejudice', 'Jane Austen', 1813);

Check whether the table is successfully created or not by executing the below command:

SELECT * FROM books;

Output:

Create an index on the author column:

CREATE INDEX idx_author ON books (author);

After executing this statement, PostgreSQL will set up an index called “idx_author” on the author field of the books table. This index will be useful for queries containing filtering and searching functions on records based on the author column.

EXPLAIN statement:

In PostgreSQL, the EXPLAIN statement is used to obtain the execution plan for a query without it being executed. It offers an insight into how PostgreSQL formulates the query, like which indexes to use, how to access the data, and any other operation it performs.

We can use the EXPLAIN command to see the query plan, as we can see in the below command:

EXPLAIN SELECT * FROM books WHERE author = 'Harper Lee';

• EXPLAIN: This command is used to generate the execution plan for the following query.
• SELECT * FROM books WHERE author = ‘Harper Lee’: This is the query you want to analyze. It takes all columns (*) from the books table where the author = ‘Harper Lee‘.

Output:

In relational databases, PostgreSQL is recognized as one of the most reliable database management systems which is used for many web applications, mobile, and analytics applications. Indexing is one of the most important aspects that has a lot to do with the efficiency of the database.

In this article, we focus on indexes in PostgreSQL, explain their significance, examine their types, learn how to create them and discuss ways of optimization.