postgresqlindexingpsql

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 ?


Solution

  • The view pg_indexes provides access to useful information about each index in the database, e.g.:

    select *
    from pg_indexes
    where tablename = 'test'
    

    The pg_index system view contains more detailed (internal) parameters, in particular, whether the index is a primary key or whether it is unique. Example:

    select 
        c.relnamespace::regnamespace as schema_name,
        c.relname as table_name,
        i.indexrelid::regclass as index_name,
        i.indisprimary as is_pk,
        i.indisunique as is_unique
    from pg_index i
    join pg_class c on c.oid = i.indrelid
    where c.relname = 'test'
    

    See examples in db<>fiddle.