7

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?

3 Answers 3

15

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;
0
3

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

0

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!

2
  • 1
    The solution you extended is from maxTrialfire (Evan just copyedited). I added some more on the topic. Jul 14, 2021 at 1:59
  • Thank you Erwin !
    – detzu
    Jul 17, 2021 at 20:02

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.