How to query the type of an existing index in Postgres?

Question

The pg_index table gives info on indexes, it doesn't seem to have a column describing the index type (btree, hash, gin, etc...)

What is the correct way of knowing an existing index type?

klin · Accepted Answer · 2022-06-01 14:18:28Z

1

The access method of an index is defined in the catalog pg_am, pointed by the column relam of the catalog pg_class, e.g.:

select c.relname, a.amname
from pg_index i
join pg_class c on c.oid = i.indexrelid
join pg_am a on a.oid = c.relam
where relnamespace = 'public'::regnamespace;

       relname        | amname 
----------------------+--------
 array_test_arr_idx   | gin
 students_topics_pkey | btree
 images_pkey          | btree

edited Jun 1, 2022 at 14:18

answered Nov 14, 2016 at 19:48

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query the type of an existing index in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related