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

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

Мы научили наш сервис визуализации планов 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