How to List Indexes in PostgreSQL and Related Commands

SimpleBackups founder

Laurent Lemaire

Co-founder, SimpleBackups

November 23, 2023

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.

PostgreSQL List Indexes

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.

 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.

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:


Back to blog

Stop worrying about your backups.
Focus on building amazing things!

Free 7-day trial. No credit card required.

Have a question? Need help getting started?
Get in touch via chat or at [email protected]

Customer support with experts
Security & compliance
Service that you'll love using