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

Мы научили наш сервис визуализации планов 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-функций, выдающих всегда один и тот же результат.

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

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

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

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

  • первый может использовать наиболее общий индекс и не зависит от константы

  • второй требует специального индекса с фиксацией константы

  • третий не может быть проиндексирован из-за вариативности 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 - увы, оптимизация не даст эффекта.

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

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

---

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

• запросу не хватило worker'ов для максимальной эффективности параллельного выполнения

• план имеет аномальное время выполнения существенно больше всех узлов

• время планирования запроса превышает само его выполнение, что критично для частых и быстрых запросов, где ее лучше решать через PREPARE