Database Reference
In-Depth Information
class
system table provides a complete listing of available operator classes, both from
your original install and from extensions. A particular index method will work only
against a given set of opclasses. To see this complete list, you can either open up pgAdmin
and look under operators, or execute the query in
Example 6-7
against the
system
catalog to get a comprehensive view.
Example 6-7. Which data types and operator classes does B-Tree support?
SELECT
am
.
amname
AS
index_method
,
opc
.
opcname
AS
opclass_name
,
opc
.
opcintype
::
regtype
AS
indexed_type
,
opc
.
opcdefault
AS
is_default
FROM
pg_am
am
INNER
JOIN
pg_opclass
opc
ON
opc
.
opcmethod
=
am
.
oid
WHERE
am
.
amname
=
'btree'
ORDER
BY
index_method
,
indexed_type
,
opclass_name
;
index_method | opclass_name | indexed_type | is_default
-------------+---------------------+-----------------------------+------------
btree | bool_ops | boolean | t
:
btree | text_ops | text | t
btree | text_pattern_ops | text | f
btree | varchar_ops | text | f
btree | varchar_pattern_ops | text | f
:
In
Example 6-7
, we limit our result to B-Tree. Notice that one opclass per indexed data
type is marked as the default. When you create an index without specifying the opclass,
PostgreSQL chooses the default opclass for the index. Generally, this is good enough,
but not always.
For instance, B-Tree against
text_ops
(aka
varchar_ops
) doesn't include the
~~
oper‐
ator (the
LIKE
operator), so none of your
LIKE
searches can use an index in the
text_ops
opclass. If you plan on doing many wildcard searches on
varchar
or
text
columns,
you'd be better off explicitly choosing the
text_pattern_ops
/
varchar_pattern_ops
opclass for your index. To specify the opclass, just append the opclass after the column
name, as in:
CREATE
INDEX
idx1
ON
census
.
lu_tracts
USING
btree
(
tract_name
text_pattern_ops
);
You will notice there are both
varchar_ops
and
text_ops
in the list,
but they map only to
text
.
character varying
doesn't have B-Tree
operators of its own, because it is essentially text with a length con‐
straint.
varchar_ops
and
varchar_pattern_ops
are just aliases for
text_ops
and
text_pattern_ops
to satisfy the desire of some to
maintain this symmetry of opclasses starting with the name of the
type they support.
