How to Use PostgreSQL List Index

Introduction

If you’re using indexes on your PostgreSQL tables, you’ll probably want to know how to obtain a list of these indexes. There’s no such thing as a PostgreSQL LIST INDEX command; instead, we can use the pg_indexes view to accomplish this task. This tutorial will show you how to list indexes in PostgreSQL using both the pg_indexes view and the psql utility.

Prerequisites

In order to follow along with the examples we discuss in this tutorial, you’ll need to have PostgreSQL installed and configured. This process should be running in the background. If you’re running either Linux or Windows, you can download PostgreSQL here.

What is a PostgreSQL Index?

PostgreSQL indexes are used to speed up database searches. You can think of a PostgreSQL index as a pointer to some particular piece of data within a PostgreSQL database table.

If you want to learn how to create an index in PostgreSQL, you can reference this article on PostgreSQL Create Index.

pg_indexes in PostgreSQL

As we mentioned earlier, PostgreSQL does not have a direct command to list information on a particular database or database table; however, it does provide the pg_indexes view, which allows us to query for index information. The pg_indexes view offers useful information on every index within a particular database in PostgreSQL. This view is composed of five columns:

• The schemaname: This column stores the schema name that contains indexes and tables.
• tablename: This column stores the table name for which the index was created.
• indexname: As the name implies, this column stores the index name.
• tablespace: This column holds the tablespace that contains the indexes.
• indexdef: This column contains the index definition in the form of a CREATE INDEX statement.

Let’s use the pg_indexes view to see if we can get information about our ‘public’ schema. To do this, we’ll be using a query that will list all indexes of the schema within the current database. However, we first have to log in to our server as the superuser postgres, using the following command:

Then, we can use the following query:

In our results, we should see something like the following:

We can also show the existing indexes within a table using the following query:

In the query shown above, we will only be retrieving the indexname and indexdef (index definition) columns from the table client_info.

The results will look something like this:

We can also perform a SELECT operation using the LIKE clause, where we retrieve any indexes within the table with a name whose first letter matches certain criteria.

Before we do this, let’s take a step back and list all the tables in our database using the PostgreSQL meta-command \dt.

Shown below is the result of this command:

Now, let’s perform our query:

The above query will retrieve values of tablename, indexname and the indexdef from any tables having a name that starts with the character c.

Since we have two tables with a name that starts with “c”, our results will look like this:

psql meta-command d in PostgreSQL

Now that we’ve seen how to use the pg_indexes view to list PosgtreSQL indexes for a table, we’ll look at another way to accomplish the same task using the PostgreSQL meta-command d.

If we connect to a PostgreSQL database using the psql command-line utility, we can list all existing indexes within a table using the command shown below. Be sure you’ve logged into your PostgreSQL database using psql before trying this command:

Here’s an example of this command used against the table client_info:

The output of this command should look something like the following:

We can see in this output that the index is listed in the “Indexes” section.

Conclusion

When you’re managing various tables and indexes in PostgreSQL, it’s important to be able to obtain information on all of your indexes. Although there’s no PostgreSQL LIST INDEX command to provide a listing of indexes, two other simple methods are available to return the information you need. Using the examples in this tutorial as a guide, you’ll be able to retrieve index information for any of your PostgreSQL tables.