Оптимизация запросов: EXPLAIN, планы, JOIN'ы, агрегация

Кратко о теме

Модель в голове должна быть такая: SQL — декларативный язык, вы описываете что хотите получить, а как это выполнить решает планировщик. В PostgreSQL это стоимостной (cost-based) оптимизатор: он перебирает варианты доступа к каждой таблице (Seq Scan, Index Scan, Index Only Scan, Bitmap Scan), варианты соединений (Nested Loop, Hash Join, Merge Join) и порядок соединений, считает для каждого плана абстрактную «стоимость» и выбирает самый дешёвый. Стоимость — не секунды, а условные единицы, где за 1.0 принято чтение одной страницы последовательно (seq_page_cost); остальные константы (random_page_cost = 4.0, cpu_tuple_cost = 0.01, cpu_index_tuple_cost, cpu_operator_cost) настраиваются и на SSD обычно требуют снижения random_page_cost до 1.1–2.0.

Всё это считается по статистике, а не по данным. Статистику собирает ANALYZE (сам по себе или в составе autovacuum) и кладёт в pg_statistic (человекочитаемо — pg_stats): доля NULL, оценка числа уникальных значений n_distinct, список самых частых значений most_common_vals с частотами, гистограмма распределения histogram_bounds, корреляция физического порядка строк с логическим. Плюс pg_class.reltuples и relpages — грубая оценка размера таблицы. Отсюда главный вывод для практики: почти все катастрофические планы — это следствие плохой оценки кардинальности. Планировщик решил, что из соединения выйдет 5 строк, построил Nested Loop, а вышло 5 миллионов — и запрос висит часами. Поэтому в EXPLAIN ANALYZE первое, на что смотрят, — расхождение rows= (оценка) и actual rows= (факт).

Инструмент диагностики — EXPLAIN. Без ANALYZE он только показывает выбранный план и оценки, запрос не выполняется. С ANALYZE запрос реально выполняется, и вы получаете фактическое время каждого узла, число строк, число проходов (loops), а с BUFFERS — сколько страниц прочитано из shared buffers (hit) и с диска/страничного кэша ОС (read). Читать план надо снизу вверх и изнутри наружу: листья дерева — способы доступа к таблицам, выше — соединения, агрегация, сортировка, лимит. Время в узле actual time=X..Y — это накопленное время, включая потомков, и на один проход; чтобы получить полное, умножайте на loops.

Второй пласт темы — сам SQL: порядок логической обработки запроса FROM/JOIN → WHERE → GROUP BY → HAVING → оконные функции → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. Из него автоматически следуют ответы про WHERE vs HAVING, про то, почему нельзя написать оконную функцию в WHERE, почему LEFT JOIN превращается в INNER, если условие по правой таблице ушло в WHERE, и почему ORDER BY может ссылаться на алиасы из SELECT, а WHERE — нет.

Третий пласт — понимание, что запрос может тормозить не из-за плана: N+1 из ORM, блокировки, сетевые round-trip’ы, сериализация огромного результата, холодный кэш, generic plan у prepared statement, JIT, забитый пул соединений. «Идеальный EXPLAIN и тормозящий запрос» — это классический вопрос именно про это.

Вопросы и ответы

Страница со списком сущностей работает медленно. Как будете исследовать и диагностировать проблему?

Коротко. Сначала измерю, где именно теряется время: клиент, сеть, приложение или БД — по трейсу/метрикам эндпоинта. Если БД — найду конкретные запросы (pg_stat_statements, лог медленных запросов, auto_explain), сниму по ним EXPLAIN (ANALYZE, BUFFERS) и буду смотреть на расхождение оценок с фактом, Seq Scan по большим таблицам, сортировку на диске и число вызовов.

Глубже. Для «страницы со списком» есть свой типовой набор диагнозов, и я проверю их по порядку. Первое — N+1: список из 50 сущностей, и на каждую ORM делает отдельный запрос за связями; в pg_stat_statements это виден запрос с крошечным mean_exec_time и гигантским calls. Второе — пагинация через LIMIT ... OFFSET N на больших N: сервер всё равно материализует и выбрасывает N строк. Третье — COUNT(*) для «всего страниц»: он почти всегда дороже самой страницы; лечится приблизительным счётом (reltuples, EXPLAIN-оценка) или отказом от общего числа страниц. Четвёртое — сортировка без индекса, покрывающего ORDER BY вместе с фильтром, из-за чего сервер вынужден отсортировать весь набор, прежде чем отдать 20 строк. Пятое — join с широкими таблицами и SELECT *, вытягивающий TOAST’нутые поля (jsonb, text), которые на странице не нужны. Полезно ещё посмотреть pg_stat_activity на wait_event_type — не стоит ли запрос на блокировке или на IO.

Какие инструменты и методы вы используете для отладки медленных SQL-запросов?

Коротко. EXPLAIN (ANALYZE, BUFFERS, VERBOSE) — основной инструмент, pg_stat_statements — чтобы найти, что чинить, auto_explain — чтобы поймать план медленного запроса на проде, log_min_duration_statement — чтобы просто увидеть медленные запросы в логе, pg_stat_user_tables / pg_stat_user_indexes — про seq-сканы и неиспользуемые индексы, pg_stat_activity и pg_locks — про ожидания и блокировки.

Глубже. Метод такой: сначала находим кандидатов по суммарному вкладу (total_exec_time в pg_stat_statements, а не по «самому медленному одиночному запросу» — часто главный виновник это быстрый запрос, вызванный миллион раз). Дальше воспроизводим запрос с теми же параметрами и снимаем EXPLAIN (ANALYZE, BUFFERS). Для визуализации плана удобны explain.depesz.com, explain.dalibo.com, pev2 — они подсвечивают узлы с худшим расхождением оценок и самым большим эксклюзивным временем. Из системного — perf/pg_wait_sampling/pgsentinel для профиля ожиданий, pgbench для воспроизведения нагрузки, pgbadger для отчёта по логам. Отдельно стоит EXPLAIN (GENERIC_PLAN) (PostgreSQL 16+): позволяет посмотреть план параметризованного запроса с $1, не подставляя значения.

Какие типы JOIN в SQL вы знаете?

Коротко. По стандарту: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, плюс синтаксические варианты NATURAL JOIN и JOIN ... USING. Отдельно стоит LATERAL JOIN (соединение с подзапросом, который видит колонки левой стороны) и логические полусоединения — semi-join (EXISTS, IN) и anti-join (NOT EXISTS).

Глубже. Важно не путать типы соединений (что попадает в результат) с алгоритмами соединений (как это исполняется): Nested Loop, Hash Join, Merge Join — это уже уровень исполнителя, и их выбирает планировщик. NATURAL JOIN в проде лучше не использовать: он соединяет по всем одноимённым колонкам, и добавление колонки created_at в обе таблицы молча меняет семантику запроса. Self-join — не отдельный тип, а обычный join таблицы с собой под другим алиасом.

Какие инструменты вы использовали для оптимизации SQL-запросов?

Коротко. См. выше про инструменты отладки — набор тот же. Отличие в акценте: для оптимизации, кроме диагностики, нужны средства проверки гипотез — CREATE INDEX CONCURRENTLY на реплике или в staging, hypopg для гипотетических индексов без реальной сборки, CREATE STATISTICS для расширенной статистики и pg_hint_plan, если план надо зафиксировать.

Глубже. На собеседовании стоит показать, что вы умеете не только «поставить индекс», но и оценить его цену: каждый индекс замедляет запись, увеличивает объём и WAL, мешает HOT-обновлениям. Поэтому перед созданием я смотрю, нет ли уже подходящего индекса с нужным префиксом, и после — проверяю idx_scan в pg_stat_user_indexes, чтобы удалить те, которые не используются. hypopg полезен именно тем, что даёт увидеть, изменится ли план от индекса, не платя за его построение на большой таблице.

Вью и средства анализа скорости (explain analyze)

Коротко. Обычный VIEW — это сохранённый текст запроса; на этапе rewrite он подставляется в вызывающий запрос, и планировщик планирует всё вместе, так что сама по себе вью ничего не ускоряет и не замедляет. MATERIALIZED VIEW физически хранит результат и ускоряет чтение ценой устаревания данных. EXPLAIN ANALYZE показывает план уже раскрытой вью — по нему и надо оптимизировать.

Глубже. Подводные камни вью: вложенные друг в друга вью (вью поверх вью поверх вью) дают планировщику огромное дерево, и он упирается в join_collapse_limit/from_collapse_limit (по умолчанию 8), после чего перестаёт полноценно перебирать порядок соединений, а при числе элементов ≥ geqo_threshold (12) переключается на генетический оптимизатор GEQO, который выбирает план недетерминированно. Вью с DISTINCT, GROUP BY, оконными функциями или LIMIT становится барьером оптимизации: предикаты снаружи не проталкиваются внутрь, и вы получаете полный расчёт вью ради трёх строк. В EXPLAIN вью не видна как отдельный узел — вы увидите её содержимое «вплавленным» в план, и это нормально.

В чем отличие WHERE и HAVING?

Коротко. WHERE фильтрует строки до группировки и не может содержать агрегатные функции; HAVING фильтрует уже сформированные группы после GROUP BY и агрегатов и может ссылаться на SUM(), COUNT() и т.п.

Глубже. Порядок логической обработки: FROM → WHERE → GROUP BY → агрегаты → HAVING. Отсюда правило производительности: всё, что можно отфильтровать в WHERE, надо фильтровать в WHERE — тогда до агрегации доедет меньше строк и, что важнее, фильтр сможет использовать индекс. HAVING без GROUP BY тоже валиден: вся выборка считается одной группой, и SELECT count(*) FROM t HAVING count(*) > 100 вернёт либо одну строку, либо ноль. И HAVING без агрегатов формально разрешён, но это почти всегда ошибка — такое условие надо переносить в WHERE.

-- правильно: дешёвый фильтр в WHERE, агрегатный — в HAVING
SELECT user_id, sum(amount) AS total
FROM purchases
WHERE created_at >= date '2026-01-01'
GROUP BY user_id
HAVING sum(amount) > 100;

WHERE vs HAVING?

Коротко. См. выше: WHERE — по строкам до группировки, HAVING — по группам после агрегации.

Глубже. Отличие формулировки в том, что здесь часто ждут короткого «и что быстрее». Ответ: WHERE дешевле, потому что срезает строки до GROUP BY и может опираться на индекс, а HAVING работает уже по результату агрегации и индексом не ускоряется. Оптимизатор PostgreSQL умеет самостоятельно перенести из HAVING в WHERE условия, не содержащие агрегатов, но полагаться на это не стоит.

Как можно оптимизировать запрос медленный запрос в Elastic?

Коротко. В Elasticsearch я начинаю с Profile API ("profile": true) и slow log, чтобы понять, где время: query, fetch или aggregation. Дальше типовые рычаги — перенести всё точное в filter context (кэшируется и не считает _score), убрать ведущие wildcard’ы и скрипты, ограничить _source, отказаться от deep pagination в пользу search_after + PIT, поставить track_total_hits: false и привести маппинг в порядок (keyword вместо text для фильтрации, отключить лишние doc_values/norms/index).

Глубже. Разбор по слоям. Запрос: bool.filter/must_not вместо must для всего, что не влияет на релевантность — filter context кэшируется в node query cache; terms вместо длинных цепочек should; вместо wildcard: "*abc" — edge_ngram/reverse анализатор на этапе индексации; script_score и runtime fields считаются на каждый документ, их выносят в поля индекса. Агрегации: высококардинальные terms-агрегации дороги, помогает уменьшение size/shard_size, composite агрегация для постраничного обхода, предагрегация через transform или rollup. Пагинация: from + size глубже нескольких тысяч документов запрещена не зря — каждый шард отдаёт from + size хитов координатору; search_after с PIT решает это. Инфраструктура: шарды 10–50 ГБ (и не сотни мелких шардов), _forcemerge для read-only индексов, index.sort под самый частый порядок сортировки, routing чтобы бить запрос по одному шарду, preference для попадания в кэш того же шарда. И банально: size: 0, если нужны только агрегаты, и terminate_after, если достаточно приблизительного ответа.

Был ли у вас опыт оптимизации запросов в PostgreSQL? Приведите пример интересного случая, где вам удалось оптимизировать запрос и решить проблему.

Коротко. Это вопрос про личный опыт — здесь важна структура рассказа, а не сам факт. Стройте ответ по схеме: контекст и метрика (что тормозило и на сколько) → как нашли (pg_stat_statements/auto_explain) → что показал EXPLAIN ANALYZE → гипотеза → что сделали → замеренный результат → чем заплатили.

Глубже. Хорошие «интересные случаи», которые легко рассказать честно и подробно: (1) неверная оценка кардинальности из-за коррелированных колонок — планировщик считал city и region независимыми, выбирал Nested Loop, лечилось CREATE STATISTICS ... (dependencies, mcv); (2) LIKE 'abc%' не использовал индекс из-за локали — помог text_pattern_ops или триграммный GIN; (3) OFFSET 500000 в выгрузке — заменили на keyset-пагинацию по (created_at, id); (4) prepared statement после пятого вызова переключился на generic plan и потерял селективность параметра — лечилось plan_cache_mode = force_custom_plan; (5) сортировка уходила на диск — подняли work_mem для конкретной сессии; (6) NOT IN (SELECT ...) с NULL внутри — переписали на NOT EXISTS и получили anti-join вместо чудовищного плана. Типичные ошибки в ответе: рассказывать без цифр («стало быстрее»), сводить всё к «добавил индекс» и не упоминать цену решения.

What are window functions (in databases)?

Коротко. Оконные функции считают агрегат по «окну» строк, связанному с текущей строкой, но, в отличие от GROUP BY, не схлопывают строки: на выходе столько же строк, сколько на входе, просто с дополнительной колонкой. Синтаксис — func(...) OVER (PARTITION BY ... ORDER BY ... frame).

Глубже. Делятся на три группы: ранжирующие (row_number, rank, dense_rank, ntile, percent_rank), смещающие (lag, lead, first_value, last_value, nth_value) и обычные агрегаты в оконном режиме (sum, avg, count с OVER). Ключевая тонкость — рамка окна: при наличии ORDER BY по умолчанию действует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, то есть накопительный итог, причём RANGE объединяет строки с одинаковым значением ключа сортировки — из-за этого last_value() без явной рамки почти всегда возвращает «не то». Оконные функции вычисляются после WHERE/GROUP BY/HAVING, поэтому в WHERE их использовать нельзя — нужен подзапрос или CTE. В плане они видны как узел WindowAgg, а перед ним почти всегда Sort — индекс, совпадающий с PARTITION BY, ORDER BY, позволяет этот Sort убрать. Несколько окон с одинаковым OVER можно вынести в WINDOW w AS (...).

SELECT user_id, created_at, amount,
       row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn,
       sum(amount)  OVER (PARTITION BY user_id ORDER BY created_at
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM purchases;

There is a method that iterates through all records from a table by increasing the offset. What problems do you see?

Коротко. Две проблемы. Производительность: OFFSET N заставляет сервер каждый раз считать и выбросить N строк, поэтому обход всей таблицы получается квадратичным по числу страниц. Корректность: между страницами данные меняются, поэтому вставки и удаления сдвигают окно — часть строк вы увидите дважды, часть пропустите. Плюс если ORDER BY неуникален (или отсутствует), порядок вообще не определён и страницы могут перемешаться.

Глубже. Правильное решение — keyset (seek) пагинация: запоминать последний виденный уникальный ключ и брать WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT 100. Сравнение кортежей в PostgreSQL работает лексикографически и умеет использовать составной индекс (created_at, id), так что каждая страница стоит одинаково — O(log n + page). Если нужен именно снимок на момент начала обхода, альтернативы: курсор (DECLARE ... CURSOR) в рамках одной транзакции, репликационный слот/CDC, или REPEATABLE READ-транзакция — но долгие транзакции держат горизонт видимости и мешают vacuum, так что для многочасового обхода лучше keyset. Ещё частый вариант — обход по диапазонам первичного ключа блоками, если ключ плотный.

Расскажи про Order by и Group by?

Коротко. GROUP BY схлопывает строки в группы по значениям перечисленных выражений, чтобы применить к ним агрегаты; ORDER BY только упорядочивает итоговый результат и на состав строк не влияет. Это разные стадии: GROUP BY идёт сразу после WHERE, ORDER BY — почти в самом конце, перед LIMIT.

Глубже. В GROUP BY действует правило: в SELECT можно вынести только сгруппированные выражения и агрегаты (PostgreSQL проверяет это строго; MySQL с выключенным ONLY_FULL_GROUP_BY — нет, и это источник тихих багов). Исключение — функциональная зависимость: если сгруппировали по первичному ключу, остальные колонки этой таблицы доступны. В плане GROUP BY реализуется как HashAggregate (строит хеш-таблицу в work_mem, порядок не гарантирован) или GroupAggregate поверх отсортированного входа; на больших группировках PostgreSQL 13+ умеет проливать хеш-агрегацию на диск, а не выбирать заведомо другой план. ORDER BY — это Sort (с пометкой Sort Method: quicksort / external merge Disk) либо он вовсе исчезает, если данные уже приходят в нужном порядке из индекса. Отдельно: ORDER BY может ссылаться на алиасы и номера колонок из SELECT, а WHERE и GROUP BY — на алиасы нет (в PostgreSQL GROUP BY алиас всё-таки допускает как расширение, но переносимо это не работает). И GROUP BY сам по себе не гарантирует порядок вывода — нужен явный ORDER BY.

Что такое подзапросы? Где можно использовать?

Коротко. Подзапрос — это SELECT внутри другого запроса. Использовать можно в FROM (производная таблица), в JOIN, в WHERE/HAVING (скалярный, IN, EXISTS, ANY/ALL), в списке SELECT (скалярный подзапрос), в WITH (CTE) и в LATERAL-форме, когда подзапрос должен видеть колонки левой таблицы.

Глубже. Практически важно деление на некоррелированные (выполняются один раз) и коррелированные (ссылаются на внешнюю строку и потенциально выполняются на каждую строку). PostgreSQL часто умеет «расплющить» подзапрос: IN (SELECT ...) превращается в semi-join, NOT EXISTS — в anti-join, простой подзапрос в FROM вплавляется в основной запрос. Но есть случаи, когда этого не происходит: подзапрос с LIMIT, DISTINCT, GROUP BY, оконными функциями, volatile-функциями — становится барьером. Отдельная ловушка — NOT IN (SELECT col ...), где col может быть NULL: по стандарту результат становится NULL, anti-join невозможен, и план деградирует в чудовищный filter. Всегда предпочитайте NOT EXISTS. Скалярный подзапрос в SELECT, выполняемый по строке, — это классический SQL-N+1 внутри одного запроса; обычно переписывается в LEFT JOIN LATERAL.

Расскажи про join?

Коротко. JOIN соединяет строки двух наборов по условию, формируя строки с колонками из обоих. INNER оставляет только совпавшие пары, LEFT/RIGHT/FULL OUTER дополняют результат несовпавшими строками соответствующей стороны, добивая противоположные колонки NULL’ами, CROSS даёт декартово произведение.

Глубже. Мысленная модель: любой join логически — это декартово произведение с последующим фильтром по ON, а внешний join потом ещё добавляет «потерянные» строки. Реально исполнитель, конечно, произведение не строит: он выбирает Nested Loop (хорош, когда внешняя сторона мала, а внутренняя имеет индекс по ключу соединения), Hash Join (строит хеш по меньшей стороне, требует эквиусловия и памяти work_mem) или Merge Join (обе стороны отсортированы по ключу, отлично когда данные уже приходят из индексов). Кардинальность результата зависит от уникальности ключа: если справа ключ не уникален, join размножит строки левой стороны — это самая частая причина «внезапно выросших сумм» после добавления join’а.

На что именно опирается оптимизатор при каждом новом EXPLAIN?

Коротко. На статистику из pg_statistic (собранную последним ANALYZE), на reltuples/relpages из pg_class, на текущий набор индексов и ограничений, на cost-константы и параметры вроде work_mem, effective_cache_size, random_page_cost, enable_*, и на конкретные значения параметров запроса. Ничего из этого он не «замеряет» на месте — он считает по метаданным.

Глубже. Именно поэтому один и тот же EXPLAIN может дать разный план в разное время: autovacuum пересобрал статистику или обновил reltuples, таблица выросла, добавился/удалился индекс, поменялся GUC в сессии, подставились другие значения параметров, изменилась доля видимых страниц в visibility map (влияет на выбор Index Only Scan). Ещё два фактора: prepared statement после пяти выполнений может перейти на generic plan с усреднёнными оценками (plan_cache_mode управляет этим), а при большом числе таблиц в запросе включается GEQO, который перебирает порядок соединений стохастически — и на одном и том же входе может дать разные планы. Расширенная статистика CREATE STATISTICS добавляет знание о зависимостях между колонками, которых по одиночным гистограммам не видно.

Что такое EXPLAIN в SQL?

Коротко. EXPLAIN — команда, которая показывает план выполнения запроса: какие способы доступа к таблицам выбраны, в каком порядке и какими алгоритмами соединяются наборы, где сортировка и агрегация, а также оценки стоимости и числа строк. Сам запрос при этом не выполняется.

Глубже. Вывод — дерево узлов; читается снизу вверх, вложенные узлы выполняются раньше родительских. У каждого узла печатается cost=A..B rows=N width=W, где A — стоимость получения первой строки (важно для LIMIT), B — полной выборки, rows — оценка числа строк, width — средний размер строки в байтах. EXPLAIN есть и в других СУБД (MySQL, Oracle EXPLAIN PLAN, SQL Server SET SHOWPLAN), но формат и набор узлов у всех свой.

Explain/Explain Analyze что показывает, из чего можно сделать вывод чтобы было лучше?

Коротко. EXPLAIN показывает план и оценки, EXPLAIN ANALYZE — ещё и факт: реальное время каждого узла, реальное число строк, число проходов. Выводы делают из трёх вещей: где расходятся оценка и факт (плохая статистика), какой узел съедает основное эксклюзивное время, и сколько страниц читается с диска (BUFFERS).

Глубже. Алгоритм чтения на практике: (1) найти узел с наибольшим actual time за вычетом времени детей и умножением на loops — это точка приложения усилий; (2) посмотреть на нём rows vs actual rows — расхождение в 10 и более раз означает, что виноват не узел, а оценка, и лечить надо ANALYZE, расширенной статистикой или переписыванием запроса; (3) посмотреть Rows Removed by Filter — если прочитали миллион, а оставили сотню, нужен индекс или более селективный предикат; (4) Sort Method: external merge Disk и Batches: N у Hash Join означают нехватку work_mem; (5) Buffers: shared read=... в разы больше hit — данные не в кэше, стоит смотреть на размер выборки и на effective_cache_size.

Задача на sql-запрос с join’ом.

Коротко. Типовая формулировка: «есть users(id, name) и orders(id, user_id, amount, created_at) — вывести всех пользователей и сумму их заказов за 2026 год, включая тех, у кого заказов не было». Ключевой момент — LEFT JOIN и условие по дате именно в ON, а не в WHERE, иначе пользователи без заказов исчезнут.

Глубже. Правильный вариант и его разбор:

SELECT u.id,
       u.name,
       coalesce(sum(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o
       ON o.user_id = u.id
      AND o.created_at >= date '2026-01-01'
      AND o.created_at <  date '2027-01-01'
GROUP BY u.id, u.name
ORDER BY total DESC;

Что здесь проверяют: (1) понимание разницы ON и WHERE при внешнем соединении; (2) coalesce, потому что sum() по пустой группе даёт NULL, а не 0; (3) GROUP BY u.id, u.name (в PostgreSQL достаточно GROUP BY u.id, если id — первичный ключ, и это хороший бонус в ответе); (4) полуоткрытый интервал по дате вместо BETWEEN/extract(year ...) — так работает индекс по created_at. Частое продолжение вопроса: «а если нужны только пользователи с заказами?» — тогда INNER JOIN; «а как без группировки?» — LEFT JOIN LATERAL (SELECT sum(amount) ...) ON true.

Какие еще виды соединений кроме join знаешь?

Коротко. Кроме явных JOIN результаты можно комбинировать множественными операциями: UNION/UNION ALL, INTERSECT, EXCEPT. Плюс есть логические полусоединения через EXISTS/IN (semi-join) и NOT EXISTS (anti-join), соединение через LATERAL, старый синтаксис соединения через запятую в FROM с условием в WHERE, и рекурсивное соединение через WITH RECURSIVE.

Глубже. Разница принципиальная: JOIN склеивает наборы «по горизонтали» (растёт число колонок), а UNION/INTERSECT/EXCEPT — «по вертикали» (растёт или уменьшается число строк, набор колонок должен совпадать по числу и совместимым типам). UNION убирает дубликаты (то есть неявно сортирует или хеширует — это стоит денег), UNION ALL не убирает и почти всегда быстрее. Semi-join отличается от INNER JOIN тем, что не размножает строки левой стороны, даже если справа несколько совпадений, — поэтому для проверки «существует ли» всегда пишут EXISTS, а не JOIN с последующим DISTINCT.

Что будет, если вместо join применить left join в этом запросе?

Коротко. В результат добавятся строки левой таблицы, для которых справа не нашлось пары; их правые колонки будут NULL. Число строк не уменьшится и обычно вырастет, а агрегаты и последующие фильтры это заметят.

Глубже. Практические следствия, которые проверяют этим вопросом. Во-первых, count(*) вырастет, а count(o.id) — нет, потому что count(колонка) не считает NULL; это классическая ловушка. Во-вторых, если ниже по запросу есть WHERE o.status = 'paid', то LEFT JOIN немедленно выродится обратно в INNER JOIN: NULL не удовлетворяет условию, добавленные строки отфильтруются. Чтобы этого не произошло, условие переносят в ON или пишут WHERE (o.status = 'paid' OR o.id IS NULL). В-третьих, план обычно становится дороже и менее гибким: внешнее соединение ограничивает планировщику свободу менять порядок join’ов, потому что LEFT JOIN не коммутативен и не всегда ассоциативен.

Что показывает эта команда explain?

Коротко. Без конкретного плана в вопросе отвечать надо схемой: EXPLAIN показывает дерево узлов плана — способ доступа к каждой таблице, алгоритмы соединений, узлы сортировки/агрегации/лимита — и для каждого узла оценку стоимости cost=first..total, оценку числа строк rows и среднюю ширину строки width.

Глубже. Если на собеседовании показывают конкретный вывод, комментировать надо по этому чек-листу: тип нижних узлов (Seq Scan / Index Scan / Index Only Scan / Bitmap Heap Scan), есть ли Filter и сколько строк он выбрасывает, какой алгоритм соединения и в каком порядке, есть ли Sort и Hash, нет ли Materialize или Nested Loop с большим числом loops, стоит ли сверху Limit (тогда важна не полная стоимость, а стоимость первой строки), и не появился ли Gather/Parallel Seq Scan — признак параллельного плана.

Limit  (cost=0.43..8.46 rows=10 width=64)
  ->  Index Scan using orders_created_at_idx on orders  (cost=0.43..8034.12 rows=10000 width=64)
        Index Cond: (created_at >= '2026-01-01'::date)
        Filter: (status = 'paid'::text)

Здесь видно: индекс используется по created_at, status проверяется уже постфактум как Filter, а благодаря Limit 10 полная стоимость 8034 не будет уплачена — исполнение остановится после десяти подходящих строк. Если бы status='paid' встречался редко, Filter пришлось бы прогонять по большому числу строк, и тут помог бы составной или частичный индекс.

Что можно увидеть в плане запроса?

Коротко. Способы доступа к таблицам и используемые индексы, условия (Index Cond, Filter, Join Filter, Recheck Cond), алгоритмы и порядок соединений, узлы сортировки, агрегации, Limit, Unique, WindowAgg, CTE Scan, признаки параллелизма, оценки cost/rows/width, а с ANALYZE — фактические время, строки и число проходов, с BUFFERS — обращения к страницам.

Глубже. Полезные дополнительные строки: Rows Removed by Filter (сколько прочитали зря), Heap Fetches у Index Only Scan (сколько раз пришлось лезть в heap из-за неактуальной visibility map — намёк, что нужен VACUUM), Heap Blocks: exact / lossy у Bitmap Heap Scan (lossy означает, что work_mem не хватило и битмап огрубился до страниц), Sort Method и Memory/Disk, Buckets/Batches у Hash, Workers Planned/Launched у параллельных узлов, Planning Time и Execution Time, а при SETTINGS — какие GUC отличаются от дефолтных. В PostgreSQL 17 добавились MEMORY (память планировщика) и SERIALIZE (время формирования и отправки результата клиенту).

Зачем нужен group by и отличие от order by.

Коротко. См. выше про ORDER BY и GROUP BY. Кратко: GROUP BY нужен, чтобы посчитать агрегаты в разрезе — суммы, счётчики, средние по ключу; он меняет число строк. ORDER BY число строк не меняет, он только задаёт порядок вывода.

Глубже. Отличие в этой формулировке — акцент на «зачем». Без GROUP BY агрегат считается по всей выборке и даёт одну строку; с GROUP BY — по одной строке на уникальную комбинацию группирующих выражений. Расширения: GROUPING SETS, ROLLUP, CUBE позволяют получить несколько уровней агрегации за один проход вместо UNION ALL из нескольких запросов, а функция GROUPING() помогает отличить «итоговую» строку от строки с NULL в данных. И ещё: DISTINCT — это по сути GROUP BY по всем колонкам, планировщик реализует их одинаковыми узлами.

чем разница where и having?

Коротко. См. выше: WHERE — фильтр строк до группировки, без агрегатов; HAVING — фильтр групп после агрегации, с агрегатами.

Глубже. Дополнение к сказанному: WHERE применяется и в запросах без GROUP BY, а HAVING без агрегатов практически бессмыслен. Ещё один нюанс, который иногда спрашивают: в WHERE можно использовать подзапрос с агрегатом (WHERE amount > (SELECT avg(amount) FROM ...)) — это не нарушает правило, потому что агрегат вычисляется в отдельном подзапросе, а не по текущей группе.

Можно ли использовать where с join? (Дa)

Коротко. Да, конечно. JOIN ... ON задаёт условие соединения, WHERE — фильтр по уже соединённому набору. Для INNER JOIN они логически эквивалентны и планировщик всё равно перетасует предикаты как ему выгодно; для внешних соединений — принципиально нет.

Глубже. Разница проявляется на LEFT/RIGHT/FULL JOIN: условие в ON применяется до добавления NULL-строк, условие в WHERE — после, поэтому предикат по «внешней» стороне в WHERE отбрасывает NULL-строки и вырождает внешний join во внутренний. Обратно: WHERE o.id IS NULL после LEFT JOIN — это осознанный приём для поиска «сирот» (эмуляция anti-join). Отдельный стилистический момент: старый синтаксис FROM a, b WHERE a.id = b.a_id работает и это тот же inner join, но в современном коде так не пишут — легко потерять условие и получить декартово произведение.

Какие есть join-ы и чем отличаются? (Left/right/cross join)

Коротко. См. выше про типы join. По сути: INNER — только совпадения; LEFT — все строки левой плюс совпадения справа (иначе NULL); RIGHT — зеркально; FULL — все строки обеих сторон; CROSS — декартово произведение без условия.

Глубже. RIGHT JOIN — это LEFT JOIN с переставленными таблицами, и в коде его почти не пишут: людям проще читать запрос, где «главная» таблица слева. CROSS JOIN не бесполезен: он нужен для генерации комбинаций (например, календарь generate_series × список сервисов, чтобы получить нули в пропущенных днях), но случайный CROSS JOIN из-за забытого условия — классическая авария. FULL OUTER JOIN в PostgreSQL требует эквиусловия для hash/merge-реализации; с неэквиусловием план становится крайне дорогим.

Твой написанный запрос начал тормозить, как бы ты действовал?

Коротко. Проверил бы, что изменилось: объём данных, план, нагрузка или окружение. Практически — снял бы EXPLAIN (ANALYZE, BUFFERS) на боевых параметрах, сравнил с ожидаемым планом, посмотрел pg_stat_statements (выросло mean_exec_time или calls?) и pg_stat_user_tables (когда был последний autovacuum/autoanalyze, сколько мёртвых строк).

Глубже. Типичные причины «внезапно начал»: таблица перешла порог, на котором планировщик сменил Index Scan на Seq Scan или Nested Loop на Hash Join; статистика устарела после массовой заливки; накопился bloat и Index Only Scan перестал работать из-за Heap Fetches; параметр запроса стал непредставительным (искали по редкому значению, стали искать по частому); prepared statement переключился на generic plan; появился конкурент, забирающий IO; сменилась версия PostgreSQL или GUC после деплоя; индекс был удалён или стал невалидным после неудачного CREATE INDEX CONCURRENTLY (indisvalid = false). Если план тот же и он хороший, значит дело не в SQL — смотреть на блокировки, пул соединений и сеть.

Explain говорит, что все хорошо. Что тогда?

Коротко. Значит, узкое место не в плане. Проверяю: не N+1 ли это на стороне приложения, не ждёт ли запрос блокировку, не тратится ли время на передачу и сериализацию результата, не забит ли пул соединений, и совпадает ли план, который я смотрю, с планом, который реально исполняется на проде.

Глубже. Важная методическая деталь: EXPLAIN без ANALYZE «говорит хорошо» на основании оценок, которые могут быть неверными — это вообще не доказательство. Первый шаг — заменить на EXPLAIN (ANALYZE, BUFFERS) и убедиться, что Execution Time действительно мал. Если и он мал, а пользователь ждёт секунды, то время уходит вне выполнения: Planning Time (бывает больше Execution Time у запросов с десятками партиций или join’ов), сериализация и передача большого результата, ожидание на LWLock/Lock (видно в pg_stat_activity.wait_event), время в приложении и ORM (маппинг, ленивая подгрузка), очередь в пуле (pgbouncer), TLS-хендшейк на каждое соединение. И проверить, что вы смотрите тот же запрос: в проде часто идёт prepared statement с другим планом, auto_explain покажет реальный.

Explain идеальный, база не нагружена, а запрос тормозит. Такое может быть? Интернет тоже хороший, а запрос все равно тормозит. Что еще может влиять на время исполнения запроса?

Коротко. Да, может. Влияют: блокировки и ожидания, время планирования, объём и сериализация результата, число round-trip’ов (курсор с маленьким fetch size, N+1), TOAST-декомпрессия больших полей, триггеры и RLS-политики, JIT-компиляция, generic plan у prepared statement, холодный кэш, а на стороне клиента — драйвер, пул и обработка строк.

Глубже. Разложу по слоям. Сервер до исполнения: парсинг и планирование (у запроса с 10 таблицами и партиционированием Planning Time легко достигает сотен миллисекунд; лечится plan_cache_mode, prepared statements, уменьшением числа партиций), ожидание блокировки от параллельного ALTER TABLE/VACUUM FULL, ожидание слота в пуле. Сервер во время исполнения, но невидимо в основном дереве плана: триггеры (их время печатается отдельной секцией Trigger ... в EXPLAIN ANALYZE), foreign key checks, RLS-предикаты, JIT (JIT: Functions ... Timing: Generation/Optimization/Emission — при jit_above_cost мелкие запросы иногда тратят на компиляцию больше, чем на работу). Сервер после исполнения: формирование строк на выход и распаковка TOAST — в PostgreSQL 17 это можно измерить EXPLAIN (ANALYZE, SERIALIZE), до 17 это время в EXPLAIN ANALYZE вообще не видно, потому что результат никуда не отправляется. Клиент и протокол: fetch_size/курсор, из-за которого 100k строк едут пачками по 10 с round-trip’ом на каждую; отсутствие пула и создание соединения на запрос; синхронный маппинг в объекты. И системное: холодные страницы после рестарта, checkpoint’ы, «шумный сосед» на виртуалке, замедлившийся диск — то, чего в плане не видно, но видно в BUFFERS и в системных метриках.

Explain что показывает помимо плана?

Коротко. Помимо дерева узлов: Planning Time и Execution Time, время инициализации/завершения JIT, отдельную секцию по времени триггеров, при BUFFERS — статистику обращений к буферам, при SETTINGS — нестандартные GUC, влиявшие на план, при WAL — объём сгенерированного WAL для модифицирующих запросов, при VERBOSE — список выводимых колонок и схемы объектов, при MEMORY (PG17) — память планировщика, при SERIALIZE (PG17) — время сериализации результата.

Глубже. Форматы вывода: FORMAT JSON|XML|YAML|TEXT — JSON удобен для машинной обработки и визуализаторов. Есть и обратная сторона: COSTS OFF, TIMING OFF, SUMMARY OFF полезны в тестах, чтобы вывод был стабильным. EXPLAIN (GENERIC_PLAN) в PostgreSQL 16+ показывает план для запроса с параметрами $1 без их значений — то, что реально может исполняться из плана-кэша. В PostgreSQL 18 BUFFERS включён по умолчанию вместе с ANALYZE.

В чем отличия explain от explain analyze?

Коротко. EXPLAIN только планирует и печатает предполагаемый план с оценками — запрос не выполняется. EXPLAIN ANALYZE реально выполняет запрос и добавляет к плану фактические цифры: время на узел, реальное число строк, число проходов, а также итоговые Planning Time и Execution Time.

Глубже. Три практических следствия. Первое: EXPLAIN ANALYZE для INSERT/UPDATE/DELETE изменит данные — выполнять надо внутри BEGIN; ... ROLLBACK;. Второе: измерение времени каждого узла само по себе стоит денег (много вызовов gettimeofday), поэтому Execution Time в EXPLAIN ANALYZE может заметно превышать реальное время запроса на узкоспециальных планах с миллионами строк — для таких случаев есть TIMING OFF. Третье: главная ценность ANALYZE — сравнение rows (оценка) с actual rows (факт); без этого невозможно понять, врёт ли планировщику статистика.

Как вы выявляли и оптимизировали медленные SQL-запросы?

Коротко. См. выше про инструменты и методику. Схема ответа: находим кандидатов по pg_stat_statements/логам → снимаем EXPLAIN (ANALYZE, BUFFERS) → классифицируем проблему (плохая оценка / нет индекса / лишние данные / N+1 / нехватка памяти) → правим → замеряем и фиксируем результат.

Глубже. Здесь ждут, что вы назовёте конкретные приёмы правки, а не только диагностику: добавить точный (в том числе частичный, покрывающий, по выражению) индекс; переписать OR в UNION ALL или в = ANY(array); заменить NOT IN на NOT EXISTS; убрать функцию с индексируемой колонки (date(created_at) = X → диапазон); заменить OFFSET на keyset; вынести тяжёлую агрегацию в материализованное представление или в предагрегированную таблицу; поднять work_mem для конкретного отчётного запроса; разбить один монстр-запрос на два с временной таблицей; включить партиционирование, если запросы всегда по диапазону дат. И обязательно — регресс-проверка: убедиться, что новый индекс не убил скорость записи.

Что такое профилирование запросов? Чем отличаются ANALYZE и EXPLAIN ANALYZE ? Как по выводу EXPLAIN понять, выполняется ли запрос быстро или медленно? Какие «красные флаги» в выводе EXPLAIN указывают на проблемы?

Коротко. Профилирование — систематический сбор данных о времени и ресурсах, потраченных запросами (pg_stat_statements, auto_explain, лог медленных запросов, профиль ожиданий). ANALYZE — отдельная команда, собирающая статистику распределения данных для планировщика; EXPLAIN ANALYZE — выполнение запроса с измерением, к статистике таблиц отношения не имеет. Совпадение имён — историческая ловушка.

Глубже. Понять «быстро или медленно» по чистому EXPLAIN строго нельзя: cost — безразмерная величина, сравнивать её можно только между планами одного запроса, а не с секундами. Ориентир — EXPLAIN ANALYZE и Execution Time. Красные флаги в выводе: (1) rows и actual rows расходятся на порядок и больше — статистика врёт; (2) Seq Scan по большой таблице с селективным Filter и большим Rows Removed by Filter; (3) Nested Loop с loops в десятки тысяч и дорогой внутренней стороной; (4) Sort Method: external merge Disk: ... kB — сортировка не влезла в work_mem; (5) Hash Batches: N > 1 / Disk Usage у Hash Join — то же самое для хеша; (6) Heap Blocks: ... lossy в Bitmap Heap Scan; (7) Heap Fetches заметно больше нуля у Index Only Scan — нужен VACUUM; (8) Buffers: shared read сильно больше shared hit; (9) Materialize или CTE Scan над большим набором, который потом сканируется многократно; (10) Planning Time сопоставим с Execution Time или больше; (11) Filter там, где ожидался Index Cond — предикат не индексируемый (функция над колонкой, несовпадение типов, LIKE '%x%').

Расскажите про типы JOIN в SQL: LEFT JOIN , INNER JOIN , RIGHT JOIN , FULL JOIN . В чем их различия и когда какой использовать?

Коротко. INNER — когда нужны только связанные пары (заказы с существующими пользователями). LEFT — когда левая таблица главная и её строки должны остаться независимо от наличия связи (список пользователей с их последним заказом или без него). RIGHT — то же зеркально, применяется редко. FULL — когда важны непарные строки с обеих сторон, типично при сверке двух источников данных.

Глубже. Практические правила выбора. Если по внешнему ключу связь обязательна (NOT NULL + FK), LEFT JOIN семантически избыточен, но он ещё и ограничивает планировщику перестановку соединений — так что честный INNER JOIN быстрее и честнее по смыслу. LEFT JOIN + WHERE right.id IS NULL — идиома «найти строки без пары»; в PostgreSQL это превращается в Anti Join и работает хорошо, но NOT EXISTS читается лучше. FULL JOIN для сверки обычно пишется с coalesce(a.key, b.key) в SELECT, иначе ключ теряется на непарных строках. И общее: любой внешний join делает результат «шире» по NULL’ам, поэтому агрегаты после него надо писать аккуратно (count(o.id) вместо count(*)).

Как можно оптимизировать сложные запросы?

Коротко. Сначала уменьшить объём обрабатываемых данных (фильтровать как можно раньше и как можно селективнее, не тащить лишние колонки и строки), потом дать планировщику правильные вводные (актуальная статистика, расширенная статистика, нужные индексы), потом при необходимости изменить форму запроса (разбить на этапы, заменить коррелированные подзапросы на join/LATERAL, вынести предагрегацию), и только в крайнем случае — крутить GUC и подсказки.

Глубже. Конкретный набор приёмов для «сложного» запроса: (1) свести к минимуму число таблиц, реально нужных для ответа, — часто половина join’ов нужна только ради колонок в SELECT, и их можно вынести на второй запрос по 20 найденным id; (2) предагрегировать перед join’ом, а не после — соединять 1000 сгруппированных строк дешевле, чем миллион исходных; (3) применить LIMIT как можно раньше, если это допустимо (шаблон «сначала найти 20 id по индексу, потом дотянуть детали»); (4) следить за CTE: с PostgreSQL 12 однократно используемая нерекурсивная CTE инлайнится, но MATERIALIZED, рекурсия и множественное использование делают её барьером — иногда это полезно (посчитать один раз), иногда губительно (предикат не проталкивается внутрь); (5) join_collapse_limit/from_collapse_limit поднять, если таблиц больше 8 и планировщик явно выбирает плохой порядок, помня о росте времени планирования; (6) CREATE STATISTICS на коррелированные колонки; (7) партиционирование с partition pruning, если запросы всегда содержат ключ секционирования; (8) для аналитики — материализованные представления или отдельное OLAP-хранилище, потому что тюнинг OLAP-запроса на OLTP-схеме имеет предел.

Есть таблица покупок - id юзера, сумма покупки - как найти всех юзеров, которые купили больше, чем на $100? (having)

Коротко. Сгруппировать по пользователю и отфильтровать группы по сумме через HAVING.

SELECT user_id, sum(amount) AS total
FROM purchases
GROUP BY user_id
HAVING sum(amount) > 100;

Глубже. Уточнения, которые обычно спрашивают следом. «Больше, чем на $100 за одну покупку» — это уже WHERE amount > 100 без группировки. «Больше $100 за 2026 год» — фильтр по дате в WHERE, сумма в HAVING. Если нужны имена — JOIN users после агрегации или подзапросом, чтобы не тащить лишние колонки в GROUP BY. По производительности: HAVING по агрегату индексом не ускорить, ускоряется только предварительный фильтр в WHERE; при постоянном использовании такого отчёта имеет смысл держать предагрегированную таблицу или материализованное представление с суммами по пользователю.

Для чего нужен join? Обязателен ли reference?

Коротко. JOIN нужен, чтобы собрать в одной строке данные из нескольких таблиц, связанных логически, — это прямое следствие нормализации. Внешний ключ (reference) для join’а не обязателен: соединять можно любые колонки совместимых типов, хоть по вычисляемому выражению.

Глубже. Внешний ключ — это про целостность данных, а не про возможность соединения. Что он всё-таки даёт оптимизатору: PostgreSQL с 9.6 учитывает FK-ограничения при оценке кардинальности соединения — знание, что каждая строка слева имеет ровно одну пару справа, заметно улучшает оценки в многотабличных запросах. Ещё важнее другое: FK не создаёт индекс на дочерней стороне автоматически (индекс появляется только на родительской, через PRIMARY KEY/UNIQUE), поэтому индекс по orders.user_id нужно создавать руками — иначе и join, и каскадные удаления будут медленными.

Как работает explain/explain analyze? Что будешь использовать на проде? Как на проде делать explain analyze для update/delete?

Коротко. EXPLAIN проходит парсинг, rewrite и планирование и печатает выбранный план, не исполняя его; EXPLAIN ANALYZE доводит дело до исполнителя с инструментированием каждого узла. На проде безопасно всегда EXPLAIN и EXPLAIN (GENERIC_PLAN); EXPLAIN ANALYZE — осознанно и точечно, лучше через auto_explain. Для UPDATE/DELETE — только внутри явной транзакции с ROLLBACK.

Глубже.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders SET status = 'cancelled' WHERE created_at < now() - interval '1 year';
ROLLBACK;

Что важно помнить при таком приёме: изменения откатятся, но побочные эффекты останутся — сработают триггеры (в том числе пишущие в другие системы), будут взяты блокировки на строки на всё время выполнения (на проде это может заблокировать пользователей), сгенерируется WAL и вырастет число мёртвых строк, потому что откат не возвращает место. Поэтому на нагруженном проде лучше сначала посмотреть чистый EXPLAIN, а EXPLAIN ANALYZE делать на реплике или на копии данных; для UPDATE нередко достаточно проанализировать эквивалентный SELECT с тем же WHERE. auto_explain с auto_explain.log_min_duration, log_analyze = on и обязательно log_nested_statements/sample_rate меньше единицы — это способ получать реальные планы медленных запросов на проде без ручного вмешательства; включать log_analyze надо с оглядкой на накладные расходы инструментирования (или с log_timing = off).

Приходят девопсы и говорят, что REST-овый запрос твоего сервиса висит 3 секунды на базе на таблице в 5 млн строк. На что будешь смотреть в первую очередь?

Коротко. Сначала — сколько запросов на самом деле делает этот эндпоинт (один тяжёлый или тысяча лёгких из-за N+1). Потом — конкретный SQL и его EXPLAIN (ANALYZE, BUFFERS): есть ли индекс под фильтр и сортировку, нет ли Seq Scan по этим 5 млн, нет ли COUNT(*) и OFFSET, не улетает ли сортировка на диск.

Глубже. 5 млн строк — это немного, полный seq scan такой таблицы обычно занимает сотни миллисекунд-секунды, так что 3 секунды почти всегда означают одно из: (а) N+1 и сотни round-trip’ов, (б) отсутствие индекса под WHERE/ORDER BY и сортировка всего набора, (в) COUNT(*) для пагинации плюс сам запрос, (г) join, размножающий строки, с последующим DISTINCT, (д) блокировка. Порядок действий: посмотреть трейс/логи приложения на число SQL-вызовов, взять топ по pg_stat_statements за окно инцидента, снять план, проверить pg_stat_activity на wait_event. Заодно уточняю у девопсов, деградация постоянная или под нагрузкой, и что изменилось — деплой, миграция, рост данных.

Что такое Explain?

Коротко. См. выше: команда, показывающая план выполнения запроса с оценками стоимости и кардинальности, без выполнения самого запроса.

Глубже. Отличие этой формулировки — она короткая, значит ответ должен быть коротким и точным: «EXPLAIN — это окно в решения планировщика; он показывает, что СУБД собирается сделать, а не что она сделала. Чтобы увидеть факт, нужен EXPLAIN ANALYZE».

Explain why and why the output differs before and after Go v1.22. How to fix it if Go <v1.22.

Коротко. Вопрос попал в подтему по слову «explain» и к SQL отношения не имеет — это про семантику переменной цикла в Go. До Go 1.22 переменные цикла for i, v := range ... создавались один раз на весь цикл, поэтому все замыкания и все &i указывали на одну и ту же переменную; с Go 1.22 переменная создаётся заново на каждой итерации.

Глубже. Классическая демонстрация — запуск горутин в цикле: до 1.22 такой код печатал произвольные значения (часто одно и то же последнее), с 1.22 печатает все значения (в произвольном порядке).

for i := 0; i < 3; i++ {
    go func() { fmt.Println(i) }()
}

Починка для Go < 1.22 — сделать копию на каждой итерации (i := i в теле цикла) или передать значение параметром: go func(i int) { fmt.Println(i) }(i). Управляется поведение через версию языка в go.mod (go 1.22) или директивой //go:build go1.21 для файла; сборка старого модуля новым компилятором сохраняет старую семантику, если в go.mod указана версия ниже 1.22.

Чем отличается left join от left outer join?

Коротко. Ничем. OUTER — необязательное ключевое слово: LEFT JOIN и LEFT OUTER JOIN — полные синонимы, как и RIGHT [OUTER] JOIN, FULL [OUTER] JOIN. Аналогично INNER можно опустить: просто JOIN — это INNER JOIN.

Глубже. Единственное практическое различие — читаемость и командный стиль. Замечу только, что в стандарте SQL CROSS JOIN и NATURAL JOIN слово OUTER не принимают, а JOIN ... USING (col) можно комбинировать с любым типом. Если кандидат уверенно отвечает «ничем, OUTER — синтаксический сахар», вопрос закрыт.

Какой запрос наиболее оптимальный: если много разных таблиц и мы все объединяем join ’ами, или когда все данные в одной таблице?

Коротко. Зависит от профиля нагрузки. Одна широкая денормализованная таблица быстрее для чтения (нет join’ов, меньше случайных обращений), но дороже для записи, хуже по целостности и по объёму. Нормализованная схема с join’ами компактнее, целостнее и гибче, а join’ы по индексированным ключам в PostgreSQL стоят недорого — до определённого масштаба.

Глубже. Правильная развёрнутая позиция: по умолчанию нормализуем (3NF) — это решает проблемы аномалий обновления и дублирования, а join по PK/FK с индексом обходится дёшево. Денормализуем точечно и осознанно, когда конкретный горячий запрос упирается в множественные join’ы: кэш-колонки (счётчики, «последний статус»), материализованные представления, отдельные read-модели. Аргументы против «одной большой таблицы»: строки становятся шире, в страницу влезает меньше строк, и даже запрос по двум колонкам читает больше страниц (в PostgreSQL нет колоночного хранения); обновление одного поля переписывает всю строку целиком (MVCC создаёт новую версию) и раздувает WAL; TOAST усложняет картину; любое изменение схемы дороже. Аргументы против «сотни join’ов»: после 8 элементов планировщик упирается в join_collapse_limit, после 12 включается GEQO, и качество плана становится непредсказуемым; ошибки оценки кардинальности перемножаются с каждым уровнем join’а. Итог формулируется так: не «что лучше вообще», а «нормализованная база как источник истины плюс денормализованные проекции под конкретные запросы».

Что делать если запрос тормозит?

Коротко. См. выше алгоритм: воспроизвести → EXPLAIN (ANALYZE, BUFFERS) → найти узел-виновника → определить класс проблемы (оценка / доступ / объём / память / внешние причины) → исправить → замерить.

Глубже. Компактный чек-лист для устного ответа: свежая ли статистика (ANALYZE); есть ли индекс под фильтр и сортировку и используется ли он (не мешает ли функция над колонкой или несовпадение типов); не тащим ли лишние строки и колонки; нет ли OFFSET/COUNT(*)/DISTINCT, которых можно избежать; хватает ли work_mem (сортировка/хеш на диске); нет ли N+1 со стороны приложения; не ждём ли блокировку. Хорошо ещё явно проговорить: сначала измеряем, потом чиним — «поставить индекс на всякий случай» это не оптимизация.

Чем отличается explain от explain analyze?

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

Глубже. Дополнительный акцент, который стоит дать при повторе вопроса: EXPLAIN безопасен для любых запросов, EXPLAIN ANALYZE — модифицирующий, для DML его оборачивают в BEGIN ... ROLLBACK. И главное, ради чего его вообще запускают: сравнение rows и actual rows.

На основании чего работает explain, если он не выполняет запрос?

Коротко. На основании статистики и метаданных: pg_statistic (гистограммы, MCV, n_distinct, доля NULL, корреляция), pg_class.reltuples/relpages, каталог индексов и ограничений, а также cost-константы и настройки сессии. Всё это собрано заранее командой ANALYZE и autovacuum’ом.

Глубже. Механика оценки: по предикату WHERE x = 'foo' планировщик сначала смотрит, есть ли 'foo' в списке самых частых значений — если есть, берёт готовую частоту; если нет, оценивает как «остаток вероятности, делённый на число нечастых уникальных значений». Для диапазонов используется гистограмма. Для соединений оценка строится из селективностей и n_distinct обеих сторон, с 9.6 — с учётом FK-ограничений. Отсюда и типовые провалы: коррелированные колонки (планировщик считает предикаты независимыми и перемножает селективности), выражения и функции без своей статистики (можно создать индекс по выражению — тогда для него соберётся статистика), значения вне диапазона гистограммы (данные за «завтра» после массовой вставки без ANALYZE), нестабильный n_distinct на больших таблицах (лечится ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...) и увеличением default_statistics_target).

Какая из них будет быстрее работать при обычном селекте?

Коротко. Вопрос — продолжение предыдущего про «много таблиц с join’ами против одной широкой». При обычном простом SELECT по фильтру быстрее будет одна таблица: нет join’ов, один проход по индексу, одно обращение к heap.

Глубже. Оговорка, которую стоит добавить: «быстрее» здесь верно для запроса, которому нужны данные из всех этих таблиц сразу. Если же селект выбирает две узкие колонки, широкая таблица может проиграть: страница вмещает меньше строк, значит для того же числа строк надо прочитать больше страниц, и покрывающий индекс в нормализованной схеме окажется эффективнее. Плюс к этому широкая таблица платит на записи, и под смешанной нагрузкой чтение с неё замедлится из-за bloat и конкуренции. Так что честный ответ: на чистом чтении одной сущности — да, одна таблица; в системе целиком — надо смотреть на профиль нагрузки.

Как работает explain?

Коротко. См. выше про основания работы планировщика. Механика: запрос парсится, переписывается (раскрываются вью и правила), затем планировщик перебирает варианты путей доступа и порядков соединения, оценивает стоимость каждого по статистике и cost-константам, выбирает самый дешёвый и печатает его в виде дерева. Исполнитель не запускается.

Глубже. Перебор устроен снизу вверх методом динамического программирования (System-R): сначала лучшие пути для каждого отношения по отдельности (с учётом «интересных» порядков сортировки), потом лучшие пары, тройки и так далее. Для каждого узла считается пара стоимостей — startup и total, — потому что под LIMIT выгоден план с дешёвым стартом, даже если он дороже целиком. Когда число элементов в списке FROM достигает geqo_threshold (12), точный перебор заменяется генетическим алгоритмом. Отдельно: EXPLAIN над prepared statement может показать generic plan с $1, у которого оценки сделаны без знания значений.

Как вы диагностируете причины замедления запроса, который внезапно начал медленно работать?

Коротко. См. выше про «запрос начал тормозить». Ключевой приём — сравнение «до/после»: тот же ли план (auto_explain, история планов), те же ли параметры, тот же ли объём данных, свежая ли статистика, не изменилось ли окружение (деплой, миграция, версия, GUC).

Глубже. Специфика именно внезапной деградации: чаще всего это либо «переключение плана» (flip) на пороге оценки, либо устаревшая статистика после массовой операции, либо чужая нагрузка/блокировка, либо bloat после большого UPDATE/DELETE без vacuum. Быстрые проверки: pg_stat_user_tables (n_dead_tup, last_autoanalyze), pg_stat_activity (wait_event_type, state, длительные транзакции), pg_locks с join на pg_stat_activity для поиска блокирующего, pg_stat_statements со сравнением mean_exec_time по снапшотам. Быстрый митигейт — выполнить ANALYZE на таблице: если план сразу починился, значит виновата была статистика.

Разница между JOIN и UNION в SQL?

Коротко. JOIN объединяет таблицы «по горизонтали» — берёт строки из обеих и склеивает их колонки по условию; UNION объединяет результаты «по вертикали» — складывает строки одного набора колонок в общий список. У JOIN таблицы могут иметь разные схемы, у UNION число и типы колонок должны быть совместимы.

Глубже. UNION дополнительно удаляет дубликаты (в плане это HashAggregate или Sort + Unique), UNION ALL — нет, и почти всегда быстрее; если дубликатов заведомо нет, всегда пишите ALL. ORDER BY в комбинированном запросе относится ко всему результату и пишется один раз в конце; чтобы отсортировать ветку, её надо обернуть в подзапрос. Полезный приём оптимизации: WHERE a = 1 OR b = 2 часто планируется плохо (Seq Scan), а SELECT ... WHERE a = 1 UNION SELECT ... WHERE b = 2 даёт два Index Scan; PostgreSQL умеет и сам это делать через BitmapOr, но не всегда.

Как оптимизировать тяжелые JOIN/CTE?

Коротко. Сократить объём данных до соединения (фильтровать и агрегировать раньше), обеспечить индексы по ключам соединения, следить за корректностью оценок кардинальности, дать достаточно work_mem для Hash Join, и внимательно относиться к материализации CTE — она может как спасти, так и убить план.

Глубже. По join’ам: проверьте, что тип колонок совпадает (bigint vs int, text vs varchar с разными коллациями — источник неиспользуемых индексов); убедитесь, что выбран правильный алгоритм — Nested Loop при большой внешней стороне без индекса внутри означает провал оценки; при большом числе таблиц поднимите join_collapse_limit или зафиксируйте порядок явными скобками/JOIN-синтаксисом; для «один к одному по последней записи» используйте LEFT JOIN LATERAL (... ORDER BY ... LIMIT 1) ON true вместо оконной функции по всей таблице. По CTE: с PostgreSQL 12 нерекурсивная CTE без побочных эффектов, использованная один раз, инлайнится — и это обычно хорошо, потому что предикаты проталкиваются внутрь; если CTE используется несколько раз и её вычисление дорого, явное AS MATERIALIZED даст один расчёт вместо нескольких; если CTE материализуется, а вам нужен pushdown — AS NOT MATERIALIZED. Рекурсивные CTE всегда материализуются: их оптимизируют, ограничивая глубину, добавляя условие остановки и индексируя колонку, по которой идёт рекурсия. Универсальный запасной вариант для очень тяжёлых пайплайнов — разбить на шаги с временной таблицей и ANALYZE на ней: тогда следующий шаг планируется по настоящей статистике, а не по оценке через три уровня join’ов.

Когда нужно использовать materialized view?

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

Глубже. Что важно знать. REFRESH MATERIALIZED VIEW полностью пересчитывает содержимое и берёт ACCESS EXCLUSIVE — на время обновления читать нельзя; REFRESH MATERIALIZED VIEW CONCURRENTLY читать позволяет, но требует уникального индекса на представлении, работает медленнее и всё равно пересчитывает всё (никакого инкрементального обновления в ядре PostgreSQL нет; инкрементальность даёт расширение pg_ivm). По материализованному представлению можно и нужно строить свои индексы — это его главное преимущество перед обычной вью. Сразу после CREATE MATERIALIZED VIEW ... WITH NO DATA оно нечитаемо до первого REFRESH. Альтернативы, которые стоит назвать: обычная таблица-агрегат, обновляемая по расписанию или триггерами (даёт инкрементальность и контроль над транзакционностью), кэш в приложении/Redis (если допустима несогласованность), и отдельное аналитическое хранилище, если объёмы большие. Обновление по расписанию делают через cron/pg_cron, а автоматический ANALYZE по матвью autovacuum не запускает — статистику надо собирать самому после refresh.

Частые ошибки на собесе

• Говорят, что cost в EXPLAIN — это миллисекунды. Это безразмерная величина, сравнимая только между планами одного запроса.
• Считают, что EXPLAIN ANALYZE — это «EXPLAIN с более точными оценками», и не понимают, что он реально выполняет запрос (включая UPDATE/DELETE).
• Путают ANALYZE (сбор статистики) и EXPLAIN ANALYZE (выполнение с измерением).
• Утверждают, что Seq Scan — это всегда плохо. На маленькой таблице или при выборке значительной доли строк он дешевле индексного доступа.
• Не смотрят на расхождение rows и actual rows — а это главный диагностический сигнал в плане.
• Забывают умножать actual time на loops у внутренней стороны Nested Loop и делают неверный вывод о том, какой узел медленный.
• Ставят условие по правой таблице в WHERE при LEFT JOIN и удивляются, что внешнее соединение превратилось во внутреннее.
• Считают, что для JOIN обязателен внешний ключ, и что FK автоматически создаёт индекс на дочерней стороне.
• Пишут HAVING там, где достаточно WHERE, и теряют возможность использовать индекс.
• Уверены, что GROUP BY гарантирует порядок вывода строк, и не пишут ORDER BY.
• Предлагают LIMIT/OFFSET как решение для обхода всей таблицы, не видя ни квадратичной стоимости, ни пропусков строк при конкурентных изменениях.
• Используют NOT IN с подзапросом, где возможны NULL, и получают и неверный результат, и плохой план.
• Уверены, что вью ускоряет запрос сама по себе, и не отличают её от материализованного представления.
• Считают, что CTE в PostgreSQL всегда является барьером оптимизации — это верно только до версии 12 и для MATERIALIZED/рекурсивных CTE.
• Сводят всю оптимизацию к «добавить индекс» и не упоминают цену: замедление записи, объём, WAL, потеря HOT-обновлений.

Что почитать

• PostgreSQL Docs, «Using EXPLAIN» и «Planner Cost Constants» — база по чтению планов: https://www.postgresql.org/docs/current/using-explain.html
• PostgreSQL Docs, справка по команде EXPLAIN со всеми опциями (BUFFERS, SETTINGS, GENERIC_PLAN, SERIALIZE, MEMORY, WAL): https://www.postgresql.org/docs/current/sql-explain.html
• PostgreSQL Docs, «How the Planner Uses Statistics» и «Extended Statistics» — про оценки кардинальности и CREATE STATISTICS: https://www.postgresql.org/docs/current/planner-stats.html
• pg_stat_statements и auto_explain — как искать медленные запросы и ловить их планы на проде: https://www.postgresql.org/docs/current/pgstatstatements.html и https://www.postgresql.org/docs/current/auto-explain.html
• Markus Winand, «Use The Index, Luke!» — лучшее объяснение связки индексов, ORDER BY, пагинации и keyset-пагинации: https://use-the-index-luke.com/
• Elasticsearch Docs, «Tune for search speed» и Profile API — для вопросов про оптимизацию поиска: https://www.elastic.co/guide/en/elasticsearch/reference/current/tune-for-search-speed.html

---

Список исходных вопросов с привязкой к компаниям: ../questions/query-optimization.md