[Перевод] Как мы использовали расширенную статистику Postgres и ускорились в 2850 раз
В этом переводе к старту курса по Fullstack-разработке на Python напоминаем о том, насколько важно знать технологии в деталях, грамотно применять их и планировать работу в целом. Цифра 2850 в заголовке — не преувеличение: ранее занимавший две минуты запрос в базе данных компании Affinity сегодня выполняется за 42 миллисекунды. Подробности, как всегда, под катом. А если вам нужен план развития навыков с большим количеством практики, вы можете обратить внимание на наши курсы.
Подобно отделу транспортных средств [в США], планировщик запросов PostgreSQL — это мощная, таинственная сущность, которой мы полуслепо доверяем своё благополучие. На ней лежит важнейшая ответственность за выбор наиболее эффективного плана выполнения каждого запроса. Вот пример медленного запроса с нашего веб-сервера и выбранный Postgres неэффективный план:
SELECT count(*) AS "count"
FROM (
SELECT "lists_entries".*
FROM "lists_entries"
WHERE (
("lists_entries"."org_id" = ?) AND
("lists_entries"."list_id" = ?) AND
(
"lists_entries"."company_id" IN (
SELECT "company_id"
FROM "entity_values"
WHERE (
("entity_attribute_id" = ?) AND
(("value_location")."country" = 'United States') AND
("org_id" = ?)
)
)
) AND
(
"lists_entries"."id" IN (
SELECT "list_entry_id"
FROM "entity_values"
WHERE (
("entity_attribute_id" = ?) AND
("value_dropdown_option_id" = ?) AND
("org_id" = ?)
)
)
)
)
OFFSET 0
) AS "t1"
LIMIT 1;QUERY PLAN
--------------------------------------------------------------------
Limit (cost=41.70..41.71 rows=1 width=8) (actual time=4122081.782..122081.792 rows=1 loops=1)
-> Aggregate (cost=41.70..41.71 rows=1 width=8) (actual time=122081.79..122081.79 rows=1 loops=1)
-> Nested Loop Semi Join (cost=1.57..41.69 rows=1 width=44) (actual time=0.818..122081.782 rows=33 loops=1)
-> Nested Loop Semi Join (cost=1.01..25.07 rows=1 width=4) (actual time=0.079..122074.806 rows=1958 loops=1)
-> Index Scan using lists_entries_org_id_list_id_index on lists_entries (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..5.564 rows=13769 loops=1)
Index Cond: ((org_id = ?) AND (list_id = ?))
-> Index Scan using entity_values_org_id_entity_attribute_id_company_id_index on entity_values (cost=0.57..8.59 rows=1 width=4) (actual time=0.014..8.865 rows=0 loops=13769)
Index Cond: ((org_id = ?) AND (entity_attribute_id = ?) AND (company_id = lists_entries.company_id))
Filter: ((value_location).country = 'United States'::text)
Rows Removed by Filter: 1
-> Index Scan using entity_values_org_id_list_entry_id_index on entity_values entity_values_1 (cost=0.56..8.59 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1958)
Index Cond: ((org_id = 4796) AND (list_entry_id = lists_entries.id))
Filter: ((entity_attribute_id = ?) AND (value_dropdown_option_id = ?))
Rows Removed by Filter: 2
Planning Time: 1.828 ms
Execution Time: 122081.792 ms
(16 rows)Copied successfully!
Самый расточительный шаг — второе вложенное соединение циклов:
Nested Loop Semi Join (cost=1.01..25.07 rows=1 width=4) (actual time=0.079..122074.806 rows=1958 loops=1).
Postgres подсчитала, что этот шаг вернёт около 1 строки, что было дикой недооценкой — на самом деле он вернул 1958 строк и занял около 122 секунд. Благодаря грамотному использованию статистики Postgres мы сократили время выполнения этого запроса с 2 минут до 42 миллисекунд — почти в 3000 раз, но до погружения в настройки статистики для понимания расскажем, как работает планировщик Postgres.
Статистика и планы запросов Postgres
Основы
Статистика — это собранные для подбора плана запросов данные Postgres. Postgres из коробки делает выборку возможных значений каждого столбца каждой таблицы, чтобы создать гистограммы и список самых частых значений. Эти значения оценивают, сколько строк получится после применения некоторого набора фильтров к таблице.
В случае больших таблиц планировщик не может отслеживать каждое значение столбца, вместо этого для каждого столбца он делает выборку значений. Количество выборок настраивается так:
ALTER TABLE table ALTER column SET STATISTICS {-1 ..10000}где -1 устанавливает значение по умолчанию 100 (документация). Это число задаёт количество корзин в гистограмме и количество сохраняемых встречающихся чаще всего значений. Недостаток увеличения значений собираемой статистики столбца заключается в том, что в pg_statistic хранится больше данных и ANALYZE таблицы столбца замедляется. Подробнее об этом рассказывается в документации Postgres.
Расширенная статистика
Расширенная статистика — это пользовательские объекты, которые указывают Postgres собирать определённые виды данных относительно наборов столбцов, а не для отдельных столбцов. Без расширенной статистики Postgres рассматривает влияние фильтров на таблицу независимо.
Пример: база данных содержит 10 записей об артистах, каждая из которых имеет 10 ссылающихся на неё записей об альбомах, а каждая запись об альбоме имеет 10 ссылающихся на неё записей о песнях. В общей сложности это 10 исполнителей, 100 альбомов и 1 000 песен. Если выполнить запрос ниже:
SELECT * FROM songs WHERE (artists_id = 1 and album_id = 1);то при идеальной выборке план может выглядеть так:
Index Scan using songs_artists_id_album_id_index on songs (cost=0.28..6.05 rows=1 width=159) (actual time=5.555..5.562 rows=10 loops=1)
Index Cond: ((artists_id = 1) AND (album_id = 1))
Planning Time: 311.482 ms
Execution Time: 9.266 ms
(4 rows)
(cost=0.28...6.05 rows=1 width=159) — оценка планировщика, (actual time=5.555...5.562 rows=10 loops=1) — фактические результаты выполнения плана. Предполагалось, что будет возвращена 1 строка, но на самом деле их было 10.
Планировщик вычислил оценку строк, сначала взяв общее количество Songs (1000), затем рассмотрев фильтр
artists_id.10% песен имеют
artists_id = 1, таким образом, остаётся 100 песен.Далее рассматривается фильтр
album_id. 1% песен имеютalbum_id = 1, поэтому остаётся 1 песня.
Postgres упускает ключевой момент: artist_id и album_id сильно коррелируют. На самом деле знание album_id однозначно определяет artist_id. Если бы Postgres знала об этом, то в оценке могла бы задействовать только фильтр album_id = 1 и получить правильный результат — 10 песен.
Такая корреляция в Postgres указывается с помощью статистики зависимостей. Эта статистика отражает частоту, с которой каждый столбец однозначно определяет другой столбец. Статистика зависимости для (artist_id, album_id) может дать следующее:
CREATE STATISTICS album_id_artist_id_dep_stt (dependencies) ON album_id, artist_id FROM songs;ANALYZE songs;SELECT stxname, stxkeys, stxddependencies
FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
WHERE stxname = 'stts';
stxname | stxkeys | stxddependencies
---------+---------+------------------------------------------
stts | 1 5 | {"1 => 5": 0.1, "5 => 1": 1.0}
(1 row)
1 и 5 в stxkeys и stxddependencies относятся к 1 и 5 столбцам таблицы songs, это artist_id и album_id, соответственно. Значение для »1 => 5» составляет 0,1, поскольку artist_id определяет 10% album_id.
Поскольку album_id всегда определяет artist_id, значение »5 => 1» равно 1.0. Когда Postgres фильтрует по столбцам с соответствующей статистикой зависимости, она может воспользоваться статистикой для уточнения оценки. Конечно, есть другие виды расширенной статистики, но для такого типа распределения данных статистика зависимости — самый здравый выбор.
Далее — об одной из особенностей расширенной статистики. Postgres умеет использовать её только при фильтрации именно по тем столбцам, на которые ссылается статистика, а также при фильтрации с использованием простых условий равенства, например, artist_id = 5, а не artist_id IN (5, 6) или artist_id < 10.
Использование расширенной статистики может привести к неинтуитивному выбору индексов. Если статистика зависимости указывает Postgres на избыточность фильтра столбцов, как в случае artist_id и album_id, она может задействовать ссылающийся только на один из столбцов индекс. В случае songs она может использовать индекс только на (album_id), а не на (artist_id, album_id), когда есть оба столбца.
Стратегии соединения (Join)
В Postgres есть три варианта объединения таблиц:
Соединение вложенных циклов. Используя эту стратегию объединения, Postgres просматривает каждую строку в левом отношении и сканирует правое отношение в поисках строк, удовлетворяющих условию объединения, при этом в идеале используя индекс. Стратегия эффективна в случаях, когда в левом отношении очень мало строк.
Соединение слияния. Из документации: «каждое отношение сортируется по атрибутам соединения до начала операции. Затем два отношения сканируются параллельно, и совпадающие строки объединяются, образуя объединённые строки. Этот вид объединения более привлекателен, поскольку каждое отношение сканируется только один раз. Требуемая сортировка достигается либо явным шагом сортировки, либо сканированием отношения в нужном порядке с использованием индекса по ключу соединения».
Хеш-соединение. Из документации: «нужное отношение сначала сканируется и загружается в хеш-таблицу с использованием присоединяемых атрибутов в качестве хеш-ключей. Далее сканируется левое отношение, и соответствующие значения каждой найденной строки используются в качестве хеш-ключей, чтобы найти совпадающие строки в таблице».
Для наших задач главное отметить, что, по сравнению с другими стратегиями соединения, накладные расходы на вложенные циклы очень малы. Однако это объединение может пойти не так, когда в левом отношении много строк. Например, предположим, что в левом отношении имеется 1 000 строк, а для доступа к правому отношению Postgres использует индекс. Если каждый доступ к индексу занимает 4 мс, то всё соединение займёт 4 секунды, для ответа на запрос пользователя это слишком медленно.
Статистика PostgreSQL на практике
Теперь, когда мы понимаем различные типы соединений, давайте вернёмся к соединению вложенных циклов, которое показалось нам проблематичным. Не вдаваясь в подробности модели данных Affinity, достаточно знать, что в наших таблицах entity_values и lists_entries столбец org_id однозначно определяется list_id или entity_attribute_id. Это означает, что в оценке выборки набора фильтров по этим столбцам фильтры не следует рассматривать по отдельности.
Запросы замедлялись из-за того, что Postgres недооценила количество получаемых в результате фильтрации строк, а значит из-за недооценки решала использовать соединение вложенных циклов.
Принятые меры
Вернёмся к проблемному запросу. Самым затратным шагом было обращение к индексу entity_values_org_id_entity_attribute_id_company_id_index 13 769 раз.
Чтобы побудить планировщика использовать другую стратегию соединения, нам нужно было улучшить его оценки для фильтров на lists_entries и entity_values. Исходя из фильтров, среди других таблиц и столбцов различных шаблонов запросов мы достигли максимального значения статистики по столбцам для:
lists_entries:
- org_id
- list_identity_values:
- org_id
- entity_attribute_idА также, поскольку и list_id, и entity_attribute_id однозначно определяют org_id, то в других статистиках зависимостей для других таблиц и столбцов добавили статистики по зависимостям:
lists_entries (list_id, org_id)
entity_values (entity_attribute_id, org_id)После этого Postgres выбрала такой план запроса:
QUERY PLAN
--------------------------------------------------------------------
Limit (cost=466.34..466.35 rows=1 width=8) (actual time=37.736..37.737 rows=1 loops=1)
-> Aggregate (cost=466.34..466.35 rows=1 width=8) (actual time=37.736..37.736 rows=1 loops=1)
-> Nested Loop Semi Join (cost=404.32..466.33 rows=1 width=44) (actual time=23.545..37.727 rows=36 loops=1)
-> Hash Semi Join (cost=403.76..458.04 rows=1 width=4) (actual time=22.076..28.610 rows=1972 loops=1)
Hash Cond: (lists_entries.company_id = entity_values.company_id)
-> Bitmap Heap Scan on lists_entries (cost=362.16..380.21 rows=13744 width=8) (actual time=2.579..7.395 rows=13887 loops=1)
Recheck Cond: ((org_id = ?) AND (company_id IS NOT NULL) AND (list_id = ?))
Heap Blocks: exact=3141
-> BitmapAnd (cost=362.16..362.16 rows=9 width=0) (actual time=2.212..2.212 rows=0 loops=1)
-> Bitmap Index Scan on lists_entries_org_id_company_id_index (cost=0.00..135.52 rows=8145 width=0) (actual time=1.142..1.143 rows=15166 loops=1)
Index Cond: (org_id = ?)
-> Bitmap Index Scan on lists_entries_list_id_index (cost=0.00..219.51 rows=13744 width=0) (actual time=0.944..0.944 rows=13887 loops=1)
Index Cond: (list_id = ?)
-> Hash (cost=40.10..40.10 rows=120 width=4) (actual time=19.484..19.484 rows=4914 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 237kB
-> Index Scan using entity_values_org_id_entity_attribute_id_company_id_index on entity_values (cost=0.57..40.10 rows=120 width=4) (actual time=0.019..18.791 rows=4914 loops=1)
Index Cond: ((org_id = ?) AND (entity_attribute_id = ?))
Filter: ((value_location).country = 'United States'::text)
Rows Removed by Filter: 19190
-> Index Scan using entity_values_org_id_list_entry_id_index on entity_values entity_values_1 (cost=0.56..4.42 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1972)
Index Cond: ((org_id = ?) AND (list_entry_id = lists_entries.id))
Filter: ((entity_attribute_id = ?) AND (value_dropdown_option_id = ?))
Rows Removed by Filter: 2
Planning Time: 3.559 ms
Execution Time: 37.809 ms
(25 rows)
Time: 42.189 msЗдесь оценки намного точнее, и планировщик для внутреннего соединения (INNER JOIN) выбрал хеш-соединение. Вместо 2 минут запрос занял 42 миллисекунды.
Заключение
Увеличение статистики по столбцам и добавление статистики зависимостей очень помогло, но это только начало прогресса. Как вы могли заметить, планировщик по-прежнему недооценивает количество получаемых в результате внутреннего соединения строк.
Хотя в этот раз OUTER JOIN вложенного цикла не занял много времени, нетрудно представить себе запрос, в котором INNER JOIN приводит к получению большого количества строк, а OUTER JOIN (внешнее соединение) становится узким местом.
Так планирование влияет на результаты. Именно поэтому программы наших курсов-профессий — не просто упорядоченный набор знаний. Это планы приобретения навыков, составленные практикующими экспертами, которых достаточно, чтобы вы устроились на новую работу, поэтому вы можете смело начинать карьеру с наших специализаций или прокачивать у нас навыки, если вам интересна Fullstack-разработка на Python, Frontend или другие направления IT.
Data Science и Machine Learning
Python, веб-разработка
Мобильная разработка
Java и C#
От основ — в глубину
А также:
