Nov 29

Индексы PostgreSQL

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

  • операции selectupdate и delete, в которых присутствует условие where;
  • операция join и подобные ей (если индекс создан для полей, по которым происходит объединение);
  • некоторые случаи, где присутствует ORDER BY.

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

В самом базовом случае команда создания индекса выглядит следующим образом:

CREATE INDEX index_name ON table_name (column_name);

Чтобы создать уникальный индекс, который не позволяет создавать несколько записей с одинаковым значением в поле, нужно добавить модификатор UNIQUE. Уникальность поддерживают только B-tree индексы.

CREATE UNIQUE INDEX index_name ON table_name (column_name);

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

CREATE CONCURRENTLY INDEX index_name ON table_name (column_name);

Есть пара моментов, о которых стоит помнить:

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

Для удаления индекса необходимо воспользоваться запросом вида:

DROP INDEX name;

Пересоздание индексов (реиндекс)

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

REINDEX INDEX index_name; /* Пересоздаст индекс index_name */
REINDEX TABLE table_name; /* Пересоздаст все индексы в таблице table_name */
REINDEX DATABASE database_name; /* Пересоздаст все индексы в базе database_name */

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

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

CREATE INDEX index_name ON table_name USING type (column_name);

Рассмотрим подробнее каждый тип.

B-tree

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

  • операторы сравнения ><=>=<=BETWEEN и IN;
  • условия пустоты IS NULL и IS NOT NULL;
  • операторы поиска подстроки LIKE и ~, если искомая строка закреплена в начале шаблона (например str_1 LIKE 'search%');
  • регистронезависимые операторы поиска подстроки ILIKE и ~*. Но только в том случае, если искомая строка начинается с символа, который одинаков и в верхнем и в нижнем регистре (например числа)`.

GiST

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

  • типы boxcircle и polygon - операторы &&&>&<&<|>><<<<|<@@>@|&>,|>>~~=;
  • типы inet и cidr - операторы &&>>>>=>>=<><<<<=<<==;
  • тип point - операторы >>>^<<<@<@<@<^~=;
  • тип tsquery - операторы <@@>;
  • тип tsvector - оператор @@;
  • все типы range - операторы &&&>&<>><<<@-|-=@>@>.

GIN

GIN индексы применимы к составным типам, работа с которыми осуществляется с помощью ключей. Это массивы, jsonb и tsvector. Как и GiST индексы, они могут реализовать один из нескольких алгоритмов. И стандартный набор можно так же расширить, установив модели.
Ниже приведен список типов полей и индексируемых операторов для них. Значение операторов для каждого конкретного типа можно найти в официальной документации.

  • массивы - операторы &&<@=@>;
  • тип jsonb - операторы ??&?|@>;
  • тип tsvector - операторы @@ и @@@.

Так же GIN индекс может быть создан только для определенных полей jsonb поля.

HASH

Hash индексы могут использоваться только если проиндексированное поле участвует в сравнении (только оператор =). Этот индекс так же не используется в условиях IS NULL и IS NOT NULL. По ряду причин этот тип индексов не рекомендуется к использованию.

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

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

CREATE INDEX index_name ON table_name (column_name_1, compumn_name_2);

Максимальное количество полей в индексе - 32. Это значение может быть изменено, если вы самостоятельно компилируете PostgreSQL из исходников. Но на практике индексы, состоящие больше чем из трех полей, практически не применяются.
Составными могут быть индексы только B-tree, GiST и GIN типов. Составные индексы могут использоваться, когда в условии участвуют все поля, входящие в индекс. Так же составной индекс может быть использован, если условие касается только одного или нескольких проиндексированных полей. Для каждого типа есть свои особенности.

B-tree

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

GiST

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

GIN

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

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

Иногда может возникать ситуация, что индекс требуются не для всех записей в таблице. Например у нас есть таблица users, у которой есть поле is_active типа boolean. Записей со значением поля true намного меньше, чем с false. Но логично, что основная часть запросов идет к активным пользователям. В этом случае будет разумно построить индекс только для них. PostgreSQL предоставляет такую возможность.
Для создания частичного индекса надо добавить модификатор WHERE в запрос на создание.

CREATE INDEX index_name ON table_name (column_name) WHERE condition;

Вот запрос на создание частичного индекса для поля users.rating с условием is_active = true:

CREATE INDEX active_users ON users (rating) WHERE is_active = true;

Составные и уникальные индексы тоже могут быть частичными.

Функциональные индексы

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

SELECT * FROM users where (name || ' ' || surname) = 'Haru Atari';

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

CREATE INDEX active_users ON users ((name || ' ' || surname));

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

Share:

Comments

comments powered by HyperComments
Comment