Индексы

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

Краткая справка по индексам
Типы индексов
Многоколоночные индексы
Индексы и ORDER BY
Объединение нескольких индексов
Уникальные индексы
Индексы по выражениям
Частичные индексы
Сканирование только по индексу и покрывающие индексы
Классы операторов и семейства операторов
Индексы и правила сортировки
Анализ использования индексов

Краткая справка по индексам

Предположим, у нас есть таблица, подобная этой:

CREATE TABLE test1 (
    id integer,
    content varchar
);

и приложение делает много запросов вида

SELECT content FROM test1 WHERE id = constant;

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

Подобный подход используется в большинстве научно-популярных книг: термины и понятия, которые часто ищут читатели, собраны в алфавитном указателе в конце книги. Заинтересованный читатель может сравнительно быстро просканировать указатель и перейти к соответствующей странице (страницам), а не читать всю книгу целиком, чтобы найти интересующий материал. Подобно тому, как задача автора состоит в том, чтобы предвидеть элементы, которые читатели могут искать, задача программиста базы данных — предвидеть, какие индексы будут полезны.

Следующая команда может использоваться для создания подобного индекса для столбца id:

CREATE INDEX test1_id_index ON test1 (id);

Имя test1_id_index можно выбрать любое, но в идеале оно должно напоминать вам о назначении индекса.

Чтобы удалить индекс, используйте команду DROP INDEX. Индексы могут быть добавлены и удалены из таблиц в любое время.

После создания индекса дальнейшее вмешательство не требуется: система будет обновлять индекс при изменении таблицы и будет использовать индекс в запросах, когда считает, что это будет более эффективно, чем последовательное сканирование таблицы. Но вам, возможно, придется регулярно запускать команду ANALYZE для обновления статистики, чтобы планировщик запросов мог принимать обоснованные решения. См. главу Советы по производительности для получения информации о том, как узнать, используется ли индекс, и когда и почему планировщик может предпочесть не использовать индекс.

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

Создание индекса для большой таблицы может занять много времени. По умолчанию QHB позволяет выполнять чтение (SELECT) из таблицы параллельно с созданием индекса, но модификации (INSERT, UPDATE, DELETE) блокируются до завершения построения индекса. В нагруженной системе это часто недопустимо. Можно разрешить модификации параллельно с созданием индекса, но следует учитывать несколько моментов — для получения дополнительной информации см. раздел Неблокирующее построение индексов.

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

Типы индексов

QHB предоставляет несколько типов индексов: B-дерево, Hash, GiST, SP-GiST, GIN и BRIN. Каждый тип индекса использует свой алгоритм, который лучше всего подходит для разных типов запросов. По умолчанию команда CREATE INDEX создает B-дерево, потому что оно подходит в наиболее распространенных ситуациях.

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

<
<=
=
>=
>

Конструкции, эквивалентные комбинациям этих операторов, такие как BETWEEN и IN, также могут быть реализованы с помощью поиска по B-дереву. Кроме того, B-дерево может быть использовано при запросе условия IS NULL или IS NOT NULL на столбец индекса.

Оптимизатор также может использовать B-дерево для запросов, включающих операторы сопоставления с образцом LIKE и ~, если шаблон является константой и привязан к началу строки; например, col LIKE 'foo%' or col ~ '^foo', но не col LIKE '%bar'. Однако, если ваша база данных использует локаль, отличную от C, вам нужно будет создать индекс со специальным классом операторов для поддержки индексации запросов на сопоставление с образцом; см. раздел Классы операторов и семейства операторов ниже. Также возможно использовать B-дерево для ILIKE и ~*, но только если шаблон начинается с символов, для которых нет верхнего и нижнего регистра, например, цифр.

Индексы типа B-дерево также можно использовать для извлечения данных в отсортированном порядке. Это не всегда быстрее, чем простое сканирование и сортировка, но часто полезно.

Хеш-индексы могут обрабатывать только простые сравнения на равенство. Планировщик запросов рассмотрит возможность использование хеш-индекса, когда индексируемый столбец участвует в сравнении с использованием оператора =. Следующая команда создает хеш-индекс:

CREATE INDEX name ON table USING HASH (column);

Индексы GiST — это не единая категория индексов, а скорее инфраструктура, с помощью которой может быть реализовано множество различных стратегий индексации. Соответственно, конкретные операторы, с которыми может использоваться индекс GiST, варьируются в зависимости от стратегии индексации (класса операторов). Например, стандартный дистрибутив QHB включает классы операторов GiST для нескольких двумерных геометрических типов данных, которые поддерживают индексированные запросы с использованием следующих операторов:

<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&

(Значение этих операторов см. в разделе Геометрические функции и операторы).

Многие другие классы операторов GiST доступны в коллекции contrib или в виде отдельных проектов.

Индексы GiST могут оптимизировать поиск «ближайшего соседа», например, такой запрос

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

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

Индексы SP-GiST, также как и индексы GiST, предлагают инфраструктуру, которая поддерживает различные виды поиска. SP-GiST позволяет реализовать широкий спектр различных несбалансированных дисковых структур данных, таких как дерево квадрантов, k-мерные деревья и префиксные деревья (Tries). Например, стандартный дистрибутив QHB включает классы операторов SP-GiST для точек двумерного пространства, которые позволяют использовать индекс для запросов с использованием следующих операторов:

<< >> ~= <@ <^ >^

(Значение этих операторов см. в разделе Геометрические функции и операторы).

Как и GiST, SP-GiST поддерживает поиск «ближайшего соседа».

Индексы GIN — это «инвертированные индексы», которые подходят для индексации столбцов, значение которых представляет из себя коллекцию элементов, и последующего поиска по отдельным элементам. Инвертированный индекс содержит отдельную запись для каждого элемента и может эффективно обрабатывать запросы, которые проверяют наличие определенных элементов.

Подобно GiST и SP-GiST, GIN может поддерживать множество различных пользовательских стратегий индексирования, и конкретные операторы, с которыми может использоваться индекс GIN, различаются в зависимости от стратегии индексирования. Например, стандартный дистрибутив QHB включает класс операторов GIN для массивов, который поддерживает индексированные запросы с использованием операторов:

<@ @> = &&

Значение этих операторов см. в разделе Функции и операторы массива. Классы операторов GIN, включенные в стандартную поставку, а также входящие в коллекцию contrib, перечислены в разделе Встроенные классы операторов GIN.

Индексы BRIN (сокращение от Block Range INdexes, индекс диапазона блоков) хранят сводные данные о значениях, хранящихся в последовательных диапазонах физических блоков таблицы. Как и GiST, SP-GiST и GIN, BRIN может поддерживать множество различных стратегий индексирования, и конкретные операторы, с которыми может использоваться индекс BRIN, различаются в зависимости от стратегии индексирования. Для типов данных, имеющих линейный порядок сортировки, индекс хранит минимальные и максимальные значения в столбце для каждого диапазона блоков. Это позволяет использовать индекс для запросов, использующих следующие операторы < <= = >= >. Для получения дополнительной информации см. главу Индексы BRIN.

Многоколоночные индексы

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

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

(предположим, что вы так храните каталог /dev в базе данных), и вы часто делаете запросы вида

SELECT name FROM test2 WHERE major = constant AND minor = constant;

то может быть целесообразно создать индекс по столбцам major и minor вместе, например:

CREATE INDEX test2_mm_idx ON test2 (major, minor);

В настоящее время только индексы типа B-tree, GiST, GIN и BRIN поддерживают многоколоночные индексы. Можно указать до 32 столбцов.

Многоколоночный индекс типа B-дерево может использоваться с условиями запроса, которые включают любое подмножество столбцов индекса, но индекс наиболее эффективен, когда заданы ограничения на ведущие (крайние левые) столбцы.

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

Ограничения на прочие столбцы индекса ("дополнительный фильтр поиска") проверяются при сканировании индекса прямо в нем, экономя обращения к таблице, но эти ограничения не уменьшают диапазон сканирования индекса. Например, если есть индекс по (a, b, c), а условие запроса WHERE a = 5 AND b <= 50 AND c = 100, то индекс будет сканироваться от первой записи a = 5 до последней записи a = 5 AND b <= 50, и для каждой записи этого диапазона будет проверяться дополнительное условие с = 100. Этот индекс в принципе может быть использован и для запросов, которые имеют ограничения на b и/или c без ограничения на a — но в этом случае будет сканироваться весь индекс, и скорее всего планировщик предпочтет последовательное сканирование таблицы такому использованию индекса.

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

Многоколоночный индекс GIN может использоваться с условиями запроса, которые включают любое подмножество столбцов индекса. В отличие от B-дерева или GiST, эффективность поиска по индексу одинакова независимо от того, какие столбцы индекса входят в условие запроса.

Многоколоночный индекс BRIN может использоваться с условиями запроса, которые включают любое подмножество столбцов индекса. Подобно GIN и в отличие от B-дерева или GiST, эффективность поиска по индексу одинакова независимо от того, какие столбцы индекса входят в условие запроса. Единственная причина иметь несколько индексов BRIN в одной таблице вместо одного многоколоночного индекса BRIN — это задать для нескольких индексов разное значение параметра pages_per_range.

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

Многоколоночные индексы следует использовать с осторожностью. В большинстве случаев достаточно одноколоночного индекса, который занимает меньше места и работает быстрее. Индексы с более чем тремя столбцами понадобятся только в экзотических ситуациях. См. также разделы Объединение нескольких индексов и Сканирование только по индексу и покрывающие индексы, где обсуждаются достоинства различных конфигураций индексов.

Индексы и ORDER BY

В дополнение к просто поиску строк, удовлетворяющих запросу, индекс может выдать их в определенном отсортированном порядке. Это позволяет реализовать указание ORDER BY без отдельного шага сортировки. Из всех типов индексов, поддерживаемых в настоящее время QHB, только B-дерево умеет сортированный вывод — другие типы индексов возвращают строки в неопределенном, зависящем от реализации порядке.

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

Важным частным случаем является ORDER BY в сочетании с LIMIT n: использование индекса позволит сразу отсечь n строк в порядке ORDER BY, а при использовании сканирования таблицы придётся достать и отсортировать всю выборку, чтобы получить первые n строк.

По умолчанию индексы B-дерева хранят свои записи в порядке возрастания, значения NULL после всех остальных (в случае равенства ссылка на строку в куче (TID) определяет порядок). Это означает, что прямое сканирование индекса по столбцу x приводит к выводу, удовлетворяющему ORDER BY x (точнее, ORDER BY x ASC NULLS LAST). Тот же индекс также можно сканировать в обратном направлении, получая выходные данные, удовлетворяющие ORDER BY x DESC (или, точнее, ORDER BY x DESC NULLS FIRST, именно такой порядок противоположен предыдущему).

Вы можете настроить порядок индекса B-дерево, включив опции ASC/DESC, NULLS FIRST/NULLS LAST при создании индекса, например:

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

Индекс, созданный как ASC NULLS FIRST может удовлетворять либо ORDER BY x ASC NULLS FIRST либо ORDER BY x DESC NULLS LAST в зависимости от того, в каком направлении он сканируется.

Вы можете спросить, зачем предлагать все четыре варианта, когда два варианта вместе с возможностью обратного сканирования будут охватывать все варианты ORDER BY. В одноколоночных индексах параметры действительно избыточны, но в многоколоночных индексах они могут быть полезны. Рассмотрим индекс из двух столбцов для (x, y) : он подходит для ORDER BY x, y если мы сканируем вперед, или ORDER BY x DESC, y DESC, если мы сканируем назад. Но он не может поддержать порядок ORDER BY x ASC, y DESC. Для такого порядка годится индекс (x ASC, y DESC) или (x DESC, y ASC)

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

Объединение нескольких индексов

Простое сканировании индекса может использоваться для условий на отдельные столбцы индекса, объединённых по AND. Например, при наличии индекса (a, b) условие запроса WHERE a = 5 AND b = 6 может использовать индекс, но запрос, например, WHERE a = 5 OR b = 6 не может напрямую использовать индекс.

К счастью, QHB имеет возможность комбинировать несколько индексов (включая многократное использование одного и того же индекса) для обработки случаев, которые не могут быть реализованы при сканировании одного индекса. Система может реализовать условия AND и OR за нескольких сканирований индекса. Например, запрос типа WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 можно реализовать через сканирование четырех отдельных диапазонов индекса по x, каждое по одному из условий x = ?. Результаты этих сканирований затем объединяются для получения результата. Другой пример: если у нас есть отдельные индексы для x и y, одна из возможных реализаций запроса WHERE x = 5 AND y = 6 состоит в том, чтобы использовать каждый индекс для соответствующего условия, а затем посчитать пересечение двух множеств строк.

Чтобы объединить несколько индексов, система сканирует каждый необходимый индекс и сохраняет множество строк, подходящий под эту часть условия, в памяти в виде битовой карты. Затем битовые карты можно объединять или пересекать в соответствии с запросом. Наконец, фактические строки таблицы посещаются и возвращаются. Строки таблицы посещаются в физическом порядке, потому что так они лежат в битовой карте; это означает, что любой порядок выдачи исходных индексов потерян, и поэтому потребуется отдельный шаг сортировки, если в запросе есть предложение ORDER BY. По этой причине, а также потому, что каждое дополнительное сканирование индекса добавляет дополнительное время, планировщик иногда предпочитает использовать простое сканирование индекса, даже если доступны дополнительные индексы, которые также можно было бы использовать.

Во всех приложениях, кроме самых простых, могут быть полезны различные комбинации индексов, и разработчик базы данных должен найти компромисс, какие индексы иметь. Иногда лучше использовать многоколоночные индексы, а иногда лучше создавать отдельные индексы и полагаться на функцию комбинирования индексов. Например, если ваша рабочая нагрузка включает в себя набор запросов, которые иногда содержат условие только на столбец x, иногда только на столбец y, а иногда на оба столбца, вы можете создать два отдельных индекса для x и y, полагаясь на комбинацию индексов для обработки запросов, которые используйте оба столбца. Вы также можете создать многоколоночный индекс для (x, y). Этот индекс эффективнее, чем комбинация индексов, для запросов, включающих оба столбца, но, как обсуждалось в разделе Многоколоночные индексы, он почти бесполезен для запросов, включающих только y, поэтому он не должен быть единственным индексом. Комбинация многоколоночного индекса и отдельного индекса по y будет неплохим вариантом. Для запросов, включающих только x, можно использовать многоколонный индекс, хотя он будет больше и, следовательно, медленнее, чем индекс только для x. Третий вариант заключается в создании всех трех индексов, но это разумно, только если поиск в таблице происходит гораздо чаще, чем модификации, и все три типа запросов одинаково частые. Если один из типов запросов встречается значительно реже, чем другие, то лучше создать только два индекса, которые лучше всего соответствуют двум более частым запросам.

Уникальные индексы

Индексы также можно использовать для обеспечения уникальности значения столбца или комбинации из нескольких столбцов.

CREATE UNIQUE INDEX name ON table (column [, ...]);

В настоящее время только индексы типа B-дерево могут быть объявлены уникальными.

Когда индекс объявляется уникальным, несколько строк таблицы с одинаковыми индексируемыми значениями не допускаются. Нулевые значения не считаются равными, т.е. уникальный индекс не мешает иметь несколько строк со значением NULL. Уникальный индекс из нескольких столбцов будет отклонять только те случаи, когда все индексируемые столбцы равны в нескольких строках.

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

Заметка
Не надо вручную создавать индексы для уникальных столбцов; это создаст копию автоматически созданного индекса.

Индексы по выражениям

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

Например, распространенный способ сравнения без учета регистра состоит в использовании функции lower:

SELECT * FROM test1 WHERE lower(col1) = 'value';

Этот запрос может использовать индекс, если он был определен по функции lower(col1) :

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

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

CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)));

В качестве другого примера, если вы часто делаете запросы вроде

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

тогда, возможно, стоит создать такой индекс:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

Синтаксис команды CREATE INDEX обычно требует записи круглых скобок вокруг выражения, как показано в последнем примере. Скобки могут быть опущены, когда выражение является просто вызовом функции, как в первом примере.

Индексы по выражениям относительно дороги в обслуживании, поскольку производные выражения должны вычисляться для каждой строки после вставки и каждого изменения. Однако выражения индекса не пересчитываются во время поиска по индексу, поскольку результат вычисления уже хранятся в индексе. В обоих приведенных выше примерах система видит запрос как WHERE indexed_column = ’constant’, поэтому скорость поиска эквивалентна любому другому простому запросу индекса. Таким образом, индексы по выражениям полезны, когда скорость поиска важнее скорости вставки и обновления.

Частичные индексы

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

Одна из основных причин использования частичного индекса — избегать индексации наиболее популярных значений. Поскольку запрос, выполняющий поиск популярного значения (на которое приходится более скольких-то процентов от всей таблицы), все равно будет использовать сканирование таблицы, а не индекс, то нет смысла вообще сохранять эти строки в индексе. Это уменьшает размер индекса, что ускорит те запросы, которые используют индекс. Это также ускорит многие операции обновления таблиц, поскольку индекс нужно обновлять не всегда. Пример ниже показывает возможное применение этой идеи.

Пример. Настройка частичного индекса для исключения частых значений

Предположим, вы храните журналы доступа веб-сервера в базе данных. Большинство обращений происходит из диапазона IP-адресов вашей организации, но некоторые из других источников (например, сотрудники, подключающиеся по телефонной линии). Если вы ищете по IP-адресу в основном для внешних обращений, вам, вероятно, не нужно индексировать диапазон IP-адресов, который соответствует подсети вашей организации.

Предположим, что таблица такая:

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

Чтобы создать частичный индекс, который соответствует нашему примеру, используйте такую команду:

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

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

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

Здесь IP-адрес из запроса покрывается частичным индексом. Следующий запрос не может использовать частичный индекс, так как он использует IP-адрес, который исключен из индекса:

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '192.168.100.23';

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

Другое возможное использование частичного индекса — включение в индекс только тех значений, которые участвуют в типичной рабочей нагрузке (как показано в следующем примере). Это дает преимущества, описанные выше, однако не позволяет использовать индекс при поиске по "нетипичным" значениям. Очевидно, что выбор частичных индексов в таком сценарии потребует большой осторожности и экспериментов.

Пример. Настройка частичного индекса для исключения «неинтересных» значений

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

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

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

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

Однако индекс также может использоваться в запросах, которые вообще не включают order_nr, например:

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

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

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

SELECT * FROM orders WHERE order_nr = 3501;

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

Пример 3.2 также иллюстрирует, что индексированный столбец и столбец, используемый в предикате, не обязаны совпадать. QHB поддерживает частичные индексы с произвольными предикатами, при условии, что задействованы только столбцы индексируемой таблицы. Однако имейте в виду, что предикат должен соответствовать условиям, используемым в запросах, которые должны использовать индекс. Чтобы быть точным, частичный индекс может использоваться в запросе, только если система может аналитически распознать, что из условия WHERE всегда следует предикат индекса. QHB не имеет сложного средства проверки теорем, способного распознавать математически эквивалентные выражения, написанные в разных формах. (Мало того, что такое средство чрезвычайно трудно создать, оно, вероятно, будет работать слишком медленно, чтобы использоваться в планировщике запросов.) Система может распознавать простые следствия из неравенства, например, x < 1 подразумевает x < 2 . А в общем случае, лучше бы предиката индекса точно соответствовал части WHERE, иначе индекс не будет признан пригодным для использования. Сопоставление происходит во время планирования запроса, а не во время выполнения. Как следствие, параметризованные запросы могут не работать с частичным индексом. Например, подготовленный запрос с параметром может иметь условие WHERE x < ?, а предикат индекса x < 2 — индекс не будет использоваться для этого запроса, т.к. для некоторых значений параметра это приводило бы к неправильным результатам.

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

Пример. Настройка частичного уникального индекса

Предположим, у нас есть таблица с описанием результатов теста. Мы хотим убедиться, что для данной комбинации субъекта и цели хранится только одна «успешная» запись, а записей о «неудачных» испытаниях может храниться много. Вот один из способов сделать это:

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

Это особенно эффективный подход, когда мало успешных тестов и много неудачных.

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

CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;

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

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

Более подробную информацию о частичных индексах можно найти у Stonebraker, Seshadri.

Сканирование только по индексу и покрывающие индексы

Все индексы в QHB являются вторичными, то есть каждый индекс хранится отдельно от основной области данных таблицы (которая в терминологии QHB называется кучей(heap) таблицы). Это означает, что при обычном сканировании индекса каждый поиск строки требует извлечения данных как из индекса, так и из кучи. Кроме того, хотя записи индекса, которые соответствуют заданному индексируемому условию WHERE, обычно близки друг к другу в индексе, строки таблицы, на которые они ссылаются, могут находиться где угодно в куче. И та часть доступа по индексу, которая состоит из обращения к строкам в куче, включает в себя много произвольного доступа, который может быть медленным, особенно на традиционных вращающихся носителях. (Как описано в Разделе Объединение нескольких индексов, сканирования-на-битовых-картах пытаются уменьшить эту стоимость, делая доступ к куче последовательным, но он все равно производится.)

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

Тип индекса должен поддерживать сканирование только по индексу. Индексы B-дерева поддерживают. Индексы GiST и SP-GiST поддерживают сканирование только по индексу для некоторых классов операторов, но не для всех. Остальные типы индексов не поддерживают. Основным требованием является то, что индекс должен физически хранить или иметь возможность восстановить исходное значение данных для каждой записи индекса. В качестве контрпримера, индексы GIN не могут поддерживать сканирование только по индексу, поскольку каждая запись индекса содержит только часть исходного значения столбца.
Запрос должен ссылаться только на столбцы, хранящиеся в индексе. Например, предполагая индекс по столбцам x и y таблицы, которая также имеет столбец z, эти запросы могут использовать сканирование только по индексу:
```
SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;
```
а эти запросы не могут:
```
SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;
```
(Для индексов по выражению и частичных индексов все сложнее, см. ниже.)

Если эти два фундаментальных требования выполнены, то все значения данных, требуемые запросом, доступны из индекса, поэтому физически возможно сканирование только по индексу. Но для любого сканирования таблицы в QHB есть дополнительное требование: система должна убедиться, что каждая извлеченная строка «видима» для MVCC-снимка текущего запроса, как описано в главе Параллельный контроль. Информация о видимости не хранится в записях индекса, а только в записях кучи; так что на первый взгляд может показаться, что для любого поиска в любом случае потребуется доступ к куче. И это действительно так, если строка таблицы была недавно изменена. Однако для редко меняющихся данных есть способ обойти эту проблему. QHB отслеживает для каждой страницы в куче таблицы, все ли строки, хранящиеся на этой странице, достаточно стары, чтобы быть видимыми для всех текущих и будущих транзакций. Эта информация сохраняется в виде одного бита в карте видимости таблицы. Сканирование только по индексу после нахождения подходящей записи индекса проверяет бит видимости для соответствующей страницы кучи. Если он установлен, строка определенно видимая, и поэтому данные могут быть возвращены без обращения к куче. Если он не установлен, запись кучи должна быть посещена, чтобы выяснить, видна ли строка, поэтому никакого преимущества в производительности по сравнению со стандартным сканированием индекса не достигается. Даже в успешном случае этот подход требует обращения к карте видимости; но поскольку карта видимости на четыре порядка меньше описываемой ею кучи, для доступа к ней требуется гораздо меньше операций ввода-вывода. В большинстве случаев карта видимости постоянно хранится в памяти.

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

Чтобы эффективно использовать функцию сканирования только по индексу, вы можете создать покрывающий индекс, т.е. индекс, специально включающий лишние столбцы, которые требуются конкретному запросу, который вы часто выполняете. Поскольку запросам обычно требуется получать больше столбцов, чем только те, по которым они осуществляют поиск, QHB позволяет создавать индекс, в котором некоторые столбцы являются просто «дополнительной нагрузкой», а не частью ключа поиска. Это делается путем добавления указания INCLUDE со списком дополнительных столбцов. Допустим, вы часто делаете запрос вида

SELECT z FROM tab WHERE x = 'key';

Традиционный подход к ускорению таких запросов заключается в создании индекса только по x. Тем не менее, индекс, заданный как

CREATE INDEX tab_x_z ON tab(x) INCLUDE (z);

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

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

CREATE UNIQUE INDEX tab_x_z ON tab(x) INCLUDE (z);

условие уникальности применяется только к столбцу x, а не к комбинации x и z. (При описании UNIQUE и PRIMARY KEY тоже можно задать INCLUDE, это альтернативный синтаксис для создания такого индекса.)

Добавлять в индекс неключевые столбцы дополнительной нагрузки следует обдуманно, особенно если эти столбцы широкие. Если кортеж индекса превысит максимально допустимый размер для данного типа индекса, вставка данных завершится неудачно. В любом случае неключевые столбцы дублируют данные из таблицы и увеличивают размер индекса, что потенциально замедляет поиск. И помните, что нет никакого смысла включать столбцы полезной нагрузки в индекс, если таблица изменяется часто, т.к. все равно потребуется обращаться к куче. Если есть обращение к строке в куче, получить значение всех столбцов столбца можно оттуда, и это ничего не стоит. Другие ограничения заключаются в том, что в качестве дополнительных столбцов нельзя включать выражения, и что только B-деревья и GiST в настоящее время поддерживают включенные столбцы.

До того, как в QHB появилась функция INCLUDE, люди иногда создавали покрывающие индексы, записывая столбцы полезной нагрузки как обычные столбцы индекса, то есть

CREATE INDEX tab_x_y ON tab(x, y);

даже если они не собирались использовать y как часть WHERE. Это прекрасно работает, если дополнительные столбцы являются конечными столбцами; делать их ведущими столбцами неразумно по причинам, изложенным в разделе Многоколоночные индексы. Однако этот метод не подходит для уникальных индексов.

Усечение суффиксов всегда удаляет неключевые столбцы с верхних уровней B-Дерева. Как столбцы дополнительной нагрузки, они никогда не используются при сканировании индекса. Процесс усечения в принципе удаляет один или несколько конечных столбцов ключа из не-листьев B-Дерева, когда они не требуются для обхода дерева. Поэтому на практике покрытие индексов даже без предложения INCLUDE часто позволяет избежать хранения столбцов, которые являются дополнительной нагрузкой, на верхних уровнях. Однако явное определение столбцов дополнительной нагрузки в качестве неключевых столбцов надежно сохраняет кортежи на верхних уровнях небольшими.

Теоретически, сканирование только по индексу может использоваться с индексами по выражениям. Например, имея индекс по f(x), где x — столбец таблицы, можно выполнить запрос

SELECT f(x) FROM tab WHERE f(x) < 1;

как сканирование только по индексу; и это очень привлекательно, если f() дорогая для вычисления функция. Однако планировщик QHB в настоящее время не очень разбирается в таких случаях. Он считает, что запрос потенциально может быть выполнен при сканировании только по индексу, только когда из индекса доступны все столбцы, необходимые для запроса. В этом примере x не требуется, кроме как в контексте f(x), но планировщик этого не замечает и приходит к выводу, что сканирование только по индексу невозможно. Если очень надо, то это ограничение можно обойти, добавив x в качестве включенного столбца, например

CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

Дополнительное предостережение. Если цель состоит в том, чтобы избежать повторного вычисления f(x), то есть ещё одна проблема. Не факт, что планировщик додумается использовать значение, взятое из индекса, для всех вхождений f(x). Например, в запросе

SELECT B.name FROM tab join B on B.id = f(x) WHERE f(x) < 1;

даже если будет использовано сканирование только по индексу, значение f(x) для соединения будет вычислено заново (x возьмут из дополнительной колонки индекса). Этот недостаток может быть исправлен в будущих версиях QHB.

Частичные индексы также интересны при сканировании только по индексам. Рассмотрим частичный индекс из примера выше:

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

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

SELECT target FROM tests WHERE subject = 'some-subject' AND success;

Есть проблема: WHERE содержит success, который не является столбцом индекса. Тем не менее, сканирование только по индексу возможно, потому что плану не нужно перепроверять эту часть WHERE во время выполнения: все записи, найденные в индексе, обязательно имеют success = true поэтому в плане это не нужно явно проверять.

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

Определение индекса может указывать класс оператора для каждого столбца индекса.

CREATE INDEX name ON table (column opclass [sort options] [, ...]);

Класс операторов определяет операторы, которые будут использоваться индексом для этого столбца. Например, индекс B-дерева для типа int4 будет использовать класс int4_ops ; этот класс операторов включает функции сравнения для значений типа int4. На практике класс операторов по умолчанию для типа данных столбца обычно достаточен. Основная причина наличия классов операторов заключается в том, что для некоторых типов данных может быть более одного осмысленного поведения индекса. Например, мы могли бы захотеть отсортировать тип данных комплексного числа или по абсолютному значению или по реальной части. Мы могли бы сделать это, определив два класса операторов для типа данных, а затем выбрав подходящий класс при создании индекса. Класс оператора определяет основной порядок сортировки (который затем можно изменить, добавив параметры сортировки COLLATE, ASC/DESC, NULLS FIRST/NULLS LAST).

Есть также несколько встроенных классов операторов, кроме классов по умолчанию:

Классы операторов text_pattern_ops, varchar_pattern_ops и bpchar_pattern_ops поддерживают индексы B-дерева для типов text, varchar и char соответственно. Отличие от классов операторов по умолчанию состоит в том, что значения сравниваются строго символ за символом, а не в соответствии с правилами сортировки, специфичными для локали. Это делает эти классы операторов пригодными для использования в запросах, включающих выражения сопоставления с образцом (регулярные выражения LIKE или POSIX), когда база данных использует локаль, отличную от «C». Например, вы можете проиндексировать столбец varchar следующим образом:
```
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
```
Обратите внимание, что вам придется создать еще и индекс с классом операторов по умолчанию, если вы хотите, чтобы запросы, включающие обычные сравнения <, <=, > или >= использовали индекс. Такие запросы не могут использовать операторы класса xxx_pattern_ops (сравнения на равенство — могут). Можно создать несколько индексов про один столбец, но с разными классами операторов. Если вы используете локаль C, вам не нужен индекс с операторами класса xxx_pattern_ops, т.к. в локали C индекс с операторами по умолчанию можно использовать и для запросов сопоставления с образцом.

Следующий запрос показывает все известные классы операторов:

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault AS is_default
    FROM pg_am am, pg_opclass opc
    WHERE opc.opcmethod = am.oid
    ORDER BY index_method, opclass_name;

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

Эта расширенная версия предыдущего запроса показывает семейство операторов, к которому принадлежит каждый класс операторов:

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opf.opfname AS opfamily_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault AS is_default
    FROM pg_am am, pg_opclass opc, pg_opfamily opf
    WHERE opc.opcmethod = am.oid AND
          opc.opcfamily = opf.oid
    ORDER BY index_method, opclass_name;

Этот запрос показывает все определенные семейства операторов и все операторы, включенные в каждое семейство:

SELECT am.amname AS index_method,
       opf.opfname AS opfamily_name,
       amop.amopopr::regoperator AS opfamily_operator
    FROM pg_am am, pg_opfamily opf, pg_amop amop
    WHERE opf.opfmethod = am.oid AND
          amop.amopfamily = opf.oid
    ORDER BY index_method, opfamily_name, opfamily_operator;

Индексы и правила сортировки

Индекс может использовать только одно правило сортировки (COLLATION) на столбец индекса. Если требуется поиск по столбцу с разными правилами сортировки, придется создать несколько индексов.

Рассмотрим следующие команды:

CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);

CREATE INDEX test1c_content_index ON test1c (content);

Индекс унаследует правила сортировки от базового столбца content. Так что запрос вида

SELECT * FROM test1c WHERE content > constant;

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

SELECT * FROM test1c WHERE content > constant COLLATE "y";

Если такие запросы тоже нужны, то можно создать дополнительный индекс, который будет поддерживать правило сортировки "y", например:

CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");

Анализ использования индексов

Хотя индексы в QHB не нуждаются в обслуживании или настройке, все же важно проверить, какие индексы действительно используются реальной рабочей нагрузкой запросов. Изучение использования индекса для отдельного запроса выполняется командой EXPLAIN; его применение для этой цели иллюстрируется в разделе Использование EXPLAIN. Также возможно собрать общую статистику об использовании индекса на работающем сервере, как описано в разделе Сборщик статистики.

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

В первую очередь запустите ANALYZE. Эта команда собирает статистику о распределении значений в таблице. Эта информация необходима для оценки количества строк, возвращаемых запросом, которое необходимо планировщику для реалистичной оценки затрат для каждого возможного плана запроса. При отсутствии какой-либо реальной статистики предполагаются некоторые значения по умолчанию, которые почти наверняка будут неточными. Поэтому изучение планов выполнения без запуска ANALYZE является заведомом бесперспективным. См. разделы Обновление статистики планировщика и Процесс «Автовакуум» для получения дополнительной информации.
Используйте реальные данные для экспериментов. Использование тестовых данных для настройки индексов скажет вам, какие индексы вам нужны для тестовых данных, но не более того.

Особенно фатально использовать очень маленькие наборы тестовых данных. Выборка 1000 из 100000 строк может быть кандидатом на использование индекса, но если на тестовых данных вы выбираете 1 из 100 строк, то вы получите совсем другой результат. Все 100 строк, вероятно, помещаются на одной странице, и последовательное чтение всей таблицы будет безусловно быстрее на таком объеме,

Также будьте осторожны при генерации синтетических тестовых данных (это часто неизбежно, когда приложение еще не работает). Однородные значения, полностью случайные значения или значения, вставленные в порядке возрастания, исказят статистику по сравнению с реальными данными.
Когда индексы не используются, для тестирования может быть полезно форсировать их использование. Существуют параметры времени выполнения, которые могут отключать различные типы планов (см. раздел Конфигурация метода планирования). Например, отключение последовательного сканирования (enable_seqscan) и последовательных соединений (enable_nestloop), которые являются наиболее простыми планами, заставит систему использовать другой план. Если система все еще выбирает последовательное сканирование или последовательное соединение, то, вероятно, существует более фундаментальная причина, по которой индекс не используется, например, условие запроса не соответствует индексу. (Какой запрос можно использовать какой тип индекса объясняется в предыдущих разделах.)
Если искусственными ограничениями удалось заставить планировщик использовать индекс, то есть две варианта: либо планировщик прав, и использование индекса плохо подходит, либо оценки затрат планов запросов не отражают реальность. Посмотрите оценки стоимости планов с индексом и без индекса командой EXPLAIN ANALYZE, а также сравните реальное время выполнения.
Если окажется, что оценки затрат ошибочны, опять-таки есть несколько вариантов. Общая стоимость вычисляется умножением оценки количества строк на стоимость операций, производимых с каждой строкой. Стоимость операций можно скорректированы с помощью параметров (описано в разделе Константы стоимости планировщика). Неточная оценка количества строк(селективности) обычно связана с неточной статистикой. Это можно исправить, настроив параметры сбора статистики (см. ALTER TABLE).

Если вам не удастся скорректировать оценки стоимостей, чтобы они соответствовали реальности, вам, возможно, придется силой заставить планировщик использовать индекс. Вы также можете связаться с разработчиками QHB для изучения проблемы.

Документация по СУБД «Квант-Гибрид»