CODEWITHSUNDEEP
postgresqlindexingpsql
user6023611
user6023611

Reputation:

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 ?

Upvotes: 286

Views: 267010

Answers (5)

dwilkins
dwilkins

Reputation: 2131

If you are in psql, then:

\d tablename

show Indexes, Foreign Keys and references...

Upvotes: 162

klin
klin

Reputation: 121604

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.

Upvotes: 397

santosh tiwary
santosh tiwary

Reputation: 708

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;

Upvotes: 8

Acceptance
Acceptance

Reputation: 157

The command

\di

will list all indexes for the current schema.

Upvotes: 10

b.vishnu Prasad
b.vishnu Prasad

Reputation: 581

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.

Upvotes: 48

Related Questions