К содержимому
Войти

PHP Road Map

🔎 EXPLAIN и медленные запросы

EXPLAIN показывает, как Postgres собирается выполнить запрос: какие таблицы прочитает, через какие индексы, каким способом соединит строки и сколько их ждёт на каждом шаге. Когда ручка вдруг стала отвечать секунду вместо 20 мс, план запроса почти всегда объясняет почему. На собеседовании мидла просят прочитать план, отличить Seq Scan от Index Scan и рассказать, как найти медленный запрос на проде. Все планы ниже сняты на PostgreSQL 16: orders на 2 млн строк (129 МБ) и users на 200 тысяч

🧠 Как читать план

EXPLAIN и EXPLAIN ANALYZE

Просто EXPLAIN запрос не выполняет. Планировщик выбирает план и показывает свои оценки:

explain select * from orders where user_id = 4242;

Gather  (cost=1000.00..27946.77 rows=11 width=30)
  Workers Planned: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..26945.67 rows=5 width=30)
        Filter: (user_id = 4242)

План — дерево. Читают его изнутри наружу: самый глубокий узел выполняется первым и отдаёт строки родителю. Здесь два фоновых процесса читают таблицу параллельно (Parallel Seq Scan), а Gather собирает их строки

В скобках у каждого узла три оценки:

  • cost=0.00..26945.67 — стоимость до первой строки и до последней. Это не миллисекунды, а условные единицы: чтение одной страницы подряд стоит 1 (seq_page_cost), остальное считается относительно него. Стоимость родителя включает стоимость детей
  • rows=5 — сколько строк узел, по мнению планировщика, отдаст наверх. Не сколько прочитает
  • width=30 — средний размер строки в байтах

EXPLAIN ANALYZE выполняет запрос и добавляет к оценкам факт:

explain analyze select * from orders where user_id = 4242;

Gather  (cost=1000.00..27946.77 rows=11 width=30) (actual time=4.185..44.410 rows=17 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..26945.67 rows=5 width=30) (actual time=4.521..35.663 rows=6 loops=3)
        Filter: (user_id = 4242)
        Rows Removed by Filter: 666661
Planning Time: 0.043 ms
Execution Time: 44.428 ms

Тут видно сразу три вещи. Оценка 11 строк против реальных 17 — нормальная точность. Rows Removed by Filter: 666661 значит, что каждый процесс прочитал две трети миллиона строк и выбросил почти все: это главный признак, что не хватает индекса. И loops=3: узел выполнялся трижды, по разу в каждом процессе, а rows и time показаны в среднем на один проход. Чтобы получить итог, умножай на loops

Раз ANALYZE выполняет запрос, explain analyze delete ... удалит строки. Документация советует обернуть такой запрос в транзакцию и откатить её:

begin;
explain analyze delete from orders where status = 'refund';
rollback;

Буферы

Время зависит от того, лежали ли данные в памяти. Опция buffers показывает, сколько страниц по 8 КБ узел взял из кэша Postgres (shared hit) и сколько пришлось читать с диска или из кэша ОС (read). В PostgreSQL 18 буферы выводятся с ANALYZE по умолчанию, в 16 и 17 опцию пишут явно: explain (analyze, buffers)

Буферы честнее времени. Первый запуск после рестарта будет медленным из-за read, второй быстрым из-за hit, а число страниц у обоих одно и то же. Если запрос трогает десятки тысяч страниц ради десяти строк, его надо чинить, даже если сейчас он укладывается в 50 мс

Как читают таблицу

УзелЧто делаетКогда планировщик его выбирает
Seq Scanчитает таблицу целиком подряднужна большая доля строк, нет подходящего индекса, таблица маленькая
Index Scanидёт по индексу и за каждой строкой в таблицустрок мало, нужен порядок индекса
Index Only Scanберёт всё из индекса, в таблицу почти не ходитвсе столбцы запроса есть в индексе
Bitmap Heap Scanсобирает адреса строк из индекса, сортирует по страницам и читает каждую страницу один разстрок от десятков до заметной доли таблицы

После create index on orders (user_id) тот же запрос выбрал битмап:

explain (analyze, buffers) select * from orders where user_id = 4242;

Bitmap Heap Scan on orders  (cost=4.51..47.80 rows=11 width=30) (actual time=0.046..0.090 rows=17 loops=1)
  Recheck Cond: (user_id = 4242)
  Heap Blocks: exact=16
  Buffers: shared hit=11 read=8
  ->  Bitmap Index Scan on orders_user_id  (cost=0.00..4.51 rows=11 width=0) (actual time=0.035..0.035 rows=17 loops=1)
        Index Cond: (user_id = 4242)
        Buffers: shared read=3
Execution Time: 0.120 ms

44 мс превратились в 0,12 мс, а вместо всей таблицы прочитано 19 страниц. 17 заказов пользователя разбросаны по 16 страницам, и битмап прочёл каждую по одному разу. Как устроены сами индексы и почему они иногда не срабатывают, разобрано на странице «Индексы» этого модуля

Как соединяют таблицы

УзелКак работаетХорош, когда
Nested Loopдля каждой строки слева ищет пары справа, обычно по индексуслева мало строк
Hash Joinстроит хэш-таблицу по меньшей стороне и прогоняет через неё большуюобе стороны большие, условие =
Merge Joinидёт по двум отсортированным потокам одновременнообе стороны уже отсортированы по ключу

Заказы пользователей одного города, 200 человек из 200 тысяч:

explain analyze select u.id, o.amount
from users u join orders o on o.user_id = u.id
where u.city = 'city42';

Nested Loop  (... rows=1171) (actual time=0.156..17.777 rows=1007 loops=2)
  ->  Parallel Seq Scan on users u  (... rows=117) (actual ... rows=100 loops=2)
        Filter: (city = 'city42'::text)
  ->  Index Scan using orders_user_id on orders o  (... rows=11) (actual ... rows=10 loops=200)
        Index Cond: (user_id = u.id)

Внутренний Index Scan выполнился 200 раз, по разу на пользователя. Тот же join по всем пользователям с группировкой по городу планировщик сделал через Parallel Hash Join: 2 млн поисков по индексу обошлись бы дороже, чем один проход по каждой таблице

🛠️ Как найти медленный запрос

План отвечает на вопрос «почему этот запрос медленный». Сначала надо понять, какой запрос. Для этого есть три инструмента

pg_stat_statements — расширение, которое копит статистику по всем запросам базы. Одинаковые запросы с разными константами сливаются в одну строку с $1. Его подключают в shared_preload_libraries (нужен рестарт) и создают через create extension pg_stat_statements

select query, calls, round(total_exec_time) as total_ms,
       round(mean_exec_time::numeric, 2) as mean_ms, rows
from pg_stat_statements
order by total_exec_time desc
limit 10;

Сортировать стоит по total_exec_time, а не по среднему. Запрос по 0,3 мс, который вызывают миллион раз в час, нагружает базу сильнее отчёта на 10 секунд раз в день. В нашем прогоне на первом месте оказалась пагинация order by id limit $1 offset $2: 74 мс в среднем и 15 тысяч страниц на два вызова

log_min_duration_statement пишет в лог каждый запрос дольше порога: log_min_duration_statement = '500ms'. В логе будет текст запроса с параметрами, и его можно сразу отдать в explain

auto_explain пишет в лог не только запрос, но и его план. Порог задаёт auto_explain.log_min_duration, по умолчанию он -1, то есть модуль выключен. Режим auto_explain.log_analyze даёт реальные цифры, но документация предупреждает: время по узлам тогда замеряется у всех запросов, не только у медленных, и это сильно бьёт по производительности. На проде его включают осторожно

Дальше запрос с реальными параметрами прогоняют через explain (analyze, buffers) на копии базы или на реплике, если запрос пишет

🔍 Типовые причины

Нет индекса под фильтр

Признак: Seq Scan и огромный Rows Removed by Filter при паре найденных строк. Пример с user_id = 4242 выше: 666 тысяч выброшенных строк на процесс ради 17 нужных

Планировщик ошибся в числе строк

Самая полезная привычка при чтении плана — сравнивать rows в оценке и в факте. Если они расходятся в десятки раз, планировщик выбрал план под другую задачу

Эксперимент: в orders залили 300 тысяч возвратов со статусом refund, автоанализ для таблицы был выключен, analyze никто не запускал. Postgres знал о статусе refund только то, что его нет в статистике:

explain analyze select u.city, count(*)
from orders o join users u on u.id = o.user_id
where o.status = 'refund'
group by u.city;

GroupAggregate  (cost=16.51..16.53 rows=1 width=15) (actual time=454.020..496.847 rows=1000 loops=1)
  ->  Sort  (... rows=1) (actual ... rows=300000 loops=1)
        Sort Method: external merge  Disk: 3512kB
        ->  Nested Loop  (... rows=1) (actual ... rows=300000 loops=1)
              ->  Index Scan using orders_status on orders o  (... rows=1) (actual ... rows=300000 loops=1)
              ->  Index Scan using users_pkey on users u  (... rows=1) (actual ... rows=1 loops=300000)
Execution Time: 497.220 ms

Ожидали одну строку, пришло 300 тысяч. Под одну строку Nested Loop — лучший выбор, под 300 тысяч он сделал 300 тысяч поисков по индексу users. После analyze orders индекс оценил выборку в 294 783 строки, план стал Parallel Hash Join, время 58 мс

В жизни так бывает после массовой загрузки или миграции: автоанализ запускается, когда с прошлого analyze изменилось больше 50 + 10% строк таблицы, и приходит не мгновенно. Поэтому после большой вставки analyze запускают руками

Другая причина ошибки — связанные столбцы. Планировщик считает условия независимыми и перемножает их вероятности. У нас город однозначно задаёт страну, но для city = 'city42' and country = 'c4' он ждал 2 строки вместо 200. Расширенная статистика это лечит:

create statistics users_city_country (dependencies) on city, country from users;
analyze users;
-- оценка стала rows=199 при фактических 200

Она работает только для условий вида столбец = константа и in, на диапазоны и like не влияет

Сортировка и хэш не влезли в память

Sort Method: external merge Disk: 30960kB значит, что сортировка не уместилась в work_mem (по умолчанию 4 МБ) и ушла во временные файлы. Похожая метка у хэша — Batches больше 1. Поднимать work_mem глобально опасно: лимит действует на каждую операцию сортировки или хэша, а в сложном запросе их несколько, и так на каждое соединение. Обычно его поднимают для конкретной сессии или отчёта: set work_mem = '64MB'

Диск не всегда главная беда. В нашем прогоне сортировка 2 млн строк на диске заняла 515 мс, а в памяти с work_mem = '256MB' — 542 мс, выигрыша не было. Метка Disk — повод присмотреться к запросу, и первым делом стоит спросить, зачем ему сортировать 2 млн строк

OFFSET на глубоких страницах

select id, amount from orders order by id limit 20 offset 1000000;
-- Index Scan ... (actual rows=1000020): 170 мс

select id, amount from orders where id > 1000000 order by id limit 20;
-- Index Scan, Index Cond: (id > 1000000): 0,04 мс

offset не пропускает строки, а читает и выбрасывает их: план показал 1 000 020 строк ради 20. Пагинация по ключу (keyset) прыгает сразу на нужное место индекса. Плата за это: нельзя открыть сразу сотую страницу, только следующую после известного id

Причины, которых в плане нет

Запрос может долго ждать блокировку: план будет быстрым, а в pg_stat_activity у запроса появится wait_event_type = 'Lock'. И бывает, что медленный не запрос, а код: N+1 в приложении видно в pg_stat_statements как один простой запрос с огромным calls

⚠️ Где ошибаются

Смотрят план на пустой локальной базе. На таблице в пару страниц Postgres почти всегда выберет Seq Scan, есть индекс или нет. Документация прямо говорит, что выводы с игрушечной таблицы на большую не переносятся. План проверяют на данных, похожих на продовые по объёму и распределению

Читают cost как миллисекунды. Это условные единицы, их сравнивают только между планами одного запроса. Сколько запрос шёл на самом деле, покажет только ANALYZE

Забывают про loops. actual time=0.085 loops=200 у внутреннего узла — это 17 мс, а не 0,085

Запускают explain analyze на изменяющем запросе в проде. update или delete выполнится целиком. Только внутри begin ... rollback, и помни, что строки на время транзакции будут заблокированы

Оптимизируют по одному прогону. Первый запуск читает с диска, второй из кэша, и разница бывает в разы. Сравнивают буферы, а время берут со второго-третьего прогона

🎯 Вопросы с собеседований

Чем EXPLAIN отличается от EXPLAIN ANALYZE?

EXPLAIN только строит план и показывает оценки планировщика: стоимость, ожидаемое число строк, ширину строки. Запрос не выполняется. EXPLAIN ANALYZE выполняет запрос и добавляет реальное время и число строк по каждому узлу. Поэтому update и delete под ним реально изменят данные, их запускают внутри транзакции с rollback

Что значат числа в cost=0.43..45.75?

Первое число оценивает стоимость до первой строки, второе — до последней. Единицы условные: чтение одной страницы подряд стоит 1, случайное чтение по умолчанию 4, обработка строки стоит доли единицы. Стоимость узла включает стоимость его детей. Между разными запросами эти числа сравнивать бессмысленно, планировщик по ним выбирает лучший план одного запроса

Когда Postgres выбирает Seq Scan, хотя индекс есть?

Когда запрос выбирает большую долю таблицы: читать таблицу подряд дешевле, чем прыгать по индексу за каждой строкой. Ещё при маленькой таблице, при условии, которое индекс не покрывает, например функция над столбцом, и при неверной статистике. Чтобы понять, какая из причин, сравнивают оценку rows с фактом в explain analyze

Чем Nested Loop отличается от Hash Join?

Nested Loop для каждой строки внешней стороны ищет пары во внутренней, обычно по индексу. Он лучший, когда снаружи мало строк. Hash Join строит хэш-таблицу по меньшей стороне и один раз проходит по большей, он выгоден на больших объёмах и работает только с равенством. Если планировщик ждал одну строку, а пришли сотни тысяч, Nested Loop становится главной причиной тормозов

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

Включить pg_stat_statements и отсортировать по total_exec_time: так видно, какие запросы занимают базу больше всего, с учётом частоты вызовов. Разовые тяжёлые запросы ловит log_min_duration_statement, а планы прямо в лог пишет auto_explain. Найденный запрос с реальными параметрами разбирают через explain (analyze, buffers)

План был хорошим, а после большой загрузки данных запрос стал медленным. Что случилось?

Скорее всего, устарела статистика. Планировщик оценивает число строк по данным последнего analyze, и для новых значений может ждать одну строку там, где их сотни тысяч, и выбрать Nested Loop. Автоанализ запускается, когда изменилось больше 10% таблицы, и не сразу. Лечится ручным analyze после загрузки

Почему OFFSET медленный на дальних страницах?

База не умеет перепрыгнуть строки: чтобы отдать 20 строк после миллиона, она читает миллион двадцать и выбрасывает лишние. Время растёт с номером страницы. Решение — пагинация по ключу: where id > последний_id order by id limit 20, она идёт по индексу сразу с нужного места

Что показывает BUFFERS и зачем он нужен?

Сколько страниц узел взял из кэша Postgres (shared hit), сколько прочитал с диска или из кэша ОС (read), и сколько записал во временные файлы (temp). Время скачет в зависимости от прогретости кэша, а число страниц стабильно, поэтому по буферам честнее сравнивать варианты запроса. С PostgreSQL 18 буферы показываются при ANALYZE без отдельной опции

📚 Что почитать

  • Using EXPLAIN — разбор плана по узлам из документации Postgres, с подвохами про LIMIT и маленькие таблицы
  • Статистика планировщика — откуда берутся оценки строк и как работает create statistics
  • pg_stat_statements — все столбцы представления и как его включить
  • explain.depesz.com — вставляешь вывод explain analyze и видишь, какой узел съел время, с подсветкой