Интерфейс
В первой части мы говорили о том, что метод доступа должен предоставлять информацию о себе. Посмотрим, как устроен этот интерфейс.
Свойства
Все свойства методов доступа представлены в таблице 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)
Продолжение следует.
Автор: Postgres Professional
