🚀🐘 Оптимизация хранимых процедур в PostgreSQL: 4 трюка для взрывного ускорения

Привет всем!

Я, Ипатов Александр, backend-разработчик в компании USETECH. Хотел бы поделиться с вами принципами оптимизации хранимых процедур на PostreSQL.

Введение

Оптимизация хранимых процедур — это важный этап в разработке и поддержке баз данных. Она позволяет улучшить производительность системы, сократить время выполнения запросов и снизить нагрузку на сервер. В этой статье я рассмотрю принципы оптимизации хранимых процедур и методы, которые помогут вам достичь поставленных целей.

1. Анализ запросов

Перед тем как приступить к оптимизации, необходимо провести анализ существующих запросов. Это позволит выявить узкие места и определить, какие изменения могут быть наиболее эффективными. Для анализа запросов можно использовать следующие инструменты:

• Explain analyze — позволяет увидеть план выполнения запроса и определить, какие операции занимают больше всего времени.
• Profiler — позволяет отслеживать время выполнения отдельных операций в запросе.
• Метки внутри процедуры – с помощью RAISENOTICE и переменных текущего времени можно в ручном режиме определить проблемный участок в процедуре, на котором идет долгое выполнение.

2. Индексы

Из теории следует, что индексы — это один из наиболее эффективных способов оптимизации запросов. Это верно лишь в том случае, если у вас действительное пустые таблицы и никаких индексов не реализовано. Тогда они позволяют ускорить доступ к данным и сократить время выполнения запросов. При создании индексов необходимо учитывать следующие факторы:

• Селективность — чем выше селективность индекса, тем эффективнее он будет работать.
• Размер — чем меньше размер индекса, тем быстрее он будет построен и тем меньше места он будет занимать на диске.
• Уникальность — уникальные индексы позволяют ускорить выполнение запросов, которые используют условия равенства.

Но это не значит, что нужно для любого запроса дополнять индексами таблицы. На своем опыте хочу отметить, что создание недостающего индекса – это из разряда «один на миллион». В обычных сценариях индексы уже существуют, даже в избытке.

3. Материализованные представления

Материализованные представления — это еще один способ ускорить выполнение запросов. Они представляют собой предварительно вычисленные результаты запросов, которые хранятся в базе данных. Это позволяет сократить время выполнения запросов, которые используют материализованные представления. Но есть и негативный момент – его обновление. Само по себе это действие очень затратно, а чтобы данные в результате выполнения процедуры были реально актуальными – нужно осуществлять REFRESH этого представления прямо внутри хранимой процедуры. И это не оптимально и практически неэффективно. Альтернатива – переписать это представление на подзапрос или CTE.

4. Оптимизация непосредственно SQL-запросов

Оптимизация SQL-запросов — это процесс улучшения производительности запросов. Она включает в себя следующие этапы:

1. Удаление ненужных операций — удаление операций, которые не влияют на результат запроса, может ускорить его выполнение.
2. Использование JOIN вместо подзапросов — JOIN позволяет объединить две таблицы в одну, что может ускорить выполнение запроса.
3. Использование индексов — как было сказано выше, индексы могут ускорить выполнение запросов.

Но если эти стандартные действия по «оптимизации» уже давно выполнены:

1. Все операции вычищены, аналитики подтверждают это.
2. Все запросы написаны, конечно, через JOIN-ы, не «мамонтами».
3. Индексов уже очень много и стоит большой вопрос по анализу их использования / удалению ненужных / неиспользуемых / используемых мало.

Тогда на помощь придут такие практические действия:

1. Отказ от временных таблиц в пользу CTE и массивов — временные таблицы могут занимать много памяти и замедлять выполнение запросов. CTE и массивы позволяют сократить объем занимаемой памяти и ускорить выполнение запросов.
2. Отказ от MATERIALIZED VIEW в пользу временных таблиц и CTE — как было сказано выше, MATERIALIZED VIEW может занимать много памяти и замедляться при обновлении. CTE позволяет ускорить выполнение запросов.
3. Использование pg_variables для хранения временных данных — pg_variables позволяет разгрузить read-write ноду кластера PostgreSQL и перенести часть отчётных хранимых процедур на read-only ноду. Это позволяет снизить нагрузку на основную ноду кластера и улучшить производительность системы. Статья на тему этого расширения здесь.
4. Замена временных таблиц с одним столбцом на массивы — временные таблицы с одним столбцом могут занимать много памяти и замедлять выполнение запросов. Массивы позволяют ускорить выполнение запросов и снизить нагрузку на систему. Но именно этот пункт стоит под вопросом, т.к. в зависимости от каждого SQL-запроса стоимость операций = ANY (массив)» и «IN (SELECT…..) может колебаться в разные стороны.
5. Использование индексов для объёмных временных таблиц — объёмные временные таблицы могут замедлять выполнение запросов. Индексы, которые можно наложить на такие временные таблицы, практически всегда позволяют ускорить выполнение запросов.
6. Обязательно в хранимой процедуре после DML-операций (insert / update / delete) над временными таблицами выполняем команду «analyze». Эта команда позволяет пересчитать статистику данной временной таблицы, которая позволит оптимизатору построить оптимальный план запроса, так как он будет знать статистические характеристики этой временной таблицы и не пойдет по неверному пути.
7. Переписывание неоптимальных SQL-запросов — как было сказано выше, неоптимальные SQL-запросы могут замедлять выполнение запросов. Переписывание запросов «с чистого листа» может ускорить их выполнение, но это колоссальная нагрузка на разработку и аналитику, в силу больших размеров кода и сложной логики, которая была заложена и могла дорабатываться годами.

Из частного случая хочу отметить варианты а-ля:

        LEFT LATERAL JOIN (SELECT…FROM…ORDER BY….. LIMIT 1) as left_lateral
    

Здесь джойнится построчно запрос с большим (или не очень) количеством строк, в результате из которого берется лишь первая строка. Это точно может выполняться долго. Нужно рассмотреть вариант ухода в сторону каскада из двух CTE с использованием ранжирующей оконной функции и определением лишь одной строки по рангу по указанному нами окну.

Заключение

Оптимизация хранимых процедур — это важный этап в разработке и поддержке баз данных. Она позволяет улучшить производительность системы, сократить время выполнения запросов и снизить нагрузку на сервер. Следуя принципам, указанным в статье, вы сможете оптимизировать хранимые процедуры и улучшить производительность вашей базы данных.

А какие ваши любимые приемы оптимизации PostgreSQL? Поделитесь в комментариях!

Статьи по теме

• 🚀⬆️ Оптимизируй свой AI: разоблачение 6 мифов о работе с векторами в Pgvector
• 🚀 Ускорьте свою базу данных: 7 проверенных методов масштабирования и оптимизации
• 🐘🧠 Оптимизация использования памяти в PostgreSQL: секреты профессионалов