Индексы

Введение

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

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

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

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

Если максимально упростить, то индекс в большинстве случаев это сбалансированное дерево поиска. К этому дереву обращается оптимизатор, если его алгоритм считает, что можно получить запрос быстрее с этой информацией, чем без неё. Таким образом, индексное сканирование будет вызвано при выполнении команды, где в части WHERE или ORDER BY будет поле, по которому создавали индекс. Это справедливо как для команды SELECT, так и для команд UPDATE и DELETE с условиями поиска. Так же индекс, определённый для столбца, участвующего в условии соединения, может значительно ускорить запросы с JOIN.

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

Создание и удаление индекса

Предположим, что приложение выполняет много подобных запросов:

SELECT *
FROM
  collect.client
WHERE client_id = константа;

Индекс для этого поля создан автоматически при проектировании таблицы, так как client_id является PRIMARY KEY. Так как мы запрашиваем конкретный идентификатор, то оптимизатор решит, что наиболее выгодным вариантом будет воспользоваться индексным сканированием по полю client_id.

Если будет выполняться запрос вида:

SELECT *
FROM
  collect.loan_client
WHERE
  client_id = константа;

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

Создать индекс для столбца client_id в таблице collect.loan_client можно с помощью команды:

CREATE INDEX loan_client_to_client_id
  ON collect.loan_client (client_id);

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

Для удаления индекса используется команда DROP INDEX:

DROP INDEX collect.loan_client_to_client_id;

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

Регулярная переиндексация

В некоторых ситуациях стоит периодически перестраивать индексы, выполняя команду REINDEX или последовательность отдельных шагов по восстановлению индексов. Так как при удалении объектов индексная таблица не удаляет страницы пока не освободится вся страница, а vacuum на это не влияет, то REINDEX может иногда помочь. Если много юзеров одновременно занимаются create/update/delete, то возможно возникновение взаимной блокировки, из-за которой может навернуться индекс. В таком случае выполнение REINDEX поможет восстановить индекс.

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

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

Использование EXPLAIN

После создания индекса для определённого запроса, стоит проверить, решил ли оптимизатор использовать индекс. Для этого в начало запроса добавляется команда explain(costs off):

explain(costs off)
SELECT *
FROM
  collect.loan_client
WHERE
  client_id = 6;

Результат выполнения запроса будет иметь вид:

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

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

• B-tree;

• GiST;

• Hash.

B-tree

B-деревья подходят для большинства операций, когда индексируемый столбец участвует в сравнении с одним из операторов: <, <=, =, >= или >. При обработке конструкций, представимых как сочетание этих операторов, например BETWEEN и IN, так же может выполняться поиск по индексу B-tree. Кроме того, такие индексы могут использоваться и в условиях IS NULL и IS NOT NULL по индексированным столбцам.

Например в запросе вида:

SELECT *
FROM
  collect.history
WHERE
  history_date <= ‘2022-12-31’::timestamp

Индекс B-tree по history_date значительно сократит время поиска.

CREATE INDEX collect_history_to_date
  ON collect.history (history_date);

Так как B-tree используется по умолчанию, то этот тип необязательно указывать в команде на создание индекса.

GiST

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

SELECT *
FROM
  collect.client
WHERE
  first_name ILIKE ‘%марч%’

При создании GiST-индекса необходимо указывать его тип в команде:

CREATE INDEX collect_client_to_first_name
  ON collect.client USING GIST (first_name);

Hash

Если таблица содержит несколько миллионов записей, и необходимо извлекать из нее только одну запись, то на столбец, участвующий в сравнении с оператором =, можно создать Hash-индекс:

CREATE INDEX имя
  ON таблица USING HASH (столбец);

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

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

Основные способы сканирования

Далее рассмотрим основные способы просмотра таблиц.

• Индексное сканирование (Index Scan) При индексном просмотре метод доступа возвращает значения TID по одному, до тех пор, пока подходящие строки не закончатся. Механизм индексирования по очереди обращается к тем страницам таблицы, на которые указывают TID.

• Последовательное сканирование (Seq Scan) При неселективном условии оптимизатор предпочтет последовательное сканирование таблицы целиком вместо использования индекса. Дело в том, что индексы работают тем лучше, чем выше селективность условия, то есть чем меньше строк ему удовлетворяет. При увеличении выборки возрастают и накладные расходы на чтение страниц индекса.

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

При получении всех записей для пользователя с идентификатором user_id=82 (около 70 тысяч) система выберет индексное сканирование:

Если выполним тот же запрос на получение записей для пользователя с идентификатором user_id=2, у которого в таблице хранится порядка 600 тысяч, то система выберет последовательное сканирование таблицы:

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

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

SELECT *
FROM
  collect.loan
WHERE
  credit_type_id = 4 AND
  credit_date <= '2023-01-01'::date;

В таком случае имеет смысл определить индекс, покрывающий оба столбца credit_type_id и credit_date:

CREATE INDEX cession_loan_to_type_id_and_credit_date
  ON collect.cession_loan (credit_type_id, credit_date);

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

Порядок полей в запросе важен, он должен совпадать с порядком полей в индексе.

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

Составные индексы следует использовать обдуманно. В большинстве случаев индекс по одному столбцу будет работать достаточно хорошо и сэкономит время и место.

Подробнее про составные индексы можно почитать в статье из официальной документации PostgreSQL.

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

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

Например, часто выполняется запрос вида:

SELECT *
FROM
  collect.client
WHERE	
  (second_name || ' ' || third_name) = 'Елена Владимировна';

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

CREATE INDEX client_to_name
  ON collect.client ((second_name || ' ' || third_name))

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

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

CREATE INDEX client_to_name
  ON collect.client (lower(second_name || ' ' || third_name))

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

Индексы с выражением стоит применять очень осторожно, так как на их перезапись зачастую уходит больше времени, так как эти выражения должны вычисляться при каждом добавлении строки и при каждом изменении без оптимизации HOT. Однако при поиске по индексу индексируемое выражение не вычисляется повторно, так как его результат уже сохранён в индексе. В рассмотренных выше случаях система видит запрос как WHERE столбец_индекса = 'константа', и поэтому поиск выполняется так же быстро, как и с простым индексом. Таким образом, индексы по выражениям могут быть полезны, когда скорость извлечения данных гораздо важнее скорости добавления и изменения.

Индекс с условием

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

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

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

SELECT *
FROM
  collect.loan
WHERE
  deleted;

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

CREATE INDEX loan_to_deleted
  ON collect.loan (deleted)
WHERE deleted;

Рассматриваемый индекс не будет строиться по записям, для которых поле deleted будет хранить значение false.

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

CREATE INDEX loan_to_closed
  ON collect.loan (debt_base)
WHERE NOT closed;

Этот индекс будет применяться, например в таком запросе:

SELECT *
FROM collect.loan
WHERE
  NOT closed AND
  debt_base < 10000;

Однако он также может применяться в запросах, где поле debt_base вообще не используется, например:

SELECT *
FROM collect.loan
WHERE
  NOT closed AND
  credit_sum > 10000;

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

Узнать о частичных индексах больше можно в статье из официальной документации PostgreSQL.

Советы

1. Если вам кажется, что индекс сильно задерживает CRUD, то для начала проверьте его через удаление индекса перед операцией и пересоздание сразу после. Иногда vacuum очень плохо работает и индексы приходится пересохранять. В принципе хорошее дело иногда пересоздавать индексы для того, чтобы они нормально перестраивались.

2. Индексные таблицы при удалении старых значений не становятся меньше, если на странице остаётся хоть одно значение, таким образом в крайне редких случаях можно нарваться на то, что индекс разрастается и становится неэффективным. В таких случаях автовакуум никак не поможет, требуется удалить и создать индекс по новой или использовать команду REINDEX.

3. Стоит обращать внимание на CONSTRAINT unique и Primary KEY, они создают индексы b_tree. Поэтому пересоздание индекса при существовании этих constraint будет излишним.

4. Зачастую использование индексов на малых таблицах не только излишне, но и обременительно. Оптимизатор может решить, что ему хочется сначала получить индексную таблицу, а потом уже со значениями из неё идти в основную таблицу и получать остальные поля. В то время как простое сканирование займёт меньше времени. Но всё же с третьей стороны опережающее архитектурное решение будет более мудрым решением. Если вы уверены, что таблица разрастётся в ближайшее время, то индексы по основным полям там просто необходимы.

5. Существующие на базе методы доступа для индексов можно посмотреть по запросу:

SELECT amname FROM pg_am;

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

SELECT * FROM pg_indexes WHERE tablename = 'client';

Результатом запроса будет таблица вида:

Источники

1. Статья Индексы в PostgreSQL с habr.

2. Официальная документация по PostgreSQL,