How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)

Question

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?

Answer 1

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;

Answer 2

Simple

Just use the System Catalog Information Function pg_get_indexdef(index oid):

Reconstructs the creating command for an index. (This is a decompiled reconstruction, not the original text of the command.)

So just:

SELECT pg_get_indexdef(indexrelid) AS index_definition
FROM   pg_catalog.pg_index;
                                                                                                                                                                              index_definition                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX pg_foreign_data_wrapper_oid_index ON pg_catalog.pg_foreign_data_wrapper USING btree (oid)
 CREATE UNIQUE INDEX pg_foreign_server_oid_index ON pg_catalog.pg_foreign_server USING btree (oid)
 CREATE UNIQUE INDEX pg_user_mapping_oid_index ON pg_catalog.pg_user_mapping USING btree (oid)
...

This includes the index method (what you are asking for). The manual for Postgres 14:

method

The name of the index method to be used. Choices are btree, hash, gist, spgist, gin, and brin. The default method is btree.

... as well as: operator class (only if not default), collation (only if not default), schema (table is always schema-qualified), table, and index name.

Everything you might possibly need. Maybe even more than that, because the simple query above includes the many indexes of system catalogs.

Only indexes from user-land

SELECT pg_get_indexdef(i.indexrelid) AS index_definition
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = 'i';

                                                                                       index_definition                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX b2_pkey ON public.b2 USING btree (big_id)
 CREATE INDEX big_big_fts_gin_idx ON public.big USING gin (to_tsvector('simple'::regconfig, big))
 ...

The filter AND c.relkind = 'i' is redundant, but makes the query faster.

(Related queries typically also add AND n.nspname <> 'information_schema'. But since there are no indexes in information_schema, we can skip that.)

Deluxe version

You may want data in dedicated columns and a meaningful order on top:

SELECT n.nspname AS schema
     , i.indrelid::regclass::text AS table
     , c.relname AS index
     , a.amname AS index_method
     , opc.operator_classes
     , pg_get_indexdef(i.indexrelid) AS index_definition
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
JOIN   pg_catalog.pg_am        a ON a.oid = c.relam
CROSS  JOIN LATERAL (
   SELECT ARRAY (SELECT opc.opcname
                 FROM   unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
                 JOIN   pg_opclass opc ON opc.oid = o.oid
                 ORDER  BY o.ord)
   ) opc(operator_classes)
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = 'i'
ORDER  BY 1, 2, 3, 4;

 schema |       table       |             index             | index_method |          operator_classes           |                                                                                       index_definition                                                                                        
--------+-------------------+-------------------------------+--------------+-------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 public | b2                | b2_pkey                       | btree        | {int4_ops}                          | CREATE UNIQUE INDEX b2_pkey ON public.b2 USING btree (big_id)
 public | big               | big_big_fts_gin_idx           | gin          | {tsvector_ops}                      | CREATE INDEX big_big_fts_gin_idx ON public.big USING gin (to_tsvector('simple'::regconfig, big))
...

Fetching the index method from the catalog table pg_am, and operator classes from pg_opclass. You might add collations ...

db<>fiddle here

Answer 3

Adding the schema in case it's needed .

SELECT n.nspname, 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
JOIN pg_catalog.pg_namespace n ON n.oid = cls.relnamespace

Have extended maxTrialfire solution, thank you maxTrialfire!