На что обращать внимание при оптимизации SQL запроса

Что проверяют

Есть ли у вас метод, а не набор примет. Правильный ответ начинается с плана запроса.

Как отвечать

Порядок работы такой.

Сначала измерить. EXPLAIN ANALYZE показывает фактический план, время каждого узла и расхождение между ожидаемым и реальным числом строк. Большое расхождение почти всегда означает устаревшую статистику: помогает ANALYZE, а иногда увеличение default_statistics_target.

На что смотреть в плане:

• Seq Scan по большой таблице там, где выбирается мало строк, значит не хватает индекса или он не применим;
• тип соединения: Nested Loop хорош на малых объёмах и катастрофичен, если оценка занизила число строк; Hash Join и Merge Join для больших;
• Rows Removed by Filter: сколько строк прочитали зря;
• сортировка на диске (external merge): не хватает work_mem;
• число проходов по внутренней таблице в цикле.

Что чаще всего чинит запрос:

• индекс по колонкам условия и соединения, для запросов по диапазону составной индекс в правильном порядке колонок;
• покрывающий индекс (INCLUDE) ради чтения только по индексу;
• частичный индекс с WHERE, если выбирается небольшая доля таблицы;
• убрать функцию с колонки: WHERE date(created_at) = ... убивает индекс, нужен диапазон или индекс по выражению;
• убрать SELECT * и лишние колонки;
• заменить коррелированный подзапрос на соединение или переписать NOT IN (который ещё и ломается на NULL) на NOT EXISTS;
• заменить OFFSET на пагинацию по ключу: большой OFFSET читает и выбрасывает всё до него;
• вынести повторяющееся в WITH ... AS MATERIALIZED, если иначе подзапрос считается многократно.

Что смотреть за пределами запроса: число обращений (проблема N+1), блокировки, раздувание таблицы, объём возвращаемых данных.

Где спотыкаются

Начинают с добавления индексов, не посмотрев план.

Вешают индекс на каждую колонку, замедляя запись.

Не знают, что порядок колонок в составном индексе важен.

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