Interface

In the first part, we said that the access method should provide information about itself. Let's see how this interface works.

Properties

All properties of access methods are presented in the pg_am table (am - access method). From this table you can get the list of available methods:

postgres=# select amname from pg_am;
amname
--------
btree
hash
gist
gin
spgist
brin
(6 rows)

Although sequential scanning can rightfully be attributed to access methods, historically, it is not on this list.
')
In PostgreSQL 9.5 and earlier, each property was represented by a separate field in the pg_am table. Starting from version 9.6, properties are polled with special functions and divided into several levels:

• accessor properties - pg_indexam_has_property,
• properties of a specific index - pg_index_has_property,
• properties of individual index columns - pg_index_column_has_property.

The division into access method and index levels is done with an eye to the future: at present, all indexes created on the basis of a single access method will always have the same properties.


The properties of the access method are the following four (for example, btree):

postgres=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
from pg_am a,
unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
where a.amname = 'btree' order by a.amname;
amname | name | pg_indexam_has_property
--------+---------------+-------------------------
btree | can_order | t
btree | can_unique | t
btree | can_multi_col | t
btree | can_exclude | t
(4 rows)


• can_order
  The access method allows you to specify the sort order of values ​​when creating an index (currently only applicable to btree);
• can_unique
  Supports uniqueness and primary key constraint (applicable to btree only);
• can_multi_col
  An index can be built in several columns;
• can_exclude
  Supports EXCLUDE exception constraint.

Properties related to the index (take for example an existing one):

postgres=# select p.name, pg_index_has_property('t_a_idx'::regclass,p.name)
from unnest(array['clusterable','index_scan','bitmap_scan','backward_scan']) p(name);
name | pg_index_has_property
---------------+-----------------------
clusterable | t
index_scan | t
bitmap_scan | t
backward_scan | t
(4 rows)


• clusterable
  The possibility of reordering the rows of the table in accordance with a given index (clustering with the CLUSTER command of the same name);
• index_scan
  Index scan support. This property may seem strange, but not all indices can issue TIDs one at a time — some give all the results at once and only support bitmap scanning;
• bitmap_scan
  Support bitmap scanning;
• backward_scan
  Returning the result in the reverse order specified when creating the index.

Finally, the column properties:

postgres=# select p.name, pg_index_column_has_property('t_a_idx'::regclass,1,p.name)
from unnest(array['asc','desc','nulls_first','nulls_last','orderable','distance_orderable','returnable','search_array','search_nulls']) p(name);
name | pg_index_column_has_property
--------------------+------------------------------
asc | t
desc | f
nulls_first | f
nulls_last | t
orderable | t
distance_orderable | f
returnable | t
search_array | t
search_nulls | t
(9 rows)

• asc, desc, nulls_first, nulls_last, orderable
  These properties are associated with the ordering of values ​​(we will talk about them when we get to the btree indexes);
• distance_orderable
  Issuing the result in the sort order by operation (currently applicable only for the gist and rum indices);
• returnable
  The ability to use the index without reference to the table, that is, support exclusively for index access;
• search_array
  Support for searching multiple values ​​for the construction of the “ indexed-field IN ( constant_list )” or, equivalently, “the indexed-field = ANY ( constant_string )”;
• search_nulls
  The ability to search by the conditions is null and is not null.

Some of the properties we have already discussed in detail earlier. Some properties are currently implemented by only one method. We will consider such opportunities when we talk about this particular method.

Classes and operator families

In addition to a set of "skills", you also need to know with which data types and with which operators the access method works. For this, PostgreSQL has the notion of a class of operators and a family of operators .

The operator class contains the minimum set of operators (and, possibly, auxiliary functions) for an index to work with a certain data type.

A class is always a member of a family of statements. In this case, several classes can belong to one common family if they have the same semantics. For example, the integer_ops family includes the classes int8_ops, int4_ops, and int2_ops for different in size but identical in size bigint, integer, and smallint types:

postgres=# select opfname, opcname, opcintype::regtype
from pg_opclass opc, pg_opfamily opf
where opf.opfname = 'integer_ops'
and opc.opcfamily = opf.oid
and opf.opfmethod = (select oid from pg_am where amname = 'btree');
opfname | opcname | opcintype
-------------+----------+-----------
integer_ops | int2_ops | smallint
integer_ops | int4_ops | integer
integer_ops | int8_ops | bigint
(3 rows)

Another example: the datetime_ops family includes operator classes for working with dates (both without time and with time):

postgres=# select opfname, opcname, opcintype::regtype
from pg_opclass opc, pg_opfamily opf
where opf.opfname = 'datetime_ops'
and opc.opcfamily = opf.oid
and opf.opfmethod = (select oid from pg_am where amname = 'btree');
opfname | opcname | opcintype
--------------+-----------------+-----------------------------
datetime_ops | date_ops | date
datetime_ops | timestamptz_ops | timestamp with time zone
datetime_ops | timestamp_ops | timestamp without time zone
(3 rows)

The family may also include additional operators for comparing values ​​of different types. By grouping into a family, the scheduler can use an index for predicates with values ​​of different types. The family can also contain other auxiliary functions.

In most cases, the families and classes of operators do not need to know anything. Usually we just create an index, and this uses some class of default operators.

However, you can specify the class of operators explicitly. A simple example when this is necessary: ​​in a database with a sorting rule other than C, the usual index on the text field does not support the LIKE operation:

postgres=# show lc_collate;
lc_collate
-------------
en_US.UTF-8
(1 row)
postgres=# explain (costs off) select * from t where b like 'A%';
QUERY PLAN
-----------------------------
Seq Scan on t
Filter: (b ~~ 'A%'::text)
(2 rows)

This limitation can be overcome by creating an index with the text_pattern_ops operator class (note how the condition in the plan has changed):

postgres=# create index on t(b text_pattern_ops);
CREATE INDEX
postgres=# explain (costs off) select * from t where b like 'A%';
QUERY PLAN
----------------------------------------------------------------
Bitmap Heap Scan on t
Filter: (b ~~ 'A%'::text)
-> Bitmap Index Scan on t_b_idx1
Index Cond: ((b ~>=~ 'A'::text) AND (b ~<~ 'B'::text))
(4 rows)


System catalog

In conclusion of this part, we present a small diagram of the system catalog tables related directly to classes and operator families.



All these tables, of course, are described in detail .

Using the system catalog, you can find the answer to a number of questions, even without looking at the documentation. For example, with what types of data such an access method can work?

postgres=# select opcname, opcintype::regtype
from pg_opclass
where opcmethod = (select oid from pg_am where amname = 'btree')
order by opcintype::regtype::text;
opcname | opcintype
---------------------+-----------------------------
abstime_ops | abstime
array_ops | anyarray
enum_ops | anyenum
...

What operators are included in the class (and, therefore, the index can be used to access by the condition that includes such an operator)?

postgres=# select amop.amopopr::regoperator
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where opc.opcname = 'array_ops'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'btree'
and amop.amoplefttype = opc.opcintype;
amopopr
-----------------------
<(anyarray,anyarray)
<=(anyarray,anyarray)
=(anyarray,anyarray)
>=(anyarray,anyarray)
>(anyarray,anyarray)
(5 rows)

Continued .