An index definition may specify an operator class for each column of an index.

CREATE INDEX name ON table (columnopclass [, ...]);

The operator class identifies the operators to be used by the
index for that column. For example, a B-tree index on the type
int4 would use the int4_ops class; this operator class includes
comparison functions for values of type int4. In practice the default operator class for the
column's data type is usually sufficient. The main point of
having operator classes is that for some data types, there could
be more than one meaningful index behavior. For example, we might
want to sort a complex-number data type either by absolute value
or by real part. We could do this by defining two operator
classes for the data type and then selecting the proper class
when making an index.

There are also some built-in operator classes besides the
default ones:

The operator classes text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops, and name_pattern_ops support B-tree indexes on the
types text, varchar, char, and
name, respectively. The difference from
the ordinary operator classes is that the values are compared
strictly character by character rather than according to the
locale-specific collation rules. This makes these operator
classes suitable for use by queries involving pattern
matching expressions (LIKE or POSIX
regular expressions) if the server does not use the standard
"C" locale. As an example, you
might index a varchar column like
this:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

If you do use the C locale, you may instead create an
index with the default operator class, and it will still be
useful for pattern-matching queries. Also note that you
should create an index with the default operator class if you
want queries involving ordinary comparisons to use an index.
Such queries cannot use the xxx_pattern_ops operator classes.
It is allowed to create multiple indexes on the same column
with different operator classes.

The following query shows all defined operator classes:

SELECT am.amname AS index_method,
opc.opcname AS opclass_name
FROM pg_am am, pg_opclass opc
WHERE opc.opcamid = am.oid
ORDER BY index_method, opclass_name;

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.