Оптимизационные трюки в PostgreSQL. Как быстро загрузить данные: часть 1
Пересказ статьи Mitchell Warr. PostgreSQL Optimization Tricks: How to Load Data Fast Part 1
Итак, у вас есть несколько сотен миллионов строк данных в таблице, и вы просто не получаете той скорости, что раньше. Она работает медленнее, чем когда была молодой и энергичной базой данных, и вы можете поклясться, что слышите, как она скрипит, когда вы ускоряете распределение памяти.
Вы открываете объяснение плана запроса и заглядываете под капот в чем-то типа https://explain.dalibo.com. Но что вы фактически можете сделать?
Давайте сначала сосредоточимся на нескольких основных моментах. В следующей части мы рассмотрим более сложные комбинации.
Индекс великолепно работает для быстрой фильтрации строк. Это надежный простой ответ, и вы можете соблазниться использовать его для медленного запроса. Если ваша таблица содержит 1000000 строк, и индекс может сократить это число от 1 до 1000 строк, то это хороший индекс.
Если узким местом вашего запроса является необходимость отфильтровать множество строк, чтобы получить их небольшое число, индекс идеально подходит. Без индекса планировщик, вероятно, будет использовать последовательное сканирование (Sequential Scan), чтобы обойти все строки по очереди.
Два столбца элементов, в первом их 20, а во втором - 4. Стрелка с названием "index" ведет от первого к последнему столбцу
Хотя посмотрите, что делает ваш план. Если вам необходимы 1000000 строк, чтобы агрегировать и сгруппировать их, то вы не можете отфильтровать их небольшое число. Индекс помогает лишь быстрей отфильтровать данные. Если в вашем плане запроса есть узел, который потребляет много времени на фильтрацию данных, то это идеальное место для индекса.
Он может иногда помочь с соединением двух таблиц. Если вы соединяете 1000000 строк с другими 100000 строк, то наличие индекса поможет при соединении каждой отдельной строки. Планировщик может использовать хэш-соединение, а не перекрестные ссылки каждой строки на каждую другую строку. Также имеются другие методы соединения, но индекс не сделает ничего особенного для стандартного соединения.
Когда у вас есть простой индекс плюс предложение WHERE, которое задействует его, вы все еще имеете последовательную проверку результирующих строк. Это означает, что быстрее будет добавить предложение WHERE в сам индекс, если можете. Если соединение более чем 1:1, это тоже может дать преимущество от использования предложения WHERE в индексе.
Прирост производительности, которое дает индекс, имеет, однако, свою цену в скорости записи. Прежде чем добавлять индекс, убедитесь в его полезности или в том, что вас не заботит скорость записи в таблицу.
Удивительно, что агрегация больших таблиц иногда может быть быстрой, а иногда медленней всего. Это расхождение в скорости обычно вызывается этапом сортировки перед агрегацией. Запрос будет отсортирован по порядку групп, чтобы сделать шаг агрегации более эффективным. Хотя сортировка больших объемов данных является медленной. Вот несколько приемов ускорить ее:
Иногда вы хотите соединить пачку таблиц, а затем агрегировать результаты. Это может оказаться медленным, если одна из этих таблиц очень большая, и при соединении таблиц вы обнаружите, что строки теперь отсортированы по ключу соединения. Ключи соединения зачастую не совпадают с ключами группировки. Если ключи соединения совпадают с ключами группировки, то вы можете вообще пропустить шаг сортировки.
Добавление правильных индексов может помочь, если они предварительно отсортированы по столбцу группировки. Это может сообщить PostgreSQL, как выполнять соединение, или вы можете добавить ORDER BY в запрос, чтобы дать подсказку PostgreSQL. Если вы соединяете по столбцу, отличному от столбца группировки, тогда вам может не повезти.
Планировщик запросов имеет множество различных приемов, которые могут использоваться в зависимости от того, что дальше делает запрос, поэтому имейте в виду эти возможности. В зависимости от происходящего планировщик может делать неожиданные вещи, которые аннулируют вашу оптимизацию. Я стараюсь просто избегать соединения перед агрегацией, когда она является проблемой, и выполняю соединение уже агрегированных результатов.
Если вы можете переписать запрос так, чтобы большая таблица агрегировалась первой, вы можете использовать сортированный индекс. Это можно выполнить, если соединяемая с ней таблица мала и используется только для дополнительной информации, например, фильтрации результатов. Быстрей получается сначала агрегировать по ключу соединения, соединять с этой небольшой таблицей, и использовать эту информацию для фильтрации и новой агрегации. Хотя иногда это может оказаться более медленным, и вы не узнаете этого, пока не попробуете.
Вы пишете простой запрос, все что он делает - это соединение нескольких таблиц. У вас небольшая таблица, которая отфильтровывается до 20 строк. Вы соединяете ее с дочерней таблицей, которая содержит пару миллионов строк. Теперь это медленно.
Есть несколько трюков, которые позволяют изменить ситуацию и заставить планировщик PostgreSQL принимать более правильные решения. Все сводится к тому, что вы хотите сделать с данными в конечном итоге. Если вы соединяете маленькую таблицу с большой для того, чтобы агрегировать или отфильтровать результаты, то вы можете изменить запрос, чтобы получить выигрыш в скорости.
Если вы сначала агрегируете таблицу, то можете радикально уменьшить число строк, которые вы затем соединяете.
Диаграмма двух различных процессов. Первый соединяет большое и малое число строк, что приводит к большому числу данных, которые требуется агрегировать. Во втором большие данные агрегируются до соединения с малым числом строк. Во втором процессе в целом обрабатывается меньше данных.
Рассмотрите эти два процесса. В первом вы видите, что мы должны сравнить малую таблицу со всей большой таблицей, а затем агрегировать. Во втором сначала выполняется агрегация, а затем выполняется соединение двух небольших наборов данных.
Наши проблемы здесь - соединение и сортировка. Агрегация единственной таблицы является быстрой, поскольку может использоваться сортированный индекс. Соединение миллиона строк является медленным, а сортировка после соединения миллиона строк даже еще медленней.
Здесь следует обратить внимание на одну вещь; планировщик может попытаться быть полезным и видит, что вы все еще соединяете малые и большие таблицы посредством агрегации. PostgreSQL может все же спланировать соединение перед группировкой. В этом случае вы можете применить искусное предложение CTE "with" и материализовать результат агрегации.
Это может помочь, но иногда становится только хуже. Результат материализации этих строк означает, что вы теряете все индексирование на сгруппированным СТЕ, и доступными для соединения окажутся лишь несколько вариантов. Однако в крайнем случае стоит попробовать.
Другие подходы
Если вы соединяете к таблицу, не ожидая агрегации, и вам нужно много строк данных, то вам придется в значительной степени полагаться на свои индексы.
Иногда планировщик будет медленным тут. Он должен предсказать окончательное число ожидаемых строк для оптимизации методов, которые он использует. Когда он планирует, что соединение будет иметь таблицу с небольшим числом строк, он не учитывает размер большой таблицы и использует неоптимальные подходы.
Например, если планировщик ожидает небольшое число строк, он будет использовать методы, оптимизированные для единственной страницы данных. Если данные разрастаются на множество страниц или даже достигает предела памяти, его можно ускорить, предоставив планировщику более точную оценку.
Проверьте, не делаете ли вы массовую фильтрацию после получения строк из индекса. Планировщик не знает, сколько строк будет отфильтровано после получения строк из индекса. Если этот случай имеет место, вы можете попробовать добавить некоторую фильтрацию непосредственно в индекс.
Иногда левое соединение большой таблицы может сказать PostgreSQL, что вы ожидаете, что вся таблица останется.
Целью ускорения запросов в PostgreSQL является насколько это возможно сокращение числа строк, с которыми выполняется работа. Чем с меньшим числом строк вы работаете, тем меньше памяти это занимает и тем более эффективным может быть планировщик. Чем к меньшему числу строк вы можете прийти, тем быстрей становятся соединения и сортировка.
Все, что вы пытаетесь сделать, чтобы ускорить планировщика, сводится к сокращению размера данных как можно раньше в выполняемом процессе.
Если вы хотите исследовать более конкретные примеры использования, перейдите к части 2
Ссылки по теме
1. Понимание хеш соединений в MySQL 8
2. Индексы PostgreSQL: что это такое и как они могут помочь
3. Что такое план выполнения и как его найти в PostgreSQL
4. Обзор соединений в PostgreSQL
5. Фильтрованные индексы
Индексы
Индекс великолепно работает для быстрой фильтрации строк. Это надежный простой ответ, и вы можете соблазниться использовать его для медленного запроса. Если ваша таблица содержит 1000000 строк, и индекс может сократить это число от 1 до 1000 строк, то это хороший индекс.
Если узким местом вашего запроса является необходимость отфильтровать множество строк, чтобы получить их небольшое число, индекс идеально подходит. Без индекса планировщик, вероятно, будет использовать последовательное сканирование (Sequential Scan), чтобы обойти все строки по очереди.
Два столбца элементов, в первом их 20, а во втором - 4. Стрелка с названием "index" ведет от первого к последнему столбцу
Хотя посмотрите, что делает ваш план. Если вам необходимы 1000000 строк, чтобы агрегировать и сгруппировать их, то вы не можете отфильтровать их небольшое число. Индекс помогает лишь быстрей отфильтровать данные. Если в вашем плане запроса есть узел, который потребляет много времени на фильтрацию данных, то это идеальное место для индекса.
Он может иногда помочь с соединением двух таблиц. Если вы соединяете 1000000 строк с другими 100000 строк, то наличие индекса поможет при соединении каждой отдельной строки. Планировщик может использовать хэш-соединение, а не перекрестные ссылки каждой строки на каждую другую строку. Также имеются другие методы соединения, но индекс не сделает ничего особенного для стандартного соединения.
Когда у вас есть простой индекс плюс предложение WHERE, которое задействует его, вы все еще имеете последовательную проверку результирующих строк. Это означает, что быстрее будет добавить предложение WHERE в сам индекс, если можете. Если соединение более чем 1:1, это тоже может дать преимущество от использования предложения WHERE в индексе.
Прирост производительности, которое дает индекс, имеет, однако, свою цену в скорости записи. Прежде чем добавлять индекс, убедитесь в его полезности или в том, что вас не заботит скорость записи в таблицу.
Агрегация
Удивительно, что агрегация больших таблиц иногда может быть быстрой, а иногда медленней всего. Это расхождение в скорости обычно вызывается этапом сортировки перед агрегацией. Запрос будет отсортирован по порядку групп, чтобы сделать шаг агрегации более эффективным. Хотя сортировка больших объемов данных является медленной. Вот несколько приемов ускорить ее:
- Используйте единый id группировки
- Используйте тип данных столбца группировки, который быстрей сортировать
- Используйте индекс, который предварительно отсортирован в нужном порядке или может быть использован для сортировки
- Ограничьте соединения перед агрегацией
Иногда вы хотите соединить пачку таблиц, а затем агрегировать результаты. Это может оказаться медленным, если одна из этих таблиц очень большая, и при соединении таблиц вы обнаружите, что строки теперь отсортированы по ключу соединения. Ключи соединения зачастую не совпадают с ключами группировки. Если ключи соединения совпадают с ключами группировки, то вы можете вообще пропустить шаг сортировки.
Добавление правильных индексов может помочь, если они предварительно отсортированы по столбцу группировки. Это может сообщить PostgreSQL, как выполнять соединение, или вы можете добавить ORDER BY в запрос, чтобы дать подсказку PostgreSQL. Если вы соединяете по столбцу, отличному от столбца группировки, тогда вам может не повезти.
Планировщик запросов имеет множество различных приемов, которые могут использоваться в зависимости от того, что дальше делает запрос, поэтому имейте в виду эти возможности. В зависимости от происходящего планировщик может делать неожиданные вещи, которые аннулируют вашу оптимизацию. Я стараюсь просто избегать соединения перед агрегацией, когда она является проблемой, и выполняю соединение уже агрегированных результатов.
Если вы можете переписать запрос так, чтобы большая таблица агрегировалась первой, вы можете использовать сортированный индекс. Это можно выполнить, если соединяемая с ней таблица мала и используется только для дополнительной информации, например, фильтрации результатов. Быстрей получается сначала агрегировать по ключу соединения, соединять с этой небольшой таблицей, и использовать эту информацию для фильтрации и новой агрегации. Хотя иногда это может оказаться более медленным, и вы не узнаете этого, пока не попробуете.
Соединение больших таблиц
Вы пишете простой запрос, все что он делает - это соединение нескольких таблиц. У вас небольшая таблица, которая отфильтровывается до 20 строк. Вы соединяете ее с дочерней таблицей, которая содержит пару миллионов строк. Теперь это медленно.
Есть несколько трюков, которые позволяют изменить ситуацию и заставить планировщик PostgreSQL принимать более правильные решения. Все сводится к тому, что вы хотите сделать с данными в конечном итоге. Если вы соединяете маленькую таблицу с большой для того, чтобы агрегировать или отфильтровать результаты, то вы можете изменить запрос, чтобы получить выигрыш в скорости.
Если вы сначала агрегируете таблицу, то можете радикально уменьшить число строк, которые вы затем соединяете.
Диаграмма двух различных процессов. Первый соединяет большое и малое число строк, что приводит к большому числу данных, которые требуется агрегировать. Во втором большие данные агрегируются до соединения с малым числом строк. Во втором процессе в целом обрабатывается меньше данных.
Рассмотрите эти два процесса. В первом вы видите, что мы должны сравнить малую таблицу со всей большой таблицей, а затем агрегировать. Во втором сначала выполняется агрегация, а затем выполняется соединение двух небольших наборов данных.
Наши проблемы здесь - соединение и сортировка. Агрегация единственной таблицы является быстрой, поскольку может использоваться сортированный индекс. Соединение миллиона строк является медленным, а сортировка после соединения миллиона строк даже еще медленней.
Здесь следует обратить внимание на одну вещь; планировщик может попытаться быть полезным и видит, что вы все еще соединяете малые и большие таблицы посредством агрегации. PostgreSQL может все же спланировать соединение перед группировкой. В этом случае вы можете применить искусное предложение CTE "with" и материализовать результат агрегации.
WITH large_table_grouped as MATERIALIZED(
...
)
SELECT Это может помочь, но иногда становится только хуже. Результат материализации этих строк означает, что вы теряете все индексирование на сгруппированным СТЕ, и доступными для соединения окажутся лишь несколько вариантов. Однако в крайнем случае стоит попробовать.
Другие подходы
Если вы соединяете к таблицу, не ожидая агрегации, и вам нужно много строк данных, то вам придется в значительной степени полагаться на свои индексы.
Иногда планировщик будет медленным тут. Он должен предсказать окончательное число ожидаемых строк для оптимизации методов, которые он использует. Когда он планирует, что соединение будет иметь таблицу с небольшим числом строк, он не учитывает размер большой таблицы и использует неоптимальные подходы.
Например, если планировщик ожидает небольшое число строк, он будет использовать методы, оптимизированные для единственной страницы данных. Если данные разрастаются на множество страниц или даже достигает предела памяти, его можно ускорить, предоставив планировщику более точную оценку.
Проверьте, не делаете ли вы массовую фильтрацию после получения строк из индекса. Планировщик не знает, сколько строк будет отфильтровано после получения строк из индекса. Если этот случай имеет место, вы можете попробовать добавить некоторую фильтрацию непосредственно в индекс.
Иногда левое соединение большой таблицы может сказать PostgreSQL, что вы ожидаете, что вся таблица останется.
Последние замечания
Целью ускорения запросов в PostgreSQL является насколько это возможно сокращение числа строк, с которыми выполняется работа. Чем с меньшим числом строк вы работаете, тем меньше памяти это занимает и тем более эффективным может быть планировщик. Чем к меньшему числу строк вы можете прийти, тем быстрей становятся соединения и сортировка.
Все, что вы пытаетесь сделать, чтобы ускорить планировщика, сводится к сокращению размера данных как можно раньше в выполняемом процессе.
Если вы хотите исследовать более конкретные примеры использования, перейдите к части 2
Ссылки по теме
1. Понимание хеш соединений в MySQL 8
2. Индексы PostgreSQL: что это такое и как они могут помочь
3. Что такое план выполнения и как его найти в PostgreSQL
4. Обзор соединений в PostgreSQL
5. Фильтрованные индексы
Trackbacks
No Trackbacks
The author does not allow comments to this entry
Comments
Display comments as Linear | Threaded