CODEWITHSUNDEEP
postgresqlindexingoperators
metdos
metdos

Reputation: 13929

Operator Class of an Index in PostgreSQL

How can I learn which operator class is used by an already created index in PostgreSQL?

Upvotes: 1

Views: 1191

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

You can query the system catalog.
There can be multiple operator classes for multi-column indexes.

SELECT opcname
FROM  (
   SELECT unnest(indclass) AS ind_op
   FROM   pg_index
   WHERE  indexrelid = 'index_schema.index_name'::regclass
   ) i 
JOIN   pg_opclass o ON o.oid = i.ind_op;

indclass is of type oidvector which can be unnested like any array. This way, you get multiple lines for a multi-column index. More details about the catalog tables in the manual here and here.

If index_schema is in your search_path (and comes first, in case of duplicate index names), you don't have to schema-qualify the name.

Upvotes: 3

Related Questions