Настройка параметра work_mem в PostgreSQL для ускорения медленных SQL-запросов

Почему SQL-запросы в PostgreSQL могут выполняться медленно даже для индексированных таблиц

Запросам к большим наборам строк таблицы может потребоваться много памяти для сортировки, объединения, агрегирования или подвыборки данных. PostgreSQL использует значение work_mem параметра в качестве ограничения на объем памяти, который каждый запрос может использовать для каждой операции сортировки или хэш-таблицы. Значение по умолчанию work_mem — 4 МБ.

Если для сортировки или хеш-таблицы требуется больше памяти, чем разрешено work_mem, PostgreSQL будет использовать временные файлы на диске для выполнения таких операций.

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

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

Чтобы это выяснить, просто выполните EXPLAIN ANALYZE запрос к рабочему серверу базы данных и посмотрите его выходные данные (план выполнения запроса). Если вы видите подобные линии Sort Method: external merge Disk: <XXXX>kB, то это определенно происходит.

Более высокое work_mem значение может ускорить выполнение таких запросов, если оно позволяет разместить все временные данные запроса в памяти.

Как установить work_mem в PostgreSQL?

Используйте SET команду для изменения work_mem значения. Обратите внимание, что вы можете установить его значение:

• на уровне сервера базы данных (он будет действовать для всех запросов),
• на текущем уровне сеанса,
• для отдельных транзакций (чтобы точно настроить использование памяти для конкретных запросов, не затрагивая пользователей и соединения, выполняющие другие запросы).

В этом примере показано, как разрешить определенному SQL-запросу использовать до 256 МБ физической памяти для выполнения сортировки, а затем сбросить work_mem значение для текущего сеанса до текущего значения по умолчанию.

SET work_mem = '256MB';
SELECT * FROM users ORDER BY LOWER(display_name);
RESET work_mem;

В этом примере устанавливается work_mem одна транзакция , а затем автоматически сбрасывается значение по умолчанию для сервера.

SET LOCAL work_mem = '256MB';
SELECT * FROM users ORDER BY LOWER(display_name);

Каково оптимальное work_mem значение для запроса?

Оно должно быть выше тех, <XXXX>kB которые вы видите на EXPLAIN ANALYZE выводе.

Как правило, вы можете округлить это значение до ближайшего мегабайта. Используйте его, чтобы установить work_mem значение. Затем повторите запуск EXPLAIN ANALYZE, чтобы проверить, ...external merge Disk... исчезло ли сообщение. Если да, то вы нашли подходящее значение. Если нет, увеличьте значение на один мегабайт и EXPLAIN ANALYZE еще раз проверьте вывод. Продолжайте увеличивать значение, пока ...external merge Disk... не исчезнет.

Правильное значение иногда может быть почти вдвое больше <XXXX>kB. Помните, что PostgreSQL использует до определенного объема work_memпамяти для каждой операции сортировки или хэш-таблицы для запроса. Другими словами, если в запросе есть четыре подобных операции, PostgreSQL может использовать в четыре раза больше work_memпамяти. Поэтому слишком большое увеличение значения этого параметра может привести к ошибкам «нехватки памяти» на вашем сервере базы данных. Так что используйте его осторожно.