I'm querying to the system catalog in Postgresql 9.6.4

Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to find the type of index anywhere in the system catalogs.

How can I query the catalogs to get a list of indexes along with their type?


The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can run the following

SELECT tab.relname, cls.relname, am.amname
FROM pg_index idx 
JOIN pg_class cls ON cls.oid=idx.indexrelid
JOIN pg_class tab ON tab.oid=idx.indrelid
JOIN pg_am am ON am.oid=cls.relam;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.