SimpleBackupsSimpleBackups

How to List Indexes in PostgreSQL and Related Commands

Last updated on

Originally posted on

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.

\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 use 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.

Listing indexes with their sizes:

SELECT indexrelname AS index_name,
       relname AS table_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Useful when a database is larger than the data in it. Indexes routinely account for more disk than the tables they serve.

Finding indexes nothing uses:

SELECT indexrelname AS index_name,
       relname AS table_name,
       idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan counts how often the planner has chosen each index since statistics were last reset. A zero here means the index costs you writes and disk and returns nothing. Check pg_stat_reset timing before acting on it, and be careful with indexes backing a unique or primary key constraint, which serve a purpose whether or not they are scanned.

Listing indexes in one schema:

SELECT indexname, tablename FROM pg_indexes WHERE schemaname = 'your_schema';

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;

Reindexing a large table can run for a long time and locks along the way, so take a dump before you start. The complete guide to PostgreSQL backup walks through the fastest way to get one.


Table of contents