Возможные ошибки оптимизации
- Коррелированные подзапросы, которые планировщик не декоррелирует — выполняются для каждой строки внешней таблицы (медленно).
- Использование CTE/WITH как «барьера оптимизации» (в старых версиях многих СУБД CTE материализуется и не подлежит дальнейшей оптимизации).
- Непосредственные функции/выражения в условиях (e.g. `WHERE f(col)=...`, `LIKE '%x'`) — делают предикаты несопоставимыми с индексом (non‑sargable).
- ORDER BY/ROW_NUMBER/окна над большими партициями без подходящего порядка на диске — приводят к большим сортировкам/материализации.
- DISTINCT/UNION/Nested aggregate в подзапросе мешают распараллеливанию и push‑down агрегаций.
- Неправильная статистика (не running ANALYZE) — планировщик выбирает плохие join‑методы (nested loop вместо hash/merge или наоборот).
- Использование NOT IN//IS NULL сложнее для оптимизатора и может привести к полным сканам.
Как планировщик СУБД может преобразовать запрос (типичные трансформации)
- Декорреляция подзапроса: скалярный/коррелированный подзапрос преобразуется в LEFT/INNER JOIN или в LATERAL, если возможно.
- EXISTS/IN → semi‑join (полу‑соединение) вместо материализации подзапроса.
- Push‑down предикатов: перемещение фильтров в подзапрос/проекции для уменьшения объёма данных.
- Редукция/сведение агрегаций: агрегация может быть перенесена ближе к источнику данных (aggregate pushdown), если не нарушается семантика оконных функций.
- Join reordering и выбор метода join: nested loop ↔ hash join ↔ merge join в зависимости от оценок и доступных индексов.
- Использование индекса для поддержания порядка (avoid sort): если индекс покрывает ORDER BY/partition BY, возможен merge/ index scan вместо внешней сортировки.
- Планировщик может материализовать промежуточный результат (temp) для оконных функций или, при возможности, выполнить streaming‑window, избежав materialize.
Когда материализация/сортировка вероятна
- Оконные функции с ORDER BY часто требуют сортировки партиции: стоимость сортировки $O(n\log n)$.
- Если партиция большая и нет подходящего индекса, ожидайте внешнего сортирования и временных файлов.
- LIMIT с ORDER BY может использовать top‑N алгоритм (heap) с затратой $O(n)$ по памяти/времени, но если индекс уже упорядочивает данные — сорт можно избежать.
Индексы и другие структуры, которые помогут
- B‑tree на колонках соединения: для join a.id = b.a_id индекс на `b(a_id)` (или двусторонние индексы) — ускоряет index‑nested‑loop и индексные сканы.
- Составные индексы для WHERE + ORDER BY: если запрос делает `WHERE x = ? AND y > ? ORDER BY y DESC`, индекс `(x, y DESC)` позволит избежать сортировки и даст селективный поиск.
- Covering / INCLUDE‑индексы (Postgres: `CREATE INDEX ... ON t(col) INCLUDE(other_col)`) — обеспечивают index‑only scan и уменьшают чтение таблицы.
- Функциональные/выраженные индексы для несаргабельных выражений: `CREATE INDEX ON t ((lower(name)))` для `WHERE lower(name) = '...'`.
- Частичные индексы для часто встречающихся селективных фильтров: `CREATE INDEX ON t(col) WHERE status = 'active'`.
- BRIN для больших append‑only таблиц по времени/последовательным id — дешёвый индекс для диапазонных сканов.
- GIN/GiST для полнотекстового поиска, массивов, гео‑типов.
- Индекс, поддерживающий порядок по partition+order (для окон): индекс по `(partition_col, order_col)` может позволить выполнить оконную функцию без сортировки по партициям.
- Для semi‑joins/EXISTS — индекс на проверяемом столбце в подзапросе (чтобы EXISTS быстро находил совпадение).
- При OR в WHERE: либо отдельные индексы на каждую часть + optimizer умеет их комбинировать, либо рефакторинг через UNION ALL, чтобы использовать индексы.
Практические рекомендации
- Выполните EXPLAIN (ANALYZE, BUFFERS, VERBOSE) — смотрите, материализуются ли подзапросы, какие join‑методы и есть ли большие сортировки.
- Преобразуйте коррелированные подзапросы в joins/LATERAL или агрегируйте отдельно и join‑те по результату.
- Обеспечьте актуальные статистики: `ANALYZE`.
- Подберите индексы, которые одновременно покрывают WHERE и ORDER BY (левый префикс для составного индекса).
- Увеличьте `work_mem` для уменьшения внешних сортировок/свопов; включите/настройте параллелизм.
- Если CTE используется как временная таблица, в Postgres проверьте `MATERIALIZED/NOT MATERIALIZED` опцию; в других СУБД — избегайте ненужной материализации.
Короткая оценка затрат (приблизительно)
- Полный сиквенциальный скан: примерно $O(n)$.
- Сортировка: примерно $O(n\log n)$.
- Index lookup (B‑tree): примерно $O(\log n)$ плюс чтение строк.
- Hash join: зависимость от размеров входов и размера хеша, часто ближе к $O(n)$ при достаточной памяти.
Если пришлёте конкретный фрагмент запроса и схему (ключи, кардинальности), дам точные трансформации и конкретные индексы.