Operator Class of an Index in PostgreSQL

Question

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

Erwin Brandstetter · Accepted Answer · 2016-12-27 15:56:16Z

3

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.

edited Dec 27, 2016 at 15:56

answered Sep 11, 2012 at 12:11

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

zw963 Over a year ago

More most case, I think the index_schema is 'public',

Erwin Brandstetter Over a year ago

@zw963: In simple cases, yes. But the index lives in the same schema as its parent table, which can be most any schema, especially in multi-user environments.

Collectives™ on Stack Overflow

Operator Class of an Index in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related