Interface

Properties

postgres=# select amname from pg_am;

amname -------- btree hash gist gin spgist brin (6 rows)

Access method properties — «pg_indexam_has_property»

Properties of a specific index — «pg_index_has_property»

Properties of individual columns of the index — «pg_index_column_has_property»

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 enables us to specify the sort order for values when an index is created (only applicable to «btree» so far).

The access method enables us to specify the sort order for values when an index is created (only applicable to «btree» so far). can_unique.

Support of the unique constraint and primary key (only applicable to «btree»).

Support of the unique constraint and primary key (only applicable to «btree»). can_multi_col.

An index can be built on several columns.

An index can be built on several columns. can_exclude.

Support of the exclusion constraint EXCLUDE.

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.

A possibility to reorder rows according to the index (clustering with the same-name command CLUSTER).

A possibility to reorder rows according to the index (clustering with the same-name command CLUSTER). index_scan.

Support of index scan. Although this property may seem odd, not all indexes can return TIDs one by one — some return results all at once and support only bitmap scan.

Support of index scan. Although this property may seem odd, not all indexes can return TIDs one by one — some return results all at once and support only bitmap scan. bitmap_scan.

Support of bitmap scan.

Support of bitmap scan. backward_scan.

The result can be returned in the reverse order of the one specified when building the index.

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 related to ordering the values (we'll discuss them when we reach a description of «btree» indexes).

These properties are related to ordering the values (we'll discuss them when we reach a description of «btree» indexes). distance_orderable.

The result can be returned in the sort order determined by the operation (only applicable to GiST and RUM indexes so far).

The result can be returned in the sort order determined by the operation (only applicable to GiST and RUM indexes so far). returnable.

A possibility to use the index without accessing the table, that is, support of index-only scans.

A possibility to use the index without accessing the table, that is, support of index-only scans. search_array.

Support of search for several values with the expression «indexed-field IN (list_of_constants)», which is the same as «indexed-field = ANY(array_of_constants)».

Support of search for several values with the expression «indexed-field IN (list_of_constants)», which is the same as «indexed-field = ANY(array_of_constants)». search_nulls.

A possibility to search by IS NULL and IS NOT NULL conditions.

Operator classes and families

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)

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)

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)

postgres=# create index on t(b text_pattern_ops); 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

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 ...

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)

In the first article , we've mentioned that an access method must provide information about itself. Let's look into the structure of the access method interface.All properties of access methods are stored in the «pg_am» table («am» stands for access method). We can also get a list of available methods from this same table:Although sequential scan can rightfully be referred to access methods, it is not on this list for historical reasons.In PostgreSQL versions 9.5 and lower, each property was represented with a separate field of the «pg_am» table. Starting with version 9.6, properties are queried with special functions and are separated into several layers:The access method layer and index layer are separated with an eye towards the future: as of now, all indexes based on one access method will always have the same properties.(by an example of «btree»):(let's consider an existing one for example):We've already discussed some of the properties in detail. Some properties are specific to certain access methods. We will discuss such properties when considering these specific methods.In addition to properties of an access method exposed by the described interface, information is needed to know which data types and which operators the access method accepts. To this end, PostgreSQL introduces operator class and operator family concepts.An operator class contains a minimal set of operators (and maybe, auxiliary functions) for an index to manipulate a certain data type.An operator class is included in some operator family. Moreover, one common operator family can contain several operator classes if they have the same semantics. For example, «integer_ops» family includes «int8_ops», «int4_ops», and «int2_ops» classes for types «bigint», «integer», and «smallint», having different sizes but the same meaning:Another example: «datetime_ops» family includes operator classes to manipulate dates (both with and without time):An operator family can also include additional operators to compare values of different types. Grouping into families enables the planner to use an index for predicates with values of different types. A family can also contain other auxiliary functions.In most cases, we do not need to know anything about operator families and classes. Usually we just create an index, using a certain operator class by default.However, we can explicitly specify the operator class. This is a simple example of when the explicit specification is necessary: in a database with the collation different from C, a regular index does not support the LIKE operation:We can overcome this limitation by creating an index with the operator class «text_pattern_ops» (notice how the condition in the plan has changed):In conclusion of this article, we provide a simplified diagram of tables in the system catalog that are directly related to operator classes and families.It goes without saying that all these tables are described in detail The system catalog enables us to find answers to a number of questions without looking into the documentation. For example, which data types can a certain access method manipulate?Which operators does an operator class contain (and therefore, index access can be used for a condition that includes such an operator)?