Начало работ и детали запроса

Оптимизация SQL-запросов PostgreSQL : большое количество LEFT OUTER JOIN (пример)

Оптимизация запросов с большим количеством LEFT OUTER JOIN в PostgreSQL — это комплексная задача, которая требует подхода как к написанию запроса, так и к настройке самой СУБД. Ключевые направления — это помощь планировщику запросов, правильное индексирование и рассмотрение альтернативных архитектурных решений.

1.Управление порядком JOIN

Ключевая идея

Уменьшить количество вариантов плана для анализа планировщиком

Пример действий

Использовать явный синтаксис JOIN и настроить параметр join_collapse_limit.

2.Оптимизация структур данных

Ключевая идея

Снизить нагрузку на операции сравнения и хеширования.

Пример действий

Использовать более компактные типы данных (например, INT вместо TEXT для ID).

3.Эффективное индексирование

Ключевая идея

Обеспечить быстрое нахождение строк для соединения

Пример действий

Создать индексы на колонках, участвующих в условиях ON для каждого соединения.

4.Архитектурные изменения

Ключевая идея

Полностью избежать затрат на соединение во время выполнения запроса

Пример действий

Рассмотреть денормализацию таблиц или использование материализованных представлений.

💡 Практические шаги по оптимизации

1.Помочь планировщику запросов

Планировщик PostgreSQL при большом количестве JOINов сталкивается с экспоненциальным ростом числа возможных планов выполнения. Чтобы сократить время планирования, можно использовать явный синтаксис JOIN (например, a LEFT JOIN b ON ... LEFT JOIN c ON ...), который задает более предсказуемый порядок. Затем можно установить параметр join_collapse_limit = 1, чтобы планировщик следовал этому порядку. Это особенно актуально, если само построение плана запроса занимает значительное время (секунды).

2. Анализ и упрощение данные

Если в условиях JOIN используются текстовые поля большой длины, операции хеширования и сравнения могут стать "узким местом". По возможности стоит использовать более подходящие и компактные типы данных (например, целые числа для идентификаторов). Также необходимо убедиться, что типы данных связываемых колонок совпадают, чтобы избежать неявного преобразования типов.

3.Правильные индексы

Это основа основ. Для каждого условия ON в ваших LEFT JOIN должен существовать индекс. Как минимум, индексируются колонки из правой таблицы. Например, для ... LEFT JOIN table_b ON table_a.id = table_b.a_id ... полезно иметь индекс на table_b.a_id.

4.Альтернативные подходы

Если запрос выполняется редко, но требует много ресурсов, эффективным решением может быть материализованное представление (Materialized View), которое хранит результат запроса на диске и периодически обновляется.

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

В сложных случаях можно разбить один тяжелый запрос на несколько более простых и обработать логику на стороне приложения.

Задача

Подготовить и протестировать на продуктивной СУБД, типовой шаблон действий при анализе причин инцидента производительности СУБД.

Шаблон

1. Стартовое событие - инцидент снижения производительности СУБД.

2. Корреляция типов ожиданий - определение типа ожидания с наибольшей корреляцией.

3. Диаграмма Парето по SQL запросам по данному типу ожидания - набор SQL запросов для последующей оптимизации.

1. Стартовое событие - инцидент снижения производительности СУБД

Zabbix

Результат - Стартовое событие для начала процессе Incident Management

2. Корреляция типов ожиданий - определение типа ожидания с наибольшей корреляцией

Источник:

Отчёт по статистике СУБД : make_summary_with_sql.sh

Результат - тип ожидания с наибольшим коэффициентом корреляции

Тип ожидания IPC имеет наибольший коэффициент корреляции между типом ожидания и ожиданиями СУБД.

3. Диаграмма Парето по SQL запросам по данному типу ожидания

Источник:

Отчёт по статистике СУБД : make_summary_with_sql.sh

Результат - SQL запросы имеющие 80% ожиданий типа IPC

• 6863414396188999698

• -5395258115281111645

• -4533756551948631336

• -4460774138492313959

Продолжение

Оптимизация SQL запроса -5395258115281111645

• Оптимизация SQL-запросов PostgreSQL : индекс по UUID

Оптимизация SQL запроса -4460774138492313959

• Оптимизация SQL-запросов PostgreSQL : COUNT(*)

Задача

1. Подготовка общей методологии по выявлению проблемных SQL-запросов влияющих на производительности СУБД и имеющих перспективы для оптимизации.

2. Формирование набора рекомендаций по оптимизации типовых SQL-запросов.

1. Выявление SQL-запросов для оптимизации

Детали и подробности: PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

Операционная скорость и ожидания СУБД

Диаграмма Парето по типу ожидания IPC

80% событий ожиданий :

• BgWorkerShutdown: Ожидание завершения фонового рабочего процесса.

• ExecuteGather: Ожидание активности дочернего процесса при выполнении узла плана Gather.

• ParallelFinish: Ожидание завершения вычислений параллельными рабочими процессами.

Результат анализа ожиданий на уровне SQL-запросов:

Наибольшее количество ожиданий IPC по запросу 6863414396188999698

Оптимизация SQL-запроса

Измененный текст запроса

Детали и подробности: PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

Операционная скорость и ожидания СУБД

Ожидания типа IPC по SQL-запросам

Наибольшее количество ожиданий типа IPC по SQL c queryid = -5849488707035427374

Переписать запрос с использованием CTE

Детали и подробности: PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

Операционная скорость и ожидания СУБД

Ожидания типа IPC по SQL-запросам

Следующий SQL-запрос для оптимизации : -1701015661318396920

Оптимизация структуры запроса

Основная проблема - "декартово произведение" из-за нескольких LEFT JOIN.

2.Формирование набора рекомендаций по оптимизации

Детали и подробности: Оптимизация SQL-запросов PostgreSQL : LIMIT 1

Создание подходящих индексов

Создать индекс по колонкам из WHERE и ORDER BY. Позволяет найти строку за несколько шагов.

Эффект

Кардинальное ускорение (в тысячи раз), устранение полного сканирования таблицы.

Анализ плана выполнения

Использовать EXPLAIN (ANALYZE, BUFFERS) для просмотра плана запроса. Показывает, используется ли индекс.

Эффект

Точно определяет "узкое место" в запросе, позволяет оценить стоимость операций.

Использование ORDER BY с индексом

Всегда использовать ORDER BY с LIMIT для предсказуемого результата. Упорядочивание должно совпадать с индексом.

Эффект

Гарантирует корректность результата и позволяет использовать индекс для сортировки.

Устранение лишних операций

Убрать ненужные DISTINCT, сложные вычисления в WHERE, избыточные JOIN. Снижает объем работы до применения LIMIT.

Эффект

Сокращает общее время выполнения, особенно если лишняя операция требовала сортировки.

Увеличение work_mem

Увеличить параметр work_mem, если в плане есть Sort с дисковыми операциями (Disk: writes temp).

Эффект

Ускоряет операции сортировки и хеширования, выполняемые в памяти.

Детали и подробности: Оптимизация SQL-запросов PostgreSQL : IN с большим количеством значений

Наиболее эффективные подходы заключаются в замене IN на более оптимальные конструкции и использовании специальных техник работы с данными.

ANY(ARRAY[]) вместо IN

Замена IN (...) на = ANY(ARRAY[...]).

Оператор ANY может остановить проверку при первом совпадении.

Когда метод эффективен

Когда список значений очень большой.

JOIN с виртуальной таблицей

Преобразование списка значений в виртуальную таблицу с помощью VALUES и соединение с основной таблицей.

Когда метод эффективен

Когда список значений можно представить как набор строк.

EXISTS вместо IN для подзапросов

Проверка существования записи с помощью EXISTS.

Запрос прекращает работу, как только найдет первое совпадение.

Когда метод эффективен

Особенно эффективен для коррелированных подзапросов и сценариев с NOT IN (лучше использовать NOT EXISTS)

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

Предварительный расчет и сохранение результатов "тяжелого" запроса, например, с DISTINCT.

Когда метод эффективен

Когда данные изменяются редко, а актуальность в реальном времени не критична.

Нормализация схемы данных

Вынесение часто запрашиваемых полей (например, make, vehicle_year) в отдельные справочные таблицы.

Когда метод эффективен

Когда одни и те же значения (DISTINCT ...) выбираются из таблицы миллионы раз .

Наиболее эффективные подходы заключаются в замене IN на более оптимальные конструкции и использовании специальных техник работы с данными.

ANY(ARRAY[]) вместо IN

Замена IN (...) на = ANY(ARRAY[...]).

Оператор ANY может остановить проверку при первом совпадении.

Когда метод эффективен

Когда список значений очень большой.

JOIN с виртуальной таблицей

Преобразование списка значений в виртуальную таблицу с помощью VALUES и соединение с основной таблицей.

Когда метод эффективен

Когда список значений можно представить как набор строк.

EXISTS вместо IN для подзапросов

Проверка существования записи с помощью EXISTS.

Запрос прекращает работу, как только найдет первое совпадение.

Когда метод эффективен

Особенно эффективен для коррелированных подзапросов и сценариев с NOT IN (лучше использовать NOT EXISTS)

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

Предварительный расчет и сохранение результатов "тяжелого" запроса, например, с DISTINCT.

Когда метод эффективен

Когда данные изменяются редко, а актуальность в реальном времени не критична.

Нормализация схемы данных

Вынесение часто запрашиваемых полей (например, make, vehicle_year) в отдельные справочные таблицы.

Когда метод эффективен

Когда одни и те же значения (DISTINCT ...) выбираются из таблицы миллионы раз .

💡 Рекомендации по применению методов

Помимо замены оператора, важно рассмотреть более фундаментальные оптимизации.

• Правильные индексы: Необходимо убедится, что на столбцах, которые участвуют в условиях IN, ANY или JOIN, созданы индексы (чаще всего BTREE). Без индекса даже переписанный запрос будет выполняться медленно .

• Анализ плана запроса.

• Нормализация данных: Если часто делается запросы вида SELECT DISTINCT column_name к очень большой таблице, это может указывать на недостатки в структуре базы данных. Вынесение возможных значений атрибута (например, марок автомобилей) в отдельную справочную таблицу — кардинальное, но очень эффективное решение .

⚠️ Чего следует избегать

• NOT IN с подзапросами: Для сценариев исключения (NOT IN) PostgreSQL может строить неоптимальные планы с подзапросами (SubPlan). Вместо NOT IN практически всегда лучше использовать NOT EXISTS или LEFT JOIN ... WHERE ... IS NULL, которые приводят к более эффективному плану с Hash Anti Join .

• DISTINCT без необходимости: Если нужно получить только уникальные значения, а не все строки, использование DISTINCT может создать дополнительную нагрузку. Иногда эту задачу можно решить на уровне приложения или с помощью других методов SQL .

Оптимизация запросов с LIMIT 1 в PostgreSQL фокусируется на том, чтобы СУБД нашла нужную строку максимально быстро, не перебирая все данные.

Ключевые моменты:

1. правильные индексы

2. анализ плана выполнения

3. избегание лишних операций.

Создание подходящих индексов

Создать индекс по колонкам из WHERE и ORDER BY. Позволяет найти строку за несколько шагов.

Эффект

Кардинальное ускорение (в тысячи раз), устранение полного сканирования таблицы.

Анализ плана выполнения

Использовать EXPLAIN (ANALYZE, BUFFERS) для просмотра плана запроса. Показывает, используется ли индекс.

Эффект

Точно определяет "узкое место" в запросе, позволяет оценить стоимость операций.

Использование ORDER BY с индексом

Всегда использовать ORDER BY с LIMIT для предсказуемого результата. Упорядочивание должно совпадать с индексом.

Эффект

Гарантирует корректность результата и позволяет использовать индекс для сортировки.

Устранение лишних операций

Убрать ненужные DISTINCT, сложные вычисления в WHERE, избыточные JOIN. Снижает объем работы до применения LIMIT.

Эффект

Сокращает общее время выполнения, особенно если лишняя операция требовала сортировки.

Увеличение work_mem

Увеличить параметр work_mem, если в плане есть Sort с дисковыми операциями (Disk: writes temp).

Эффект

Ускоряет операции сортировки и хеширования, выполняемые в памяти.

💡 Рекомендации по созданию индексов

Для LIMIT 1 особенно важны индексы, которые позволяют сразу найти единственную запись.

Типичные сценарии:

• Простой поиск: Для запроса SELECT * FROM users WHERE email = 'test@example.com' LIMIT 1; индекс по колонке email: CREATE INDEX CONCURRENTLY idx_users_email ON users(email);.

• Поиск с сортировкой: Для запроса SELECT * FROM logs WHERE user_id = 123 ORDER BY created_at DESC LIMIT 1; (последняя запись пользователя) оптимален составной индекс: CREATE INDEX CONCURRENTLY idx_logs_user_id_created_at ON logs(user_id, created_at DESC);. Так PostgreSQL быстро найдет все записи пользователя в обратном порядке и возьмет первую.

🔍 Анализ план запроса

После создания индекса используйте EXPLAIN (ANALYZE, BUFFERS) для проверки , необходимо убедиться, что в плане появился узел Index Scan или Index Only Scan, а не Seq Scan (последовательное сканирование таблицы). Обратить внимание на стоимость операции и количество прочитанных данных.

🚫 Частые ошибки

• LIMIT без ORDER BY: Без явного указания порядка PostgreSQL возвращает строки в удобном для него порядке, который может быть непредсказуемым. Всегда нужно использовать ORDER BY с LIMIT для гарантированного результата.

• Слишком сложные условия в WHERE: Вычисления наподобие WHERE extract(month from created_at) = 12 часто не позволяют использовать индекс. Переписать условие так, чтобы колонка была "чистой": WHERE created_at >= '2025-12-01' AND created_at < '2026-01-01'.

• OFFSET с большими значениями: LIMIT 1 OFFSET 1000000 заставляет СУБД перебрать и отбросить все миллион строк. Для постраничной навигации использовать условие WHERE id > last_seen_id ORDER BY id LIMIT n.

P.S.

Настройка work_mem может помочь, если в плане выполнения видна сортировка на диске, но начинать всегда стоит с проектирования индексов и анализа запросов.

Аналогичная задача

PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

Продолжение анализа производительности для той же СУБД .

Инцидент производительности СУБД

Операционная скорость и ожидания СУБД

Корреляция и ожидания СУБД

Ожидания типа IPC

80% ожиданий:

• BgWorkerShutdown

• ParallelFinish

• ExecuteGather

Ожидания типа IPC по SQL-запросам

Следующий SQL-запрос для оптимизации : -1701015661318396920

Текст запроса содержит параметр, поэтому для анализа возможных вариантов оптимизации придется воспользоваться планом выполнения запроса, полученному из отчетов pgpro_pwr:

• pgpro_pwr_queryid: e864c744b5bda408

• plan_id: b8b72054c691886a

План выполнения запроса

Оптимизация SQL-запроса

1. Оптимизация структуры запроса

Основная проблема - "декартово произведение" из-за нескольких LEFT JOIN.

2. Добавление недостающих индексов

3. Оптимизация текущего запроса

4. Настройка PostgreSQL

Возможный план оптимизации

1. Добавление индекса для plans_table4 - это самое слабое место в текущем плане

2. Разделить запрос на несколько - это даст наибольший прирост производительности

3. Использование агрегацию через JSON, если нужны все данные в одной строке

4. Настройка параметров СУБД