Какие методы оптимизации SQL запросов кроме индексов существуют
Что проверяют
Есть ли у вас арсенал шире одного приёма. Индексы называют все, интересно, что дальше.
Как отвечать
Переписать сам запрос. Убрать лишние соединения и подзапросы, заменить коррелированный подзапрос соединением, вынести условие ближе к источнику, отказаться от SELECT * в пользу нужных колонок, заменить DISTINCT группировкой там, где дубли появляются из-за соединения.
Сократить объём. Пагинация по ключу вместо OFFSET, ограничение периода, агрегация на стороне базы вместо вычитывания строк в приложение.
Убрать лишние обращения. Проблема N+1 решается пакетной выборкой, а не ускорением одного запроса. Пакетная вставка вместо тысячи одиночных.
Предподсчёт. Материализованное представление, витрина по расписанию, денормализованный счётчик. Отчёт перестаёт считаться в момент открытия.
Помочь планировщику. Свежая статистика (ANALYZE), корректные типы в сравнениях, отказ от функции над индексируемой колонкой, разумный work_mem для сортировок и хешей.
Инфраструктура. Кэш в приложении, реплики для чтения, секционирование больших таблиц по дате, архивация старых данных.
Транзакции. Короткие транзакции, чтобы не держать блокировки; правильный уровень изоляции; пакетные обновления вместо одного огромного, которое блокирует таблицу.
Порядок всегда один: сначала измерить (EXPLAIN ANALYZE, pg_stat_statements), потом менять.
Где спотыкаются
Начинают с настроек сервера вместо запроса.
Не различают «медленный запрос» и «много запросов».
Считают денормализацию всегда злом.
Оптимизируют на тестовых данных, где любой план быстрый.