How to list indexes created for table in postgres

Question

Could you tell me how to check what indexes are created for some table in postgresql ?

Answer 1

The view pg_indexes provides access to useful information about each index in the database, eg.

select *
from pg_indexes
where tablename not like 'pg%';

Answer 2

if you're in psql, then:

\d tablename

show Indexes, Foreign Keys and references...

Answer 3

You can use this query:

select tablename,indexname,tablespace,indexdef from pg_indexes where tablename = 'your_table_name';

where has tablename is a field in pg_indexes ,you an get an accurate indices by matching user defined table at 'your_table_name' at WHERE clause . This will give you the desired details.

Answer 4

You can find all the index related information inside the pg_indexes view. Sometimes, a table may be part of some schema ("owner") or might have had a different name in the past (see: PostgreSQL Rename Table).

So first find out what is the schema ("owner") of the table:

SELECT schemaname, tablename FROM pg_tables WHERE tablename='table_name';

and then query indexes on the table with either of these queries:

SELECT tablename, indexname FROM pg_indexes WHERE tablename='table_name';
-- or 
SELECT * FROM pg_indexes WHERE tablename='schema_name.table_name';

As an alternative to all the above, you can also use \d:

\d table_name;

Answer 5

The command

\di

will list all indexes for the current schema.