Monday, August 5, 2019

How to find indexes on a table in postgres



SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'myuser'
    and tablename='mytable'
ORDER BY
    tablename,
    indexname;

tablename indexname indexdef
mytable idx1 CREATE INDEX idx1 ON myschema.mytableUSING btree (col1, col2)
mytable idx2 CREATE INDEX idx2 ON myschema.mytable USING btree (col3)

Or use the psql command \d mytable, which lists the indexes after the description of the table columns
\d mytable

 
Indexes:
    "pk_mytable" PRIMARY KEY, btree (col1)
    "idx1" btree (col1, col2)
    "idx2" btree (col3)
    "idx3" btree (col3, col4)

No comments:

Post a Comment