How to list indexes in PostgreSQL

This week is all about listing things in PostgreSQL, and I’m going to start with indexes. Indexes are basically tables that points to data in other tables, therefore speeding up the query results in most of the cases.

Prerequisites

• PostgreSQL

List all indexes from a database using SQL query

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

List all indexes from a database using PSQL

\di

List all indexes from a database table using SQL query

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

List all indexes from a database table using PSQL

\d some_table

Conclusion

Other potentially useful PostgreSQL listing queries:

• List Stored Procedures in PostgreSQL
• List Triggers in PostgreSQL
• List Sequences in PostgreSQL

If you have anything else to add, feel free to comment below, no login/registration required. On a side note, follow our official channel on Telegram.