Чтобы оптимизировать запрос, сначала нужно понять, как база данных его выполняет. PostgreSQL использует сложный планировщик для выбора наиболее эффективного пути получения данных. К счастью, у нас есть способ заглянуть в его «мысли» — команда EXPLAIN.

EXPLAIN не выполняет запрос. Вместо этого она показывает план выполнения, который построил бы планировщик. Это как посмотреть на карту маршрута, прежде чем отправиться в путь. Вы видите шаги, но не тратите время на саму поездку.

Этот план показывает оценки планировщика. Но оценки могут быть неточными, например, если статистика по таблице устарела. Чтобы увидеть, что происходит на самом деле, мы добавляем ключевое слово ANALYZE.

EXPLAIN ANALYZE выполняет запрос и показывает план вместе с реальными данными о времени и количестве строк. Это уже не просто карта, а отчет GPS-трекера после поездки, с точным временем на каждом отрезке пути.

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

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

В каждом узле есть ключевые метрики:

Сравнивая оценочные (cost, rows) и фактические (actual time, rows (actual)) значения, вы можете понять, где планировщик ошибся. Большое расхождение — это сигнал, что статистика по таблице устарела, и нужно запустить команду ANALYZE table_name;.

В конце вывода вы также увидите Planning Time (время на построение плана) и Execution Time (общее время выполнения запроса). Если время планирования велико, это может указывать на слишком сложный запрос.

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

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

Иногда вы можете увидеть Index Only Scan. Это еще более быстрый вариант, когда все необходимые для запроса данные находятся прямо в индексе, и базе данных даже не нужно обращаться к основной таблице.

Bitmap Index Scan — это гибридный подход. Сначала PostgreSQL использует индекс для создания в памяти битовой карты страниц, содержащих нужные данные. Затем он последовательно читает только эти страницы из таблицы. Это эффективно, когда нужно выбрать умеренное количество данных, которые разбросаны по таблице.

Когда запрос включает JOIN, планировщик должен выбрать, как соединять данные из нескольких таблиц. От этого выбора сильно зависит производительность.

• Nested Loop Join (Вложенные циклы): Самый простой метод. Для каждой строки из первой (внешней) таблицы PostgreSQL ищет совпадения во второй (внутренней) таблице. Эффективен, если внешняя таблица мала, а на поле соединения во внутренней таблице есть индекс.

• Hash Join (Хеш-соединение): Планировщик создает в памяти хеш-таблицу по меньшей из двух таблиц. Затем он сканирует вторую таблицу и для каждой строки проверяет наличие совпадения в хеш-таблице. Отлично работает для соединения больших таблиц, когда нет подходящих индексов.

• Merge Join (Соединение слиянием): Обе таблицы должны быть отсортированы по ключу соединения. Затем PostgreSQL одновременно читает обе таблицы и «сливает» их. Это очень быстро, если данные уже отсортированы (например, взяты из индекса).

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

Теперь, когда вы знаете, как PostgreSQL думает, вы готовы находить и устранять узкие места в производительности.