How to List Indexes Created for Table in Postgres
Could You Tell Me How to Check What Indexes Are Created for Some Table in Postgresql? 4 Answers the View Pg_Indexes Provides Access to Useful Information About...
Could you tell me how to check what indexes are created for some table in postgresql ?
4 Answers
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%';
if you're in psql, then:
\d tablename
show Indexes, Foreign Keys and references...
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.
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;
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
