Совет дня №21

В прошлом совете упоминалось: команда EXPLAIN возвращает оценочный план запроса, при этом запрос не выполняется. Можно написать EXPLAIN drop table students, и данные не пропадут.

Вариант EXPLAIN ANALYZE выполняет запрос и возвращает фактический план. Цифры из него будут реальными, а не оценочными. Вместе с тем Postgres покажет отдельное время планирования (planning) и выполнения (execute).

Когда запросы тестируют, часто предпочитают EXPLAIN ANALYZE. При этом есть одна особенность, о которой пишут редко.

Если выполнить один и тот же запрос дважды, то второй запуск будет гораздо быстрее. Разница существенна — один-два порядка! Например, первый запуск занял 900 миллисекунд, второй — 50. Во-первых, почему так? Во-вторых, все-таки 900 или 50? Нужно оптимизировать или так прокатит?

Дело в том, как устроена работа с буферами. В Postgres любая работа с таблицами и индексами сводится к буферному кэшу. Это область памяти, доступная всем процессам на чтение и запись. Своего рода рабочий стол. Данные не читаются напрямую из таблицы: сперва они выгружаются в кэш и оттуда читаются строки. Запись работает в обратном порядке: страницы кэша обновляются в памяти, а затем сбрасываются на диск.

Скорее всего, первый запуск не нашел нужных буферов в кэше и спровоцировал их чтение. Такой запуск называют холодным. Если выполнить запрос еще раз, данные окажутся в кэше, и результат получится на порядки быстрее. Это горячий запуск. Примерно как в Redis — прочитали из памяти без обращения к диску.

На какой запуск ориентироваться? Иногда разработчик чувствует, что запрос не оптимален: первый вызов занял секунду. Долго. Он запускает еще раз и такой: а теперь 200 миллисекунд! Сойдет.

Лично я ориентируюсь на первый (холодный) вариант. Рассчитывать, что каждый раз данные окажутся к кэше — слишком оптимистично. База выполняет много запросов параллельно и постоянно жонглирует страницами и кэшами. Полагайте, что чаще всего ваш запрос будет холодным.

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

И еще одна неоднозначность. Предположим, вы тестируете запрос и часто его вызываете. В результате все буферы прогреты, и не ясно, как поведет себя запрос в холодном случае. Увы, нормального решения проблемы нет. Postgres не предлагает функции очистки кэша. Если это локальный Postgres, перезагрузите его, и первый запуск снова станет холодным. Разумеется, перезагружать удаленный сервер — моветон, поэтому сделайте следующее: заставьте Postgres заполнить буферный кэш чем-то другим. Запустите полное сканирование большой таблицы, погоняйте сложные запросы, и вскоре это сработает: запрос, что вы тестируете, станет холодным (метрика execute возрастет).

Согласен: все это напоминает шаманство, но иногда приходится так делать.