How to List Indexes in PostgreSQL and Related Commands

Indexes in PostgreSQL are objects used to improve the speed of accessing data. They are created based on either database columns or partial data.

Their function is to create a pointer to the corresponding row in the appropriate table.

Show the list of indexes in Postgres using psql cli

The most straightforward method to list all indexes (including their names, types and tables) in a PostgreSQL database is using the \di command in the psql command-line interface.

\di

 List of relations
 Schema |      Name       | Type  |  Owner   |   Table    
--------+-----------------+-------+----------+-----------
 public | idx_employee_id | index | postgres | employees
 public | idx_order_date  | index | postgres | orders
(2 rows)

How to list Postgres Indexes using SQL query

For more detailed information on indexes, such as the tables they belong to and their definitions, you can utilize SQL queries to list indexes.

Listing all indexes:

SELECT indexname, tablename, indexdef FROM pg_indexes;

   indexname     | tablename |                           indexdef                           
  ---------------+-----------+---------------------------------------------------------------
 idx_employee_id | employees | CREATE INDEX idx_employee_id ON public.employees USING btree (id)
 idx_order_date  | orders    | CREATE INDEX idx_order_date ON public.orders USING btree (order_date)
(2 rows)

Filtering indexes by table:

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

Replace your_table_name with the actual table name to list indexes associated with a specific table.

Related Useful Commands

PostgreSQL also offers several commands related to index management:

Creating an index in PostgreSQL:

CREATE INDEX index_name ON table_name (column_name);

Dropping an index in PostgreSQL:

DROP INDEX index_name;

Reindexing a database/table/index in PostgreSQL:

REINDEX {DATABASE | TABLE | INDEX} name;