Интерфейс

В первой части мы говорили о том, что метод доступа должен предоставлять информацию о себе. Посмотрим, как устроен этот интерфейс.

Свойства

Все свойства методов доступа представлены в таблице pg_am (am — access method). Из этой таблицы можно получить и сам список доступных методов:

postgres=# select amname from pg_am;

 amname

--------

 btree

 hash

 gist

 gin

 spgist

 brin

(6 rows)

Хотя к методам доступа можно с полным правом отнести и последовательное сканирование, исторически сложилось так, что оно отсутствует в этом списке.

В версиях PostgreSQL 9.5 и более старых каждое свойство было представлено отдельным полем таблицы pg_am. Начиная с версии 9.6 свойства опрашиваются специальными функциями и разделены на несколько уровней:

свойства метода доступа — pg_indexam_has_property,
свойства конкретного индекса — pg_index_has_property,
свойства отдельных столбцов индекса — pg_index_column_has_property.

Разделение на уровни метода доступа и индекса сделано с прицелом на будущее: в настоящее время все индексы, созданные на основе одного метода доступа, всегда будут иметь одинаковые свойства.

К свойствам метода доступа относятся следующие четыре (на примере 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
Метод доступа позволяет указать порядок сортировки значений при создании индекса (в настоящее время применимо только для btree);
can_unique
Поддержка ограничения уникальности и первичного ключа (применимо только для btree);
can_multi_col
Индекс может быть построен по нескольким столбцам;
can_exclude
Поддержка ограничения исключения 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
Возможность переупорядочивания строк таблицы в соответствии с данным индексом (кластеризация одноименной командой CLUSTER);
index_scan
Поддержка индексного сканирования. Это свойство может показаться странным, однако не все индексы могут выдавать TID по одному — некоторые выдают все результаты сразу и поддерживают только сканирование битовой карты;
bitmap_scan
Поддержка сканирования битовой карты;
backward_scan
Выдача результата в порядке, обратном указанному при создании индекса.

Наконец, свойства столбцов:

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
Эти свойства связаны с упорядочиванием значений (мы поговорим о них, когда дойдем до btree-индексов);
distance_orderable
Выдача результата в порядке сортировки по операции (в настоящее время применимо только для индексов gist и rum);
returnable
Возможность использования индекса без обращения к таблице, то есть поддержка исключительно индексного доступа;
search_array
Поддержка поиска нескольких значений для конструкции «индексированное-поле IN (список_констант)» или, что то же самое, «индексированное-поле = ANY(массив_констант)»;
search_nulls
Возможность поиска по условиям is null и is not null.

Часть свойств мы уже подробно обсудили ранее. Часть свойств в настоящее время реализуется только каким-то одним методом. Такие возможности мы рассмотрим, когда будет говорить про этот конкретный метод.

Классы и семейства операторов

Помимо набора «умений» надо также знать, с какими типами данных и с какими операторами работает метод доступа. Для этого в PostgreSQL есть понятия класса операторов и семейства операторов.

Класс операторов содержит минимальный набор операторов (и, возможно, вспомогательных функций) для работы индекса с некоторым типом данных.

Класс всегда входит в какое-либо семейство операторов. При этом в одно общее семейство могут входить несколько классов, если они имеют одинаковую семантику. Например, семейство integer_ops включает классы int8_ops, int4_ops и int2_ops для разных по размеру, но одинаковых по смыслу типов bigint, integer и smallint:

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)

Другой пример: в семейство datetime_ops входят классы операторов для работы с датами (как без времени, так и со временем):

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)

Семейство может также включать дополнительные операторы для сравнения значений разных типов. За счет группировки в семейство планировщик может использовать индекс для предикатов со значениями разных типов. Также семейство может содержать и другие вспомогательные функции.

В большинстве случаев про семейства и классы операторов не требуется ничего знать. Обычно мы просто создаем индекс, и при этом используется некоторый класс операторов по умолчанию.

Однако можно указать класс операторов явно. Простой пример, когда это необходимо: в базе данных с правилом сортировки, отличным от C, обычный индекс по текстовому полю не поддерживает операцию LIKE:

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)

Это ограничение можно преодолеть, создав индекс с классом операторов text_pattern_ops (обратите внимание, как изменилось условие в плане):

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)

Системный каталог

В заключение этой части приведем небольшую схему таблиц системного каталога, относящихся непосредственно к классам и семействам операторов.

Все эти таблицы, разумеется, подробно описаны.

Используя системный каталог, можно найти ответ на ряд вопросов, даже не заглядывая в документацию. Например, с какими типами данных может работать такой-то метод доступа?

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)

Продолжение.

Индексы в PostgreSQL — 2