Индексы

Евгения Кузина

Содержание

Обзор
Типы индексов
- B-дерево
- Хеш
- GiST
- SP-GiST
- GIN
- BRIN
Управление индексами
Перестроение индексов
Составные индексы
Уникальные индексы
Индексы по выражениям
Частичные индексы
Контроль использования индекса

Обзор

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

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

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

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

B-дерево

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

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

Кроме того, оптимизатор также может использовать индекс B-дерево для запросов с операторами сопоставления LIKE и ~, если шаблон является константой и определен в начале строки. Например, column1 LIKE 'abc%' или column1 ~ '^abc', но не column1 LIKE '%abc'. Если ваша база данных не использует локаль C, вам потребуется создать индекс со специальным классом операторов для поддержки индексирования запросов на соответствие шаблону, см. Operator Classes and Operator Families.

Также возможно использовать индексы B-дерево для операторов ILIKE и ~*, но только если шаблон начинается с неалфавитных символов, то есть символов, на которые не влияет преобразование верхнего/нижнего регистра.

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

По умолчанию PostgreSQL создает индекс типа B-дерево. Следующая команда добавляет индекс B-дерево для столбца book_id в таблицу books:

CREATE INDEX ind_book_id ON books(book_id);

Хеш

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

Следующая команда создает хеш-индекс для столбца id в таблице book:

CREATE INDEX idx_id ON book USING HASH (id);

GiST

Индексы GiST (Generalized Search Tree) предназначены для работы со сложными типами данных, такими как геометрические объекты, текст и массивы. Индексы GiST позволяют быстро искать пространственные, текстовые и иерархические данные. Индексы GiST — это не отдельный вид индексов. Они представляют собой инфраструктуру, в которой можно реализовать множество различных стратегий индексирования. Как следствие, индексы GiST могут использоваться с разными операторами, в зависимости от стратегии индексирования (класса оператора). Дистрибутив ADPG включает классы операторов GiST для нескольких типов двумерных геометрических данных, которые поддерживают индексированные запросы со следующими операторами: <<, &<, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~= и &&. Эти операторы описаны в статье Geometric Functions and Operators.

Классы операторов GiST, включенные в стандартный дистрибутив, описаны в статье Built-in Operator Classes. Многие другие классы операторов GiST можно найти в коллекции contrib и в других сторонних проектах. Расширение contrib предустановлено в кластере ADPG. Для получения дополнительной информации об индексах GiST обратитесь к статье GiST Indexes.

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

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

Приведенный выше код находит десять мест, ближайших к заданной целевой точке. Можно ли использовать индекс таким образом, зависит от класса оператора. В таблице Built-in GiST Operator Classes операторы, которые могут быть использованы таким образом, перечислены в столбце Ordering Operators.

Также можно создать индекс GiST для поиска текстовых данных:

CREATE INDEX ind_gist ON my_table USING gist (to_tsvector('english', ind_column));

SP-GiST

Индексы SP-GiST (space-partitioned GiST), как и индексы GiST, предлагают инфраструктуру, поддерживающую различные виды поиска. SP-GiST позволяет организовывать на диске разнообразные несбалансированные структуры данных, такие как деревья квадрантов, k-мерные и сжатые префиксные деревья. Например, PostgreSQL включает классы операторов SP-GiST для двумерных точек, которые поддерживают индексированные запросы со следующими операторами: <<, >>, ~=, <@, <<| и |>>. Эти операторы описаны в статье Geometric Functions and Operators. Классы операторов SP-GiST, включенные в стандартный дистрибутив, перечислены в таблице Built-in SP-GiST Operator Classes. Для получения дополнительной информации об индексах SP-GiST обратитесь к статье SP-GiST Indexes.

SP-GiST также поддерживает поиск "ближайшего соседа". Для классов операторов SP-GiST, поддерживающих сортировку по расстоянию, соответствующий оператор указан в столбце Ordering Operators в таблице Built-in SP-GiST Operator Classes.

В следующем примере создается индекс SP-GiST для поля, хранящего IP-адреса:

CREATE INDEX ind_spgist ON my_table USING spgist (inet(ind_column));

GIN

Индексы GIN (Generalized Inverted Index) — это инвертированные индексы, подходящие для данных с несколькими ключами. Они используются для полнотекстового поиска, поиска в массивах, данных JSON и триграммах. Индексы GIN обеспечивают высокую производительность при поиске больших объемов данных. Инвертированный индекс содержит отдельный элемент для каждого значения компонента и может эффективно работать в запросах, проверяющих наличие определенных значений компонента.

GIN поддерживает несколько различных пользовательских стратегий индексирования, а конкретные операторы, с которыми может использоваться индекс GIN, различаются в зависимости от стратегии индексирования. Например, PostgreSQL включает класс операторов GIN для массивов, поддерживающий индексированные запросы с использованием следующих операторов: <@, @>, =, и &&. Эти операторы описаны в статье Array Functions and Operators.

Классы операторов GIN, включенные в стандартный дистрибутив, перечислены в таблице Built-in GIN Operator Classes. Многие другие классы операторов GIN можно найти в коллекции contrib и в других сторонних проектах. Расширение contrib предустановлено в кластере ADPG. Для получения дополнительной информации об индексах GIN обратитесь к статье GIN Indexes.

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

CREATE INDEX ind_gin ON my_table USING gin (to_tsvector('english', ind_column));

BRIN

Индексы BRIN (Block Range Indexes) хранят сводные данные о значениях, сохраненных в физически последовательно расположенных блоках таблицы. Они наиболее эффективны для столбцов, значения которых хорошо коррелируют с физическим порядком строк таблицы. Эти индексы эффективны для хранения и обработки временных рядов и географических данных. BRIN поддерживает несколько различных стратегий индексирования, и конкретные операторы, с которыми может использоваться индекс BRIN, различаются в зависимости от стратегии индексирования. Для типов данных с линейным порядком сортировки индексированные данные соответствуют минимальному и максимальному значениям в столбце для каждого диапазона блоков. Эти типы данных поддерживают индексированные запросы с операторами: <, <=, =, >= и >.

Классы операторов BRIN, включенные в стандартный дистрибутив, перечислены в таблице Built-in BRIN Operator Classes. Для получения дополнительной информации об индексах BRIN обратитесь к статье BRIN Indexes.

В следующем примере создается индекс BRIN:

CREATE INDEX ind_brin ON my_table USING brin (ind_column);

Управление индексами

Вы можете создавать, изменять и удалять индексы, используя соответствующие команды.

Команда CREATE INDEX создает индекс для указанных столбцов отношения, которое может быть таблицей или материализованным представлением. По умолчанию CREATE INDEX создает индексы типа B-дерево, что эффективно в большинстве случаев. В следующем примере создается индекс с типом B-дерево для столбца title в таблицах books:

CREATE INDEX table_idx ON books (title);

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

CREATE INDEX name ON table USING HASH (column);

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

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

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

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

ALTER INDEX distributors_idx RENAME TO suppliers_idx;

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

ALTER INDEX suppliers_idx SET TABLESPACE my_tablespace;

Чтобы удалить индекс, используйте DROP INDEX.

Следующая команда удаляет индекс suppliers_idx:

DROP INDEX suppliers_idx;

Перестроение индексов

В PostgreSQL есть команда REINDEX, которая позволяет перестроить индексы. REINDEX перестраивает индекс, используя данные, хранящиеся в таблице индекса, и заменяет старую копию индекса. REINDEX может быть полезен в следующих сценариях:

Индекс поврежден и больше не содержит корректных данных. Хотя теоретически этого никогда не должно происходить, на практике индексы могут быть повреждены из-за ошибок программного обеспечения или сбоев оборудования. REINDEX предоставляет метод восстановления.
Индекс содержит много пустых или почти пустых страниц. Это может произойти с индексами типа B-дерево в PostgreSQL при определенных сценариях доступа. REINDEX записывает новую версию индекса без пустых страниц, чтобы уменьшить место, занимаемое индексом. Для получения дополнительной информации обратитесь к статье Routine Reindexing.
Параметр хранения индекса (например, fillfactor) был изменен с помощью ALTER INDEX, и теперь требуется, чтобы это изменение вступило в силу.
Если при использовании параметра CONCURRENTLY построение индекса завершается неудачей, этот индекс становится невалидным. Используйте REINDEX, чтобы перестроить его. Обратите внимание, что только команда REINDEX INDEX может перестроить индекс в неблокирующем режиме.

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

В следующем примере перестраивается один индекс:

REINDEX INDEX my_index;

Команда ниже перестраивает все индексы в таблице my_table:

REINDEX TABLE my_table;

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

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

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

К таблице часто выполняются следующие запросы:

SELECT * FROM authors WHERE first_name = "value1" AND last_name = "value2";

Вы можете определить составной индекс для столбцов first_name и last_name:

CREATE INDEX idx_authors_names ON authors (last_name, first_name);

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

Составные индексы следует использовать осторожно. В большинстве случаев индекс по одному столбцу работает эффективно и экономит время и место. Индексы с более чем тремя столбцами вряд ли будут полезны, если таблица не используется однообразно. Описание преимуществ различных конфигураций индексов можно найти в статьях Combining Multiple Indexes и Index-Only Scans and Covering Indexes.

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

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

CREATE UNIQUE INDEX <имя> ON <таблица> (<столбец> [, ...]);

Например:

CREATE UNIQUE INDEX ind_unique ON table1 (column1, column2);

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

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

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

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

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

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

SELECT * FROM table1 WHERE lower(column1) = 'value1';

Этот запрос может использовать индекс, определенный для результата функции low(column1):

CREATE INDEX idx_lower_column1 ON table1 (lower(column1));

Если этот индекс объявлен как уникальный (UNIQUE), он предотвратит добавление строк, в которых значения column1 совпадают или различаются только регистром. Таким образом, индексы выражений также можно использовать для обеспечения ограничений, которые нельзя записать как простые ограничения уникальности.

В качестве другого примера можно привести следующий запрос:

SELECT * FROM authors WHERE (first_name || ' ' || last_name) = 'Ivan Bunin';

Для этого запроса целесообразно создать такой индекс:

CREATE INDEX idx_names ON authors ((first_name || ' ' || last_name));

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

Поддержка индексируемых выражений обходится довольно дорого, поскольку выражения необходимо вычислять при добавлении каждой строки и при каждом последующем обновлении. Однако индексируемое выражение не вычисляется повторно во время индексированного поиска, поскольку его значения уже сохранены в индексе. В обоих приведенных выше примерах PostgreSQL рассматривает запрос как WHERE <индексированный столбец> = 'constant', и скорость поиска эквивалентна любому другому простому индексному запросу. Индексы выражений полезны, когда скорость извлечения данных важнее скорости вставки и обновления.

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

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

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

Рассмотрим следующий пример — логи доступа к веб-серверу хранятся в базе данных.

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

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

CREATE INDEX log_ip_idx 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-адрес, исключенный из индекса:

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

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

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

CREATE INDEX orders_unpaid_inx ON orders (order_nr)
    WHERE paid is not true;

Возможный запрос на использование этого индекса может быть:

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

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

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

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

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

SELECT * FROM orders WHERE order_nr = 4201;

Заказ 4201 может быть как оплаченным, так и неоплаченным.

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

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

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

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

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

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

Контроль использования индекса

Хотя индексы в PostgreSQL не требуют обслуживания или настройки, важно проверять, какие индексы действительно используются. Можно использовать команду EXPLAIN, чтобы убедиться в использовании индексов для отдельного запроса. Этот подход описан в статье Анализ запросов.

Также можно собрать общую статистику использования индексов на работающем сервере, как описано в статье The Statistics Collector. Например, поле idx.scan представления pg_stat_all_indexes показывает количество сканирований, инициированных для этого индекса. Если значение равно нулю, индекс не используется.

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

Всегда сначала выполняйте ANALYZE. Эта команда собирает статистику о распределении значений в таблице для оценки количества строк, возвращаемых запросом. Такую информацию использует планировщик, чтобы определить реалистичность затрат для каждого возможного плана запроса. Без реальной статистики будут рассматриваться некоторые значения по умолчанию, почти наверняка не соответствующие действительности. Практически невозможно понять, как приложение использует индекс, не запустив ANALYZE. За подробностями обратитесь к статьям Updating Planner Statistics и The Autovacuum Daemon.
Используйте реальные данные для экспериментов. Анализируя производительность системы на тестовых данных, вы поймете, какие индексы нужны для тестовых данных, но не более того.

Особенно неэффективно использовать очень маленькие наборы тестовых данных. Хотя индекс будет использован для извлечения 1000 строк из 100 000, маловероятно, что он понадобится для выбора 1 строки из 100. 100 строк, скорее всего, поместятся на одной странице данных на диске, и никакой другой план не будет лучше, чем простое сканирование 1 страницы.

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

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней