Псс, парень… индекс нужен?

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

Мы научили наш сервис визуализации планов PostgreSQL отвечать на эти вопросы, и под катом расскажем, чем именно он руководствуется в своих рекомендациях.

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

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

Чтобы не начинать с самых азов, договоримся, что пользоваться EXPLAIN все умеют, и что такое Seq Scan, Index Scan и Bitmap Index Scan знают. А если нет - стоит почитать переводы хороших постов Hubert 'depesz' Lubaczewski.

Проблема #1: Seq Scan

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

CREATE TABLE pg_class_copy AS TABLE pg_class;

Посмотрим, как реагирует база на попытку найти идентификаторы всех последовательностей (sequence), которые есть у нас в базе:

explain (analyze, buffers, costs off, verbose)
SELECT oid FROM pg_class_copy WHERE relkind = 'S';

Поскольку ни одного индекса на нашей таблице нет, то получим самый простой вариант, который может встретиться в плане Seq Scan:

Seq Scan on pg_class_copy (actual time=0.017..0.105 rows=2 loops=1)
  Output: oid
  Filter: (pg_class_copy.relkind = 'S'::"char")
  Rows Removed by Filter: 427
  Buffers: shared hit=11

Filter - это как раз то самое условие, которое заставило PostgreSQL из 429 прочитанных записей отбросить 427 и оставить для нас только 2. И это плохо - мы прочитали в 200 раз больше необходимого количества записей!

Способы индексации

Индекс по полю/выражению

Очевидно, что сразу напрашивается индекс по значению поля pg_class_copy(relkind):

CREATE INDEX ON pg_class_copy USING btree(relkind);

И мы видим, что вместо фильтрации наше выражение теперь ушло в условие индексного поиска Index Cond, а сам узел превратился в Index Scan:

Index Scan using pg_class_copy_relkind_idx on pg_class_copy (actual time=0.010..0.011 rows=2 loops=1)
  Output: oid
  Index Cond: (pg_class_copy.relkind = 'S'::"char")
  Buffers: shared hit=2

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

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

CREATE INDEX ON pg_class_copy USING btree(oid) -- индексируемое поле
  WHERE relkind = 'S';                         -- условие применения индекса

Обратите внимание, что узел превратился в Index Only Scan, а вот вынесенное нами условие исчезло из плана вовсе:

Index Only Scan using pg_class_copy_oid_idx on pg_class_copy (actual time=0.012..0.013 rows=2 loops=1)
  Output: oid
  Heap Fetches: 0
  Buffers: shared hit=2

Такое поведение характерно только для узлов Index [Only] Scan - в Seq Scan нам и так видны все условия сразу, а в Bitmap Heap Scan мы увидим условия дополнительной проверки записей страниц в строке Recheck Cond.

Неиндексируемые выражения

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

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

Если на совсем простых примерах:

На что обратить внимание:

1) Из всех этих вариантов условий, только первый допускает создание индекса (...) WHERE ts >= '2022-04-01 00:00:00'::timestamp, во всех остальных случаях правая часть не-иммутабельна.

2) Хотя все три последних варианта математически эквивалентны, но...

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

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

Пока мы использовали только btree - самый простой и "дефолтный" из всех типов индексов, которые PostgreSQL поддерживает "из коробки", его даже можно можно опускать при описании индекса:

CREATE INDEX ON pg_class_copy(relkind);

Но типов индексов в PostgreSQL гораздо больше, и каждый из них обладает своими возможностями и ограничениями, но основное отличие - поддерживаемые типы полей (на самом деле, конечно, произвольных IMMUTABLE-выражений от них) и операторы.

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

btree

Операторы линейного порядка (<, <=, =, >=, >):

числовые (smallint, integer, bigint, numeric, real, double precision)
хронологические (date, time, time without time zone, time with time zone, timestamp, timestamp without time zone, timestamp with time zone)
uuid
текстовые (varchar, text)

Операторы префиксного поиска (~<~, ~<=~, ~, ~>=~, ~>~) с использованием дополнительных классов операторов:

текстовые (varchar, text)

[подробнее: обзор]

hash

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

[подробнее: обзор]

gist

Операторы геометрических отношений (<<, &<, &>, >>, <@, @>, ~=, &&, <<|, &<|, |&>, |>>, ~, @):

геометрические (box, circle, poly, point)

Операторы для сетевых адресов (<<, <<=, >>, >>=, =, <>, <, <=, >, >=, &&):

сетевые адреса (inet)

Операторы для диапазонов (=, &&, @>, <@, <<, >>, &<, &>, -|-):

диапазоны числовые (int4range, int8range, numrange)
диапазоны хронологические (daterange, tsrange, tstzrange)

Операторы текстового поиска (<@, @>, @@) :

FTS-типы (tsquery, tsvector)

[подробнее: документация, обзор]

Дополнительно:

поддерживает оператор упорядочивания <->, который позволяет осуществлять индексный kNN-поиск
при использовании расширения btree_gist поддерживает операторы и типы btree

spgist

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

[подробнее: документация, обзор]

gin

Операторы для массивов (&&, @>, <@, =):

массивы (anyarray)

Операторы jsonb-ключей (@>, @?, @@, ?, ?|, ?&):

jsonb

Операторы jsonb-путей (@>, @?, @@):

jsonb

Операторы текстового поиска (@@, @@@) :

tsvector

[подробнее: документация, обзор]

Дополнительно:

при использовании расширения btree_gin поддерживает операторы и типы btree

brin

Блочный индекс с возможностями btree.

С одной стороны, позволяет эффективно индексировать большие блоки данных, с другой - неэффективен для небольших, поскольку всегда получается Bitmap Heap Scan.

[подробнее: обзор]

Условия применимости

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

поддержка нужного оператора;

не стоит пытаться использовать btree-индекс, если у вас условие с <>, но если у вас ~>=~, не забудьте text_pattern_ops.

поддержка нужного типа;

хотите что-то искать в jsonb - только gin, если надо сочетать <, =, > и <@ - смотрим на btree_gist/btree_gin.

поддержка нескольких полей;

если необходима, то hash и spgist сразу отпадают.

количество данных;

если возвращается мало записей, то brin вам не нужен.

Теперь, зная все это, посмотрим, что нам порекомендует explain.tensor.ru:

Наиболее релевантным для данного случая сервис выбрал обычный btree-индекс по полю фильтрации:

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

Проблема #2: BitmapAnd

Для демонстрации второй проблемы давайте возьмем табличку из прошлой статьи про способы работы с EAV-структурой:

CREATE TABLE tst_eav AS
SELECT
  (random() * 1e4)::integer e -- 10k объектов
, (random() * 1e2)::integer a -- 100 характеристик
, (random() * 1e2)::integer v -- 100 вариантов значений
FROM
  generate_series(1, 1e6);    -- 1M записей о значениях

CREATE INDEX ON tst_eav(a);
CREATE INDEX ON tst_eav(v);

Пробуем отобрать сразу по двум ключам:

explain (analyze, buffers, costs off)
SELECT * FROM tst_eav WHERE a = 1 AND v = 1;
Bitmap Heap Scan on tst_eav (actual time=1.495..1.603 rows=98 loops=1)
  Recheck Cond: ((a = 1) AND (v = 1))
  Heap Blocks: exact=97
  Buffers: shared hit=119
  ->  BitmapAnd (actual time=1.466..1.466 rows=0 loops=1)
        Buffers: shared hit=22
        ->  Bitmap Index Scan on tst_eav_a_idx (actual time=0.651..0.651 rows=10036 loops=1)
              Index Cond: (a = 1)
              Buffers: shared hit=11
        ->  Bitmap Index Scan on tst_eav_v_idx (actual time=0.627..0.627 rows=9963 loops=1)
              Index Cond: (v = 1)
              Buffers: shared hit=11

Очевидно, что каждый из Bitmap Index Scan "наметил" к дальнейшей проверке по 10K записей, а всего нам их оказалось нужно 98. Посмотрим внимательно на Recheck Cond - там два условия, которые мы можем комбинировать как в варианте с Seq Scan:

(a, v)
(a) WHERE v = 1
(v) WHERE a = 1
(?) WHERE a = 1 AND v = 1

Попробуем первый вариант с составным индексом как наиболее типовой:

Bitmap Heap Scan on tst_eav (actual time=0.036..0.117 rows=98 loops=1)
  Recheck Cond: ((a = 1) AND (v = 1))
  Heap Blocks: exact=97
  Buffers: shared hit=100
  ->  Bitmap Index Scan on tst_eav_a_v_idx (actual time=0.021..0.021 rows=98 loops=1)
        Index Cond: ((a = 1) AND (v = 1))
        Buffers: shared hit=3

Теперь вместо 22 страниц данных мы прочитали всего 3 - и это хорошо!

Что же нам посоветуют сделать авторекомендации explain?..

Три варианта возможных индексов - на усмотрение разработчика

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

Проблема #3: Limit - Sort - Scan

Давайте чуть модифицируем запрос, и найдем запись с минимальным v для конкретного заданного a:

explain (analyze, buffers, costs off)
SELECT * FROM tst_eav WHERE a = 1 ORDER BY v LIMIT 1;
Limit (actual time=49.178..49.178 rows=1 loops=1)
  Buffers: shared hit=3048 read=99
  ->  Gather Merge (actual time=49.177..55.782 rows=1 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=5425 read=99
        ->  Sort (actual time=30.746..30.746 rows=1 loops=3)
              Sort Key: v
              Sort Method: top-N heapsort  Memory: 25kB
              Buffers: shared hit=5425 read=99
              Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
              Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
              ->  Parallel Seq Scan on tst_eav (actual time=0.023..30.286 rows=3345 loops=3)
                    Filter: (a = 1)
                    Rows Removed by Filter: 329988
                    Buffers: shared hit=5307 read=99

"Параллельность" Seq Scan не должна нас смущать, и для условия a = 1 мы ровно как в первом случае можем порекомендовать варианты индексов:

(a)
(?) WHERE a = 1

Но если мы поднимемся выше, то увидим, что Sort-узел хранит информацию о дополнительно используемых полях: Sort Key: v. Так почему бы нам не расширить индексы ключом сортировки?

(a, v)
(v) WHERE a = 1

Попробуем первый из них (a, v) - тем более, он же попал и в рекомендации предыдущей проблемы:

Limit (actual time=0.023..0.023 rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using tst_eav_a_v_idx on tst_eav (actual time=0.021..0.021 rows=1 loops=1)
        Index Cond: (a = 1)
        Buffers: shared hit=4

Наш запрос ускорился больше чем в 2000 раз! Но при дальнейших оптимизациях надо быть много аккуратнее - в плане теперь вообще не фигурирует условие сортировки по v .

Замечу, что такое расширение индекса имеет смысл только в случае использования оператора = или IS NULL для всех остальных полей, иначе это не сможет использоваться эффективно. То есть, например, для условия a > 1 - увы, оптимизация не даст эффекта.

Посмотрим, что нам присоветует сервис:

Пара вариантов ускоряющих индексов для Limit - Sort - Scan

И прямо-таки ровно то, что мы и ожидали!

А еще explain.tensor.ru научился замечать, когда...

запросу не хватило worker'ов для максимальной эффективности параллельного выполнения
план имеет аномальное время выполнения существенно больше всех узлов
время планирования запроса превышает само его выполнение, что критично для частых и быстрых запросов, где ее лучше решать через PREPARE

Псс, парень… индекс нужен?

Проблема #1: Seq Scan

Способы индексации

Индекс по полю/выражению

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

Неиндексируемые выражения

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

btree

hash

gist

spgist

gin

brin

Условия применимости

Проблема #2: BitmapAnd

Проблема #3: Limit - Sort - Scan

Report Page