How to list indexes created for table in postgres
PostgresqlIndexingPsqlPostgresql Problem Overview
Could you tell me how to check what indexes are created for some table in postgresql ?
Postgresql Solutions
Solution 1 - Postgresql
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%';
Solution 2 - Postgresql
if you're in psql, then:
\d tablename
show Indexes, Foreign Keys and references...
Solution 3 - Postgresql
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.
Solution 4 - Postgresql
You can find all the index related information inside pg_indexes view. Sometimes table is part of some schema/ owner or might be in PostgreSQL Rename Table would have changed the table name. So First find out who is the owner of the table and then query indexes on the table.
select schemaname, tablename from pg_tables where tablename='TEST';select tablename,indexname from pg_indexes where tablename='TEST';
or
select * from pg_indexes where tablename='SCHEMA_NME.TABLE_NAME';
You can also use \d:
\d table_name;