В этом переводе к старту курса по 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#
От основ — в глубину
А также:
