5 Ways to List Indexes in PostgreSQL

Posted on by Ian

There are several ways that we can list all indexes in PostgreSQL. These include utilizing various system catalog views and psql commands. Below are five options for returning a list of indexes in PostgreSQL, along with examples of how to filter the results by index name, table name, and other criteria.

1. Use pg_indexes View

The pg_indexes system view provides information about all indexes in the current database.

List All Indexes

SELECT *
FROM pg_indexes;

Example output:

 schemaname |        tablename         |                   indexname                    | tablespace |                                                                  indexdef                                                                   
------------+--------------------------+------------------------------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------
 public     | idiots                   | idiots_pkey                                    |            | CREATE UNIQUE INDEX idiots_pkey ON public.idiots USING btree (idiotid)
 public     | departments              | departments_pkey                               |            | CREATE UNIQUE INDEX departments_pkey ON public.departments USING btree (department_id)
...

Running it like that produces a long list of indexes, and so I’ve only put the first two here. However, we can filter the results by index name, table name, etc

Filter by Index Name

To narrow down results to a specific index, for example, an index named idx_open_tasks:

SELECT *
FROM pg_indexes
WHERE indexname = 'idx_open_tasks';

Example output:

 schemaname | tablename |   indexname    | tablespace |                                                      indexdef                                                       
------------+-----------+----------------+------------+---------------------------------------------------------------------------------------------------------------------
 public     | tasks     | idx_open_tasks |            | CREATE INDEX idx_open_tasks ON public.tasks USING btree (task_name, due_date) WHERE ((status)::text = 'Open'::text)

Filter by Table Name

To find all indexes on a table called tasks:

SELECT *
FROM pg_indexes
WHERE tablename = 'tasks';

Example output:

 schemaname | tablename |   indexname    | tablespace |                                                      indexdef                                                       
------------+-----------+----------------+------------+---------------------------------------------------------------------------------------------------------------------
 public     | tasks     | tasks_pkey     |            | CREATE UNIQUE INDEX tasks_pkey ON public.tasks USING btree (task_id)
 public     | tasks     | idx_open_tasks |            | CREATE INDEX idx_open_tasks ON public.tasks USING btree (task_name, due_date) WHERE ((status)::text = 'Open'::text)
 public     | tasks     | idx_all_tasks  |            | CREATE INDEX idx_all_tasks ON public.tasks USING btree (task_name, due_date)

2. Use pg_class and pg_index System Catalogs

The pg_class system catalog holds information about tables and indexes, while pg_index provides more detailed index metadata. We can run a query that joins these tables to get information about indexes and their tables.

List All Indexes

SELECT c.relname AS index_name, t.relname AS table_name
FROM pg_class c
JOIN pg_index i ON c.oid = i.indexrelid
JOIN pg_class t ON i.indrelid = t.oid
WHERE c.relkind = 'i';

Example output:

                   index_name                   |        table_name        
------------------------------------------------+--------------------------
 pg_toast_16391_index                           | pg_toast_16391
 idiots_pkey                                    | idiots
 departments_pkey                               | departments
...

This produces quite a long list, so I’ve included just a sample here.

Filter by Index Name

To narrow down by index name:

SELECT c.relname AS index_name, t.relname AS table_name
FROM pg_class c
JOIN pg_index i ON c.oid = i.indexrelid
JOIN pg_class t ON i.indrelid = t.oid
WHERE c.relkind = 'i' AND c.relname = 'idiots_pkey';

Example output:

 index_name  | table_name 
-------------+------------
 idiots_pkey | idiots

Filter by Table Name

To narrow down by table name tasks:

SELECT c.relname AS index_name, t.relname AS table_name
FROM pg_class c
JOIN pg_index i ON c.oid = i.indexrelid
JOIN pg_class t ON i.indrelid = t.oid
WHERE c.relkind = 'i' AND t.relname = 'tasks';

Example output:

   index_name   | table_name 
----------------+------------
 tasks_pkey     | tasks
 idx_open_tasks | tasks
 idx_all_tasks  | tasks

3. Use pg_stat_user_indexes

This view provides statistics about user-defined indexes and includes useful performance data like how often an index is used.

List All Indexes

SELECT indexrelname, relname
FROM pg_stat_user_indexes;

Example output:

      indexrelname       |      relname       
-------------------------+--------------------
 idiots_pkey             | idiots
 person_pkey             | person
...

Filter by Index Name

To narrow down by index name idx_open_tasks:

SELECT indexrelname, relname
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_open_tasks';

Example output:

  indexrelname  | relname 
----------------+---------
 idx_open_tasks | tasks

Filter by Table Name

To narrow down by table name movies:

SELECT indexrelname, relname
FROM pg_stat_user_indexes
WHERE relname = 'movies';

Example output:

 indexrelname | relname 
--------------+---------
 movies_pkey  | movies

4. Use \di Command in psql

In PostgreSQL’s psql command-line tool, you can use the \di command to list all indexes in the database.

List All Indexes

\di

Example output:

                            List of relations
 Schema |          Name           | Type  |  Owner   |       Table        
--------+-------------------------+-------+----------+--------------------
 public | actors_pkey             | index | postgres | actors
 public | authors_pkey            | index | postgres | authors
...

Filter by Index Name

To narrow down to the idx_open_tasks index:

\di idx_open_tasks

Example output:

                List of relations
 Schema |      Name      | Type  | Owner  | Table 
--------+----------------+-------+--------+-------
 public | idx_open_tasks | index | barney | tasks

Filter by Name Includes…

We can use the asterisk wildcard (*) to get indexes that include tasks in their name:

\di *tasks*

Output:

                List of relations
 Schema |      Name      | Type  | Owner  | Table 
--------+----------------+-------+--------+-------
 public | idx_all_tasks  | index | barney | tasks
 public | idx_open_tasks | index | barney | tasks
 public | tasks_pkey     | index | barney | tasks

5. Use pg_constraint for Indexes on Constraints

If you are looking for indexes that are created as part of constraints (like primary keys and unique constraints), you can use pg_constraint.

List Indexes on Constraints

SELECT conname AS constraint_name, conrelid::regclass AS table_name, conindid::regclass AS index_name
FROM pg_constraint
WHERE contype IN ('p', 'u');

Example Output:

                constraint_name                 |        table_name        |                   index_name                   
------------------------------------------------+--------------------------+------------------------------------------------
 pg_proc_oid_index                              | pg_proc                  | pg_proc_oid_index
 pg_proc_proname_args_nsp_index                 | pg_proc                  | pg_proc_proname_args_nsp_index
...

To explain the contype:

  • c = check constraint
  • f = foreign key constraint
  • p = primary key constraint
  • u = unique constraint
  • t = constraint trigger
  • x = exclusion constraint

Filter by Table Name

To find constraints and their indexes on a specific table, such as orders:

SELECT conname AS constraint_name, conrelid::regclass AS table_name, conindid::regclass AS index_name
FROM pg_constraint
WHERE contype IN ('p', 'u') AND conrelid::regclass = 'idiots'::regclass;

Example output:

 constraint_name | table_name | index_name  
-----------------+------------+-------------
 idiots_pkey     | idiots     | idiots_pkey

Summary of Methods

  • pg_indexes view: Convenient for basic index information across all indexes.
  • pg_class and pg_index: Provides deeper catalog information and more customization.
  • pg_stat_user_indexes: Useful for performance statistics on index usage.
  • \di in psql: Quick index lookup via command-line.
  • pg_constraint: Helpful for finding indexes related to constraints.

You can choose the method depending on the kind of details and flexibility you need in querying index metadata.