Как оптимизировать SQL запросы

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

Есть ли у вас порядок действий или вы навешиваете индексы наугад. Первый шаг всегда одинаковый: посмотреть план.

Как отвечать

Начните с метода: найти медленные запросы (pg_stat_statements, лог долгих запросов, метрики приложения), посмотреть план через EXPLAIN (ANALYZE, BUFFERS), изменить, замерить снова.

Дальше типовые приёмы:

• индекс под запрос: составной в правильном порядке (равенство первым, диапазон последним), частичный по условию, покрывающий с дополнительными колонками. Индекс не «на всякий случай», а под конкретную форму запроса;
• не мешать индексу: функция или приведение типа над колонкой отключает его. where date(created_at) = ... заменяется диапазоном по колонке;
• брать только нужные колонки: select * тянет лишнее, ломает возможность отвечать только по индексу и раздувает трафик;
• пагинация по ключу вместо OFFSET: большой сдвиг заставляет базу прочитать и выбросить все предыдущие строки;
• соединения вместо подзапросов в цикле: проблема N+1 обычно живёт не в SQL, а в коде приложения, и лечится одним запросом или предзагрузкой;
• агрегаты заранее: счётчики и витрины вместо пересчёта на каждый показ;
• актуальная статистика: после массовой загрузки ANALYZE, иначе планировщик ошибается в оценках и выбирает неверный план;
• ограничение объёма: фильтровать до соединения, а не после.

Закончите проверкой: сравнение времени и числа прочитанных страниц до и после на одинаковых данных. Оптимизация без замера это гадание.

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

Создают индексы на все колонки из WHERE и роняют скорость записи.

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

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