PostgreSQL Tutorial: List Indexes

Summary: In this tutorial, you will learn how to list indexes from a PostgreSQL database by using either pg_indexes view or psql command.

Table of Contents

PostgreSQL does not provide a command like SHOW INDEXES to list the index information of a table or database.

However, it does provide you with access to the pg_indexes view so that you can query the index information.

If you use the psql program to interact with the PostgreSQL database, you can use the \d command to view the index information for a table.

PostgreSQL List Indexes using pg_indexes View

The pg_indexes view allows you to access useful information on each index in the PostgreSQL database. The pg_indexes view consists of five columns:

• schemaname: stores the name of the schema that contains tables and indexes.
• tablename: stores the name of the table to which the index belongs.
• indexname: stores the name of the index.
• tablespace: stores the name of the tablespace that contains indexes.
• indexdef: stores index definition command in the form of CREATE INDEX statement.

The following statement lists all indexes of the schema public in the current database:

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;

The partial output is:

To show all the indexes of a table, you use the following statement:

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'table_name';

For example, to list all the indexes for the customer table, you use the following statement:

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'customer';

Here is the output:

If you want to get a list of indexes for tables whose names start with the letter c, you can use the following query:

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename LIKE 'c%'
ORDER BY
    tablename,
    indexname;

The following shows the output:

PostgreSQL List Indexes using psql command

If you use psql to connect to a PostgreSQL database and want to list all indexes of a table, you can use the \d psql command as follows:

\d table_name

The command will return all information about the table including the table’s structure, indexes, constraints, and triggers.

For example, the following statement returns detailed information about the customer table:

\d customer

The output is:

As shown clearly in the output, you can find the index of the table under the indexes section.

In this tutorial, you have learned how to list all indexes from the PostgreSQL database by querying against the pg_indexes view.