19 Индексные методы доступа

PostgreSQL 18 · Издание 2026 года · Китайский перевод доступен для PostgreSQL 18

简体中文 · PG14 English · PG14 简体中文 · PG18Русский · PG18

19.1. Индексы и расширяемость

Индексы — объекты базы данных, предназначенные в основном для ускорения доступа к данным. Это вспомогательные структуры: любой индекс можно удалить и восстановить заново по информации в таблице. Но, кроме ускорения, индексы служат и для поддержки некоторых ограничений целостности.

В ядро встроены шесть индексных методов доступа (типов индексов): (psql: \dA)

=> SELECT amname FROM pg_am WHERE amtype = 'i';
 amname
--------
 btree
 hash
 gist
 gin
 spgist
 brin
(6 rows)

Следование идее расширяемости предполагает возможность добавлять и новые методы доступа без изменения ядра. Один такой пример (метод bloom) включен в стандартный набор расширений.

Несмотря на все различия между типами индексов, в конечном счете любой из них устанавливает соответствие между ключом (например, значением проиндексированного столбца) и версиями строк таблицы, в которых этот ключ встречается. В качестве ссылки используется шестибайтный идентификатор версии строки (tuple id, tid). Зная ключ или некоторую информацию о нем, можно быстро прочитать те версии строк, в которых может находиться нужная информация, не просматривая всю таблицу полностью. (с. 428)

Чтобы новый метод доступа можно было добавить как расширение, выделен общий механизм индексирования. Его основной задачей является получение идентификаторов версий строк от метода доступа и работа с ними:

чтение данных из соответствующих версий строк таблицы;
проверка видимости версий строк в снимке данных; (с. 97)
перепроверка условий, если метод не гарантирует их выполнения.

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

Информация о методе доступа нужна не только оптимизатору. При создании индекса надо решить: допускает ли метод создание составного индекса? может ли данный индекс обеспечить уникальность?

Механизм индексирования позволяет работать с самыми разными методами доступа; для этого метод доступа должен реализовать определенный интерфейс, сообщив о своих возможностях и особенностях.

В задачи метода доступа входят:

реализация алгоритмов построения индекса, вставки и удаления строк;
организация разбиения данных индекса по страницам (для работы с менеджером буферного кеша); (с. 183)
реализация алгоритма очистки; (с. 125)
установка блокировок для корректного конкурентного выполнения; (с. 305)
формирование журнала предзаписи; (с. 211)
поиск информации в индексе по ключу;
оценка стоимости использования индекса.

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

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

Часть логики индексирования при этом находится в самом методе доступа, а часть оказывается вынесенной в класс операторов. Это довольно произвольное деление: если для B-деревьев вся логика «зашита» в методе доступа, то некоторые методы могут предоставлять лишь основной каркас, отдавая все существенные детали на откуп конкретным классам операторов. Во многих случаях для одного и того же типа данных существует несколько классов операторов с отличающимся поведением, среди которых пользователь может выбрать наиболее подходящий.

Небольшой фрагмент общей картины показан на рисунке:

$Индексы и расширяемость: bool\_opsboolean; int4\_opsinteger; btree; text\_ops; text; text\_pattern\_ops; Механизм; индексирования; gist\_int4\_ops; gistgist\_text\_ops; point\_opspoint; методы доступаклассы операторовтипы данных$

Рисунок 19.1 Индексы и расширяемость

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

Класс операторов

Интерфейс метода доступа ¹ реализуется классом операторов ² — набором операторов и опорных функций, который используется методом доступа для работы с конкретным типом данных.

Классы операторов хранятся в системном каталоге в таблице pg_opclass. Полные данные для рисунка выше можно было бы получить запросом: (psql: \dAc)

=> SELECT amname, opcname, opcintype::regtype
FROM pg_am am
  JOIN pg_opclass opc ON opcmethod = am.oid;
 amname |           opcname            |          opcintype
--------+------------------------------+-----------------------------
 btree  | array_ops                    | anyarray
 hash   | array_ops                    | anyarray
 btree  | bit_ops                      | bit
 btree  | bool_ops                     | boolean
 ...
 brin   | pg_lsn_minmax_multi_ops      | pg_lsn
 brin   | pg_lsn_bloom_ops             | pg_lsn
 brin   | box_inclusion_ops            | box
(177 rows)

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

Например, вот классы операторов для B-дерева и типа text. Один из имеющихся классов обязательно помечен для использования по умолчанию:

=> SELECT opcname, opcdefault
FROM pg_am am
  JOIN pg_opclass opc ON opcmethod = am.oid
WHERE amname = 'btree'
  AND opcintype = 'text'::regtype;
       opcname       | opcdefault
---------------------+------------
 text_ops            | t
 varchar_ops         | f
 text_pattern_ops    | f
 varchar_pattern_ops | f
(4 rows)

Привычная команда создания индекса выглядит так:

CREATE INDEX ON aircrafts_data(model, range);

Но это просто сокращение для развернутого варианта:

CREATE INDEX ON aircrafts_data
USING btree -- метод доступа по умолчанию
(
  model text_ops, -- класс операторов по умолчанию для text
  range int4_ops  -- класс операторов по умолчанию для integer
);

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

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

Например, метод доступа btree определяет пять обязательных операторов сравнения. Любой класс операторов для него должен содержать все пять: (psql: \dAo)

=> SELECT opcname, amopstrategy, amopopr::regoperator
FROM pg_am am
  JOIN pg_opfamily opf ON opfmethod = am.oid
  JOIN pg_opclass opc ON opcfamily = opf.oid
  JOIN pg_amop amop ON amopfamily = opcfamily
WHERE amname = 'btree'
  AND opcname IN ('text_ops', 'text_pattern_ops')
  AND amoplefttype = 'text'::regtype
  AND amoprighttype = 'text'::regtype
ORDER BY opcname, amopstrategy;
     opcname      | amopstrategy |     amopopr
------------------+--------------+-----------------
 text_ops         |            1 | <(text,text)
 text_ops         |            2 | <=(text,text)
 text_ops         |            3 | =(text,text)
 text_ops         |            4 | >=(text,text)
 text_ops         |            5 | >(text,text)
 text_pattern_ops |            1 | ~<~(text,text)
 text_pattern_ops |            2 | ~<=~(text,text)
 text_pattern_ops |            3 | =(text,text)
 text_pattern_ops |            4 | ~>=~(text,text)
 text_pattern_ops |            5 | ~>~(text,text)
(10 rows)

Семантика оператора, подразумеваемая методом доступа, отражена в номере стратегии amopstrategy³. Например, для btree стратегия 1 означает «меньше», 2 — «меньше или равно» и так далее. Сами операторы при этом могут иметь произвольные названия.

В приведенном примере обычные операторы и операторы с тильдой отличаются тем, что последние не учитывают правила сортировки (collation) ⁴ и сравнивают строки побайтово. Тем не менее оба варианта реализуют одни и те же смысловые операции сравнения.

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

=> SELECT datcollate FROM pg_database
WHERE datname = current_database();
 datcollate
-------------
 en_US.UTF-8
(1 row)
=> CREATE INDEX ON tickets(passenger_name);
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE passenger_name LIKE 'ELENA%';
                  QUERY PLAN
----------------------------------------------
 Seq Scan on tickets
   Filter: (passenger_name ~~ 'ELENA%'::text)
(2 rows)

Другое дело — индекс с классом операторов text_pattern_ops:

=> CREATE INDEX tickets_passenger_name_pattern_idx
ON tickets(passenger_name text_pattern_ops);
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE passenger_name LIKE 'ELENA%';
                          QUERY PLAN
--------------------------------------------------------------
 Bitmap Heap Scan on tickets
   Filter: (passenger_name ~~ 'ELENA%'::text)
   -> Bitmap Index Scan on tickets_passenger_name_pattern_idx
       Index Cond: ((passenger_name ~>=~ 'ELENA'::text) AND
       (passenger_name ~<~ 'ELENB'::text))
(5 rows)

Обратите внимание, как изменилось выражение в условии Index Cond. Для поиска используется только префикс шаблона до символа %, а лишние совпадения отсеиваются при перепроверке условием Filter. Класс операторов для метода доступа btree не содержит оператора сравнения по шаблону, и единственный способ использовать B-дерево — переписать условие с помощью операторов сравнения. Операторы из класса text_pattern_ops действуют без учета правил сортировки, что и дает возможность заменить условие на эквивалентное⁵.

Индекс может использоваться для ускорения доступа по условию, если

это условие имеет вид «индексированный-столбец оператор выражение» (если указан коммутирующий оператор⁶, то допускается и «выражение оператор индексированный-столбец») ⁷
и при этом оператор входит в класс операторов, указанный для индексированного-столбца при создании индекса.

Например, такой запрос может использовать индекс:

=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE 'ELENA BELOVA' = passenger_name;
                       QUERY PLAN
--------------------------------------------------------
 Index Scan using tickets_passenger_name_idx on tickets
   Index Cond: (passenger_name = 'ELENA BELOVA'::text)
(2 rows)

Обратите внимание на то, как переставлены аргументы в условии Index Cond: на этапе выполнения индексированное поле должно быть левым аргументом оператора. При перестановке оператор заменяется на коммутирующий; в данном случае это тот же самый оператор, поскольку отношение равенства коммутативно.

А следующий запрос в принципе не может использовать обычный индекс, поскольку вместо имени столбца в условии стоит вызов функции:

=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE initcap(passenger_name) = 'Elena Belova';
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tickets
   Filter: (initcap(passenger_name) = 'Elena Belova'::text)
(2 rows)

В таком случае можно применить индекс по выражению ⁸, указав при его создании не столбец, а произвольное выражение:

=> CREATE INDEX ON tickets( (initcap(passenger_name)) );
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE initcap(passenger_name) = 'Elena Belova';
                             QUERY PLAN
--------------------------------------------------------------------
 Bitmap Heap Scan on tickets
   Recheck Cond: (initcap(passenger_name) = 'Elena Belova'::text)
   -> Bitmap Index Scan on tickets_initcap_idx
       Index Cond: (initcap(passenger_name) = 'Elena Belova'::text)
(4 rows)

Выражение может зависеть только от значений полей в табличной строке и не должно зависеть ни от состояния других данных в базе, ни от настроек (например, от локали). Иными словами, если выражение содержит вызовы функций, то эти функции обязаны иметь и соблюдать категорию изменчивости IMMUTABLE ⁹. В противном случае результат запроса, выполненного с индексным доступом, может отличаться от результата того же запроса, выполненного полным сканированием таблицы.

Кроме собственно операторов, класс операторов может предоставлять опорные функции ¹⁰, необходимые методу. Например, метод доступа btree определяет пять опорных функций¹¹, из которых только первая (сравнивающая два значения) является необходимой, а остальные могут отсутствовать: (psql: \dAp)

=> SELECT amprocnum, amproc::regproc
FROM pg_am am
  JOIN pg_opfamily opf ON opfmethod = am.oid
  JOIN pg_opclass opc ON opcfamily = opf.oid
  JOIN pg_amproc amproc ON amprocfamily = opcfamily
WHERE amname = 'btree'
  AND opcname = 'text_ops'
  AND amproclefttype = 'text'::regtype
  AND amprocrighttype = 'text'::regtype
ORDER BY amprocnum;
 amprocnum |       amproc
-----------+--------------------
         1 | bttextcmp
         2 | bttextsortsupport
         4 | btvarstrequalimage
(3 rows)

Семейство операторов

Класс операторов всегда входит в какое-либо семейство операторов ¹² (таблица pg_opfamily системного каталога). При этом в одно общее семейство могут входить несколько классов, если они работают одинаковым образом с похожими типами данных.

Например, семейство integer_ops включает несколько классов для разных по размеру, но одинаковых по смыслу числовых типов: (psql: \dAf)

=> SELECT opcname, opcintype::regtype
FROM pg_am am
  JOIN pg_opfamily opf ON opfmethod = am.oid
  JOIN pg_opclass opc ON opcfamily = opf.oid
WHERE amname = 'btree'
  AND opfname = 'integer_ops';
 opcname  | opcintype
----------+-----------
 int2_ops | smallint
 int4_ops | integer
 int8_ops | bigint
(3 rows)

А в семейство datetime_ops входят классы операторов для работы с датами:

=> SELECT opcname, opcintype::regtype
FROM pg_am am
  JOIN pg_opfamily opf ON opfmethod = am.oid
  JOIN pg_opclass opc ON opcfamily = opf.oid
WHERE amname = 'btree'
  AND opfname = 'datetime_ops';
     opcname     |          opcintype
-----------------+-----------------------------
 date_ops        | date
 timestamptz_ops | timestamp with time zone
 timestamp_ops   | timestamp without time zone
(3 rows)

Каждый класс операторов работает с каким-то одним типом. Семейство же включает и операторы, принимающие разные типы данных:

=> SELECT opcname, amopopr::regoperator
FROM pg_am am
  JOIN pg_opfamily opf ON opfmethod = am.oid
  JOIN pg_opclass opc ON opcfamily = opf.oid
  JOIN pg_amop amop ON amopfamily = opcfamily
WHERE amname = 'btree'
  AND opfname = 'integer_ops'
  AND amoplefttype = 'integer'::regtype
  AND amopstrategy = 1
ORDER BY opcname;
 opcname  |       amopopr
----------+---------------------
 int2_ops | <(integer,bigint)
 int2_ops | <(integer,smallint)
 int2_ops | <(integer,integer)
 int4_ops | <(integer,bigint)
 int4_ops | <(integer,smallint)
 int4_ops | <(integer,integer)
 int8_ops | <(integer,bigint)
 int8_ops | <(integer,smallint)
 int8_ops | <(integer,integer)
(9 rows)

За счет группировки операторов в семейство планировщик может использовать индекс для условий со значениями разных типов, не требуя явного приведения.

19.3. Интерфейс механизма индексирования

Как и для табличных методов доступа, столбец amhandler таблицы pg_am содержит имя функции, реализующей интерфейс ¹³:

=> SELECT amname, amhandler FROM pg_am WHERE amtype = 'i';
 amname |  amhandler
--------+-------------
 btree  | bthandler
 hash   | hashhandler
 gist   | gisthandler
 gin    | ginhandler
 spgist | spghandler
 brin   | brinhandler
(6 rows)

Функция заполняет интерфейсную структуру ¹⁴ необходимыми значениями. Часть предоставляемой информации — это функции, выполняющие отдельные элементы общей работы (например, сканирующие индекс и возвращающие идентификаторы версий строк), а часть — свойства индексного метода, о которых должен знать механизм индексирования.

Все свойства разделены на три уровня ¹⁵:

свойства метода доступа;
свойства конкретного индекса;
свойства отдельных столбцов индекса.

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

Свойства метода доступа

К свойствам метода доступа относятся следующие пять (показаны на примере B-дерева):

=> 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', 'can_include'
]) p(name)
WHERE a.amname = 'btree';
 amname |     name      | pg_indexam_has_property
--------+---------------+-------------------------
 btree  | can_order     | t
 btree  | can_unique    | t
 btree  | can_multi_col | t
 btree  | can_exclude   | t
 btree  | can_include   | t
(5 rows)

CAN ORDER Возможность сразу получить отсортированные данные ¹⁶. В настоящее время поддерживается только B-деревьями.

Чтобы получить результаты в нужном порядке, всегда можно просканировать таблицу и отсортировать полученные данные:

=> EXPLAIN (costs off)
SELECT * FROM seats ORDER BY seat_no;
       QUERY PLAN
------------------------
 Sort
   Sort Key: seat_no
   -> Seq Scan on seats
(3 rows)

Но при наличии индекса, поддерживающего это свойство, можно получить данные сразу в нужном порядке:

=> EXPLAIN (costs off)
SELECT * FROM seats ORDER BY aircraft_code;
              QUERY PLAN
--------------------------------------
 Index Scan using seats_pkey on seats
(1 row)

CAN UNIQUE Поддержка ограничения уникальности и первичного ключа¹⁷. Применимо только к B-деревьям.

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

=> INSERT INTO bookings(book_ref, book_date, total_amount)
VALUES ('000004', now(), 100.00);
ERROR:  duplicate key value violates unique constraint
"bookings_pkey"
DETAIL:  Key (book_ref)=(000004) already exists.

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

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

Например, PostgreSQL не позволяет создавать глобальные индексы на секционированных таблицах, но тем не менее позволяет объявить уникальный ключ (если в него входит ключ секционирования). В этом случае глобальная уникальность обеспечивается локальными уникальными индексами на каждой секции и тем фактом, что в двух секциях не может быть одинаковых значений ключа секционирования.

CAN MULTI COL Возможность построить составной индекс по нескольким столбцам ¹⁸.

Составной индекс позволяет ускорить поиск по нескольким условиям на разные столбцы таблицы. Например, таблица ticket_flights имеет составной первичный ключ и, соответственно, индекс:

=> \d ticket_flights_pkey
     Index "bookings.ticket_flights_pkey"
  Column   |     Type      | Key? | Definition
-----------+---------------+------+------------
 ticket_no | character(13) | yes  | ticket_no
 flight_id | integer       | yes  | flight_id
primary key, btree, for table "bookings.ticket_flights"

Поиск перелета по номеру билета и идентификатору рейса выполняется с помощью этого индекса:

=> EXPLAIN (costs off)
SELECT * FROM ticket_flights
WHERE ticket_no = '0005432001355' AND flight_id = 51618;
                        QUERY PLAN
----------------------------------------------------------
 Index Scan using ticket_flights_pkey on ticket_flights
   Index Cond: ((ticket_no = '0005432001355'::bpchar) AND
   (flight_id = 51618))
(3 rows)

В случае B-дерева возможность и эффективность поиска зависят от порядка столбцов в индексе. Для индексов других типов это может быть и не так. (с. 536) (с. 628)

CAN EXCLUDE Поддержка ограничения исключения EXCLUDE ¹⁹.

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

В качестве оператора обычно используется «пересечение» &&. Например, таким образом можно декларативно объявить, что аудитория не может быть дважды забронирована на одно и то же время или что здания на карте не могут накладываться друг на друга. (с. 571)

С оператором «равно» ограничение исключения приобретает смысл ограничения уникальности: запрещается наличие в таблице двух строк с совпадающими значениями ключевых столбцов. Тем не менее это не то же самое, что ограничение уникальности: в частности, на ключ ограничения исключения нельзя ссылаться во внешних ключах, его нельзя использовать во фразе ON CONFLICT.

CAN INCLUDE Возможность добавить к индексу столбцы, не являющиеся частью индексного ключа, чтобы сделать индекс покрывающим. (с. 425)

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

=> CREATE UNIQUE INDEX ON flights(flight_id) INCLUDE (status);
=> EXPLAIN (costs off)
SELECT status FROM flights WHERE flight_id = 51618;
                          QUERY PLAN
---------------------------------------------------------------
 Index Only Scan using flights_flight_id_status_idx on flights
   Index Cond: (flight_id = 51618)
(2 rows)

Свойств на самом деле гораздо больше, но многие недоступны на уровне SQL²⁰. Вот некоторые из них:

CAN PRED LOCKS Поддержка предикатных блокировок. (с. 298)

Отсутствует у методов доступа spgist и brin; при сканировании индексов таких типов на уровне изоляции Serializable предикатные блокировки будут устанавливаться на всю таблицу, как при последовательном сканировании, что приведет к снижению производительности.

CAN BUILD PARALLEL Возможность построения индекса несколькими процессами параллельно. Поддерживается методами доступа btree, brin и gin. (v. 17) (v. 18)

Свойства индекса

Свойства, относящиеся к индексу (показаны на примере существующего):

=>  SELECT p.name, pg_index_has_property('seats_pkey', 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 Возможность физически переместить версии строк таблицы в соответствии с порядком, в котором данный индекс выдает их идентификаторы при индексном сканировании. (с. 361)

Это свойство определяет, применима ли команда CLUSTER.

INDEX SCAN Поддержка индексного сканирования. (с. 414)

Свойство означает, что метод доступа умеет выдавать идентификаторы версий строк по одному. Может показаться странным, но не все индексы поддерживают это свойство.

BITMAP SCAN Поддержка сканирования по битовой карте. (с. 427)

Свойство означает, что метод доступа может построить и вернуть битовую карту сразу по всем идентификаторам версий строк.

BACKWARD SCAN Возможность возвращать результат в порядке, обратном указанному при создании индекса.

Это свойство актуально, только если метод доступа поддерживает индексное сканирование.

Свойства столбцов

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

=> SELECT p.name,
  pg_index_column_has_property('seats_pkey', 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 Упорядоченность значений столбца.

Эти свойства определяют, в каком порядке хранятся значения (по возрастанию или по убыванию) и где находятся неопределенные значения (до обычных значений или после). Все свойства из этой группы применимы только к B-деревьям. (с. 350)

ORDERABLE Возможность упорядочивать значения данного столбца в предложении ORDER BY.

Это свойство также применимо только к B-деревьям.

DISTANCE ORDERABLE Поддержка операторов упорядочивания²¹. (с. 410)

В отличие от обычных индексных операторов, возвращающих логическое значение, операторы упорядочивания возвращают вещественное число — «расстояние» от одного аргумента до другого. Индекс поддерживает такие операторы в предложении ORDER BY.

Например, с помощью оператора упорядочивания <-> можно найти аэропорты, ближайшие к заданной точке:

=> CREATE INDEX ON airports_data USING gist(coordinates);
=> EXPLAIN (costs off)
SELECT * FROM airports
ORDER BY coordinates <-> point (43.578,57.593)
LIMIT 3;
                           QUERY PLAN
-----------------------------------------------------------------
 Limit
   -> Index Scan using airports_data_coordinates_idx on airpo...
       Order By: (coordinates <-> '(43.578,57.593)'::point)
(3 rows)

RETURNABLE Возможность получения данных из индекса без обращения к таблице, то есть поддержка сканирования только индекса. (с. 422)

Свойство определяет, позволяет ли индексная структура восстановить проиндексированные значения. Это возможно не всегда; например, некоторые индексы могут сохранять не сами значения, а их хеш-коды. В таких случаях не будет работать и свойство CAN INCLUDE.

SEARCH ARRAY Поддержка поиска нескольких значений из массива.

Такая необходимость возникает не только при использовании массивов как таковых. Например, планировщик преобразует конструкцию IN (список) в поиск по массиву: (см. также: с. 554; ссылка добавлена редактором веб-издания)

=> EXPLAIN (costs off)
SELECT * FROM bookings
WHERE book_ref IN ('C7C821', 'A5D060', 'DDE1BB');
                 QUERY PLAN
--------------------------------------------
 Index Scan using bookings_pkey on bookings
   Index Cond: (book_ref = ANY
   ('{C7C821,A5D060,DDE1BB}'::bpchar[]))
(3 rows)

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

SEARCH NULLS Возможность поиска по условиям IS NULL и IS NOT NULL.

Надо ли индексировать неопределенные значения? Это дает возможность использовать индекс для условий IS [NOT] NULL, а также в качестве покрывающего индекса при полном отсутствии условий на таблицу (поскольку в этом случае индекс должен вернуть данные всех строк таблицы, в том числе и с неопределенными значениями). Зато без NULL индекс может получиться компактнее.

Решение принимают разработчики метода доступа, но обычно неопределенные значения все-таки индексируются.

Если неопределенные значения в индексе не нужны, их можно исключить, построив частичный индекс ²² только по тем строкам таблицы, которые представляют интерес. Например:

=> CREATE INDEX ON flights(actual_arrival)
WHERE actual_arrival IS NOT NULL;
CREATE INDEX

=> EXPLAIN (costs off)
SELECT * FROM flights
WHERE actual_arrival = '2017-06-13 10:33:00+03';
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using flights_actual_arrival_idx on flights
   Index Cond: (actual_arrival = '2017-06-13 10:33:00+03'::ti...
(2 rows)

Частичный индекс будет меньше полного по размеру и не будет обновляться при изменении строк, не входящих в индекс. В некоторых случаях это может дать ощутимый выигрыш. Конечно, условие в предложении WHERE может быть любым (удовлетворяющим требованиям категории изменчивости IMMUTABLE), а не только проверкой неопределенных значений.

Построение частичных индексов не зависит от метода доступа и обеспечивается механизмом индексирования.

Разумеется, интерфейсом охвачены не все возможности индексных методов доступа, а только те, о которых необходимо знать заранее, чтобы принять правильное решение. Например, нет свойства, которое отражает возможность неблокирующего создания индекса (CONCURRENTLY). Такие свойства определяются кодом функций, реализующих интерфейс.