Медленные запросы в PostgreSQL: как найти и ускорить
Медленный запрос ищут в три шага: смотрят, что база выполняет прямо сейчас, включают запись долгих запросов в журнал и собирают статистику модулем pg_stat_statements — он уже есть в PostgreSQL на Ubuntu. Найденный запрос разбирают командой EXPLAIN ANALYZE: она покажет, где база читает таблицу целиком и какой индекс нужен.
Содержание 10
- Что сделать прямо сейчас
- Записывать долгие запросы в журнал
- Статистика по всем запросам: pg_stat_statements
- Какие запросы занимают больше всего времени
- Как разобрать запрос: EXPLAIN ANALYZE
- Как добавить индекс, не останавливая сайт
- Ограничить время запроса
- Ошибки и как их исправить
- Источники
- Что почитать дальше
Что сделать прямо сейчас
Если сайт тормозит в эту минуту, посмотрите, какие запросы выполняются дольше всех:
- Подключитесь к серверу по SSH (как подключиться).
- Выполните:
sudo -u postgres psql -c "SELECT pid, now() - query_start AS dlitsya, usename, left(query, 80) AS zapros FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' ORDER BY dlitsya DESC;"- Сверху — запрос, который работает дольше всех: в столбце dlitsya время, в zapros — начало его текста, в pid — номер процесса.
- Если запрос завис и мешает сайту, отмените его, вместо 1290 — номер из столбца pid:
sudo -u postgres psql -c 'SELECT pg_cancel_backend(1290);' pg_cancel_backend отменяет только запрос, подключение остаётся. Программа получит ошибку canceling statement due to user request.
Если запросы быстрые, а сайт всё равно медленный, причина может быть не в базе: проверьте нагрузку на сервер — процессы: ps, top и htop.
Записывать долгие запросы в журнал
- Создайте файл настроек или откройте уже созданный:
sudo nano /etc/postgresql/16/main/conf.d/zhurnal.conf- Впишите строку — запросы дольше 1000 мс, то есть секунды, попадут в журнал:
log_min_duration_statement = 1000- Сохраните (Ctrl+O, Enter, Ctrl+X) и перечитайте настройки, перезапуск не нужен:
sudo pg_ctlcluster 16 main reload- Через час-другой работы сайта посмотрите журнал:
sudo grep 'duration:' /var/log/postgresql/postgresql-16-main.log | tail -n 20Строка выглядит так:
2026-11-05 12:40:11.204 UTC [2210] ivan@shop LOG: duration: 1532.118 ms statement: SELECT * FROM orders WHERE user_id = 42Если программа отправляет запросы с параметрами, вместо statement: будет execute <unnamed>:, а значения параметров — в следующей строке с DETAIL.
- Значение
0пишет в журнал все запросы — на загруженном сайте журнал быстро займёт диск. - Значение
-1выключает запись, так стоит после установки. - Только для одной базы:
ALTER DATABASE shop SET log_min_duration_statement = 500;— действует с новых подключений.
Где лежит журнал и как его читать — на странице Журнал PostgreSQL.
Статистика по всем запросам: pg_stat_statements
Журнал показывает отдельные долгие запросы. pg_stat_statements считает все: сколько раз выполнялся каждый запрос и сколько времени на него ушло в сумме. Так видно и короткий запрос, который сайт делает тысячи раз.
Модуль входит в пакет postgresql-16, ставить ничего не нужно. Включение:
- Проверьте, не загружены ли уже другие модули:
sudo -u postgres psql -c 'SHOW shared_preload_libraries;'. После установки ответ пустой. - Создайте файл настроек:
sudo nano /etc/postgresql/16/main/conf.d/statistika.conf Впишите shared_preload_libraries = 'pg_stat_statements'. Если в шаге 1 был ответ, перечислите через запятую: 'старый_модуль,pg_stat_statements'.
- Перезапустите базу — модуль подключается только при запуске:
sudo pg_ctlcluster 16 main restartПри перезапуске все подключения обрываются: делайте это, когда на сайте мало посетителей.
- Подключите модуль к базе сайта, вместо shop — имя базы:
sudo -u postgres psql -d shop -c 'CREATE EXTENSION pg_stat_statements;'Модуль считает запросы ко всем базам сервера, а CREATE EXTENSION нужна, чтобы смотреть статистику из этой базы.
Какие запросы занимают больше всего времени
Дайте сайту поработать час-другой, затем:
sudo -u postgres psql -d shopSELECT calls, round(total_exec_time) AS vsego_ms,
round(mean_exec_time::numeric, 1) AS sredne_ms,
rows, left(query, 80) AS zapros
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;| Столбец | Что значит |
|---|---|
calls | сколько раз выполнялся запрос |
vsego_ms | сколько миллисекунд на него ушло в сумме |
sredne_ms | сколько миллисекунд в среднем на один раз |
rows | сколько строк он вернул или изменил в сумме |
zapros | начало текста; числа и строки в нём заменены на $1, $2 |
Запросы, которые отличаются только значениями, база считает одним: WHERE user_id = 42 и WHERE user_id = 7 попадут в одну строку с WHERE user_id = $1.
С чего начинать:
- первая строка по vsego_ms — ускорение этого запроса даст сайту больше всего;
- большое sredne_ms — запрос медленный сам по себе, разберите его через EXPLAIN ANALYZE;
- огромное calls при маленьком sredne_ms — программа делает слишком много одинаковых запросов, ищите причину в ней.
Сбросить статистику, чтобы сравнить «до» и «после»: SELECT pg_stat_statements_reset();. Полный текст запроса: замените left(query, 80) на query.
Как разобрать запрос: EXPLAIN ANALYZE
Возьмите запрос из журнала или pg_stat_statements, подставьте вместо $1 настоящее значение и выполните с EXPLAIN ANALYZE в консоли базы:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; Seq Scan on orders (cost=0.00..23736.00 rows=98 width=64) (actual time=0.412..118.904 rows=101 loops=1)
Filter: (user_id = 42)
Rows Removed by Filter: 999899
Planning Time: 0.102 ms
Execution Time: 118.951 ms| Что в плане | Что значит |
|---|---|
Seq Scan on orders | база читает всю таблицу подряд |
Index Scan using … on orders | база нашла строки по индексу |
actual time=0.412..118.904 | когда шаг вернул первую строку и когда закончил, в миллисекундах |
rows=101 после actual | сколько строк шаг вернул на самом деле |
rows=98 после cost | сколько строк база ожидала |
Rows Removed by Filter: 999899 | сколько строк прочитано и отброшено |
Execution Time | сколько шёл весь запрос |
В примере база прочитала миллион строк, чтобы найти сто. Это и есть причина: по столбцу user_id нет индекса.
BEGIN;, затем EXPLAIN ANALYZE UPDATE …;, затем ROLLBACK;.Просто EXPLAIN без ANALYZE запрос не выполняет и показывает только план с ожиданиями. Сколько данных шаг взял из памяти, а сколько с диска, покажет EXPLAIN (ANALYZE, BUFFERS).
Как добавить индекс, не останавливая сайт
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);orders_user_id_idx— имя индекса, любое понятное.CONCURRENTLY— сайт продолжает писать в таблицу, пока индекс строится. Без этого слова запись в таблицу ждёт, пока индекс не будет готов.- CREATE INDEX CONCURRENTLY нельзя выполнять внутри BEGIN … COMMIT.
- Если команда прервалась с ошибкой, остаётся индекс с пометкой INVALID — его видно в
\d orders. Удалите егоDROP INDEX orders_user_id_idx;и создайте заново.
Затем повторите EXPLAIN ANALYZE: вместо Seq Scan должно появиться Index Scan или Bitmap Index Scan, а Execution Time — уменьшиться.
Индекс нужен для столбцов из WHERE и JOIN, по которым запрос выбирает немного строк из большой таблицы. Каждый индекс замедляет запись в таблицу и занимает место на диске, поэтому лишних не создавайте.
Ограничить время запроса
Чтобы один запрос не держал базу минутами, задайте пользователю сайта предел:
ALTER ROLE ivan SET statement_timeout = '30s';Действует с новых подключений. Запрос дольше 30 секунд база отменит с ошибкой canceling statement due to statement timeout. Документация PostgreSQL не советует ставить statement_timeout в postgresql.conf: предел заденет все подключения, в том числе ваши собственные из psql.
Ошибки и как их исправить
| Что видите | Причина | Что делать |
|---|---|---|
ERROR: pg_stat_statements must be loaded via shared_preload_libraries | модуль не загружен при запуске | впишите shared_preload_libraries и перезапустите базу, выше |
ERROR: relation "pg_stat_statements" does not exist | в этой базе не выполнена CREATE EXTENSION | CREATE EXTENSION pg_stat_statements; в нужной базе |
в столбце query <insufficient privilege> | смотрите чужие запросы не под postgres | войдите sudo -u postgres psql -d shop |
ERROR: canceling statement due to statement timeout | запрос дольше statement_timeout | ускорьте запрос или поднимите предел для этого пользователя |
ERROR: canceling statement due to user request | запрос отменили через pg_cancel_backend | программа может повторить запрос |
в журнале нет строк duration: | настройку не перечитали или запросов дольше предела нет | SHOW log_min_duration_statement; |
Всё сверено с PostgreSQL 16 — эта версия ставится в Ubuntu 24.04.
Источники
- PostgreSQL 16: pg_stat_statements — на английском: shared_preload_libraries и перезапуск, CREATE EXTENSION, столбцы calls, total_exec_time и mean_exec_time, замена значений на $1, кто видит текст запросов.
- PostgreSQL 16: Run-time Statistics — на английском: compute_query_id = auto включается модулем сам.
- Пакет postgresql-16 в Ubuntu 24.04: список файлов — pg_stat_statements.so входит в пакет.
- PostgreSQL 16: Error Reporting and Logging — на английском: log_min_duration_statement, 0 и −1.
- PostgreSQL 16: Using EXPLAIN — на английском: Seq Scan, Index Scan, actual time, Rows Removed by Filter.
- PostgreSQL 16: EXPLAIN — на английском: ANALYZE выполняет запрос, BEGIN и ROLLBACK, BUFFERS.
- PostgreSQL 16: CREATE INDEX — на английском: CONCURRENTLY, запрет внутри транзакции, индекс INVALID.
- PostgreSQL 16: Client Connection Defaults — на английском: statement_timeout и совет не ставить его в postgresql.conf.
- PostgreSQL 16: System Administration Functions — на английском: pg_cancel_backend.
- Исходный код PostgreSQL 16: postgres.c — строки duration, statement timeout и user request.
- Исходный код pg_stat_statements — текст must be loaded via shared_preload_libraries.
Сверено 5 ноября 2026 года.
Что почитать дальше
- Журнал PostgreSQLгде лежит журнал и какие ещё настройки включить.
- shared_buffers и памятьхватает ли базе памяти.
- max_connectionsкто занимает подключения.
- Настройки PostgreSQLгде лежат файлы настроек.