How to use index in select query in postgresql

Indexing is a technique in databases that can help speed up searches. When managing a database, depending on the workload that will run on the database, you might decide to create indexes for certain tables.

In this example, let’s imagine you manage a library, and thus we have tables to manage books, customers, and employees.

Each of these tables may have indexes depending on their workloads.

Later you might want to see what indexes exist in that table. There are 2 different methods to list down all the indexes present in the Postgres database - using command and using SQL query.

In this tutorial, we will examine these two techniques and understand their differences.

The command

Step 1 - Open your terminal and connect it to your desired Postgres database. Follow our guide to set up the connection with the database.

Step 2 - To list down all the indexes, use the command.

This command returns the table of all indexes present in the database consisting of details like Schema, Name, Type, Owner, and Table.

The command (docs) can acce

postgres list all indexes with size

psql drop all indexes