У меня был дорогостоящий ~20-секундный запрос к массивной таблице PostgreSQL с ~500 миллионами строк. Я сократил этот запрос примерно до 2 миллисекунд. Быстрый ответ заключается в том, что не хватало индекса. В этом посте мы рассмотрим, как мы пришли к такому выводу и как мы определили, какой индекс нужно добавить.
tl;dr: запрос, который мы рассмотрим в этой заметке, работал медленно из-за отсутствия индекса на столбце временной метки created_at. Мы попробовали отдельно добавить BRIN и B-Tree индексы. При добавлении индекса BRIN улучшения производительности не наблюдалось. Однако, когда был добавлен индекс B-Tree, мы увидели огромное улучшение производительности. Самый дорогой запрос сократился с ~20 секунд до ~2 миллисекунд. Ускорение в 10 000 раз. Среднее время выполнения этого запроса теперь даже немного ниже.
Читайте дальше, если вам нужны подробности.
Примечание: все запросы, о которых я расскажу, выполняются на локально восстановленном снимке производственных данных. Будьте осторожны при добавлении подобных индексов в продакшн. Делайте это concurrently и обязательно следите за своим приложением в процессе.
Модель данных
Во-первых, давайте посмотрим на достаточный снимок модели данных.
У нас есть массивная таблица events, она содержит сотни миллионов строк. Каждое из этих events связано с определенной записью в таблице users.
Пользователи делают что-то, и эти действия записываются как события в таблице events. Пользователи делают много вещей, поэтому событий много.
Медленный запрос
Запрос, который наша программа мониторинга отметила как медленный, был запросом, в котором мы пытались найти два последних события для определенного пользователя.
select * from events
where user_id = 123
order by created_at desc
limit 2;
У некоторых пользователей гораздо больше событий, чем у других, поэтому медлительность этого запроса может варьироваться в довольно широких пределах. У этого конкретного пользователя более ~200 000 событий. Именно такие случаи выявили узкое место в конструкции этой таблицы.
Давайте проанализируем этот запрос, чтобы лучше понять, в чем проблема.
explain analyze
select * from events
where user_id = 123
order by created_at desc
limit 2;
Запуск explain analyze с этим запросом дает нам действительно хорошее представление о том, что происходит, потому что он действительно выполняет запрос. Вот как выглядит его результат:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1066813.50..1066813.50 rows=2 width=114) (actual time=23207.023..23207.024 rows=2 loops=1)
-> Sort (cost=1066813.50..1067596.73 rows=313292 width=114) (actual time=23207.022..23207.022 rows=2 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on events (cost=5868.59..1063680.58 rows=313292 width=114) (actual time=74.682..23098.624 rows=222272 loops=1)
Recheck Cond: (user_id = 123)
Rows Removed by Index Recheck: 6448758
Heap Blocks: exact=67292 lossy=128418
-> Bitmap Index Scan on index_events_on_user_id (cost=0.00..5790.26 rows=313292 width=0) (actual time=57.349..57.349 rows=222272 loops=1)
Index Cond: (user_id = 123)
Planning time: 0.095 ms
Execution time: 23207.502 ms
(12 rows)
Число в заголовке — время выполнения в нижней части результата — 23207.502 ms. Это 23 секунды.
Затем я хочу обратить внимание на то, куда уходит большая часть этих 23 секунд. К счастью, в этом выводе explain analyze она довольно четко выделяется в одной области.
Обратите внимание на эту часть вывода, где происходит сортировка таблицы на основе created_at DESC.
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on events (cost=5868.59..1063680.58 rows=313292 width=114) (actual time=74.682..23098.624 rows=222272 loops=1)
Мы видим, что фактическое время варьируется от 74.682...23098.624. Именно здесь этот запрос проводит подавляющее большинство своего времени.
Глядя на индексы таблицы и выполняемый запрос, я, вероятно, мог бы предположить, что проблема заключается в отсутствии индекса на created_at. Я не хочу просто бессистемно добавлять индексы. Мне нужны цифры, подтверждающие это. Это подтверждает это. Мы на пути к исправлению этого.
BRIN против B-дерева
Итак, мы знаем, что нам нужен индекс на created_at, но какой индекс? По умолчанию, когда тип индекса не указан, используется B-Tree. Но я знаю, что слышал о том, что Postgres поддерживает другие типы индексов. Что насчет них?
Вот что говорится в блоге Crunchy Data об индексе BRIN.
В PostgreSQL 9.5 появилась функция, называемая индексами диапазона блоков (также известная как BRIN).
которая невероятно полезна для эффективного поиска в больших временных рядах
данных и занимает значительно меньше места на диске, чем стандартный индекс B-дерева.
стандартного индекса B-дерева.
«Большие данные временных рядов» — это именно то, с чем мы имеем дело, и индекс, занимающий меньше места на диске, звучит замечательно. Так что, похоже, стоит попробовать. На самом деле, давайте начнем с этого.
С индексом BRIN
Я начну с добавления индекса.
create index index_events_on_created_at
on events using brin (created_at);
Примечание: Добавление индекса BRIN в таблицу с ~500 миллионами строк заняло ~12 минут на моей рабочей машине.
Теперь я повторно выполню предыдущий запрос с помощью explain analyze:
> explain analyze select * from events where user_id = 123 order by created_at desc limit 2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1066813.50..1066813.50 rows=2 width=114) (actual time=23074.657..23074.658 rows=2 loops=1)
-> Sort (cost=1066813.50..1067596.73 rows=313292 width=114) (actual time=23074.655..23074.655 rows=2 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on events (cost=5868.59..1063680.58 rows=313292 width=114) (actual time=68.786..22975.087 rows=222272 loops=1)
Recheck Cond: (user_id = 123)
Rows Removed by Index Recheck: 6448758
Heap Blocks: exact=67292 lossy=128418
-> Bitmap Index Scan on index_events_on_user_id (cost=0.00..5790.26 rows=313292 width=0) (actual time=48.569..48.569 rows=222272 loops=1)
Index Cond: (user_id = 123)
Planning time: 2.097 ms
Execution time: 23075.473 ms
(12 rows)
К сожалению, этот результат выглядит очень похоже на тот, что был до добавления индекса. Трудно точно сказать, почему индекс BRIN не дает никаких преимуществ. Важно то, что мы провели измерения. Мы знаем, что, по крайней мере, для этого набора данных индекс BRIN — не выход.
Поскольку индекс BRIN нам не нужен, давайте откажемся от него, а затем попробуем B-Tree.
drop index index_events_on_created_at;
С индексом B-Tree
create index index_events_on_created_at
on events(created_at);
Примечание: Добавление индекса B-Tree к таблице с ~500 миллионами строк заняло ~8 минут на моей рабочей машине.
И снова мы запустим наш медленный запрос и посмотрим, как он работает.
> explain analyze select * from events where user_id = 123 order by created_at desc limit 2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..535.62 rows=2 width=114) (actual time=0.056..1.951 rows=2 loops=1)
-> Index Scan Backward using index_events_on_created_at on events (cost=0.57..83812631.72 rows=313292 width=114) (actual time=0.055..1.950 rows=2 loops=1)
Filter: (user_id = 123)
Rows Removed by Filter: 4819
Planning time: 0.116 ms
Execution time: 2.000 ms
(6 rows)
Помимо того, что запрос возвращается немедленно, я могу сказать по изменению формы и высоты плана запроса, что мы к чему-то пришли. Время выполнения также снизилось до 2.000 мс. Вторая строка плана запроса показывает, что используется новый индекс.
-> Index Scan Backward using index_events_on_created_at on events (cost=0.57..83812631.72 rows=313292 width=114) (actual time=0.055..1.950 rows=2 loops=1)
Это больше похоже на это. Это подтверждает решение добавить индекс B-Tree для столбца created_at в производстве. Как я уже упоминал в начале статьи, добавление такого индекса в любой производственной среде, но особенно для таблицы
такого размера, важно делать это последовательно и следить за показателями работоспособности вашего приложения во время процесса.
Кредит изображения на обложке: Kiyoshi on Unsplash