CODEWITHSUNDEEP
postgresqlindexing
Boutran
Boutran

Reputation: 10124

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

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?

Upvotes: 1

Views: 147

Answers (1)

klin
klin

Reputation: 121604

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

Upvotes: 1

Related Questions