Пошук уроків, статей та іншого контенту
Навчіться знаходити повільні запити за журналами, статистикою та планами виконання.
Повільний запит — це не обов’язково запит із великою кількістю рядків у результаті. Він може бути повільним через:
читання великої кількості рядків із диска;
відсутність відповідного індексу;
невдалий план виконання;
блокування іншою транзакцією;
сортування або з’єднання великих наборів даних;
надто часте виконання невеликого запиту.
Для діагностики зазвичай використовують три джерела інформації:
журнали PostgreSQL;
статистику виконання запитів;
план виконання через EXPLAIN.
PostgreSQL може записувати в журнал запити, виконання яких триває довше за вказаний поріг.
Основний параметр для цього — log_min_duration_statement. Наприклад, значення 500ms означає: записувати запити, які виконувалися щонайменше 500 мілісекунд.
Перевірити поточне значення можна так:
SHOW log_min_duration_statement;Для тимчасової зміни параметра в поточній сесії:
SET log_min_duration_statement = '500ms';Це вплине лише на поточне з’єднання. Щоб налаштувати журнал для сервера, параметр змінюють у конфігурації PostgreSQL або через ALTER SYSTEM:
ALTER SYSTEM SET log_min_duration_statement = '500ms';Після зміни конфігурації потрібно перечитати її:
SELECT pg_reload_conf();Для цього потрібні відповідні права адміністратора.
Поріг залежить від типу системи:
для інтерактивного API часто важливі запити довші за 100–300 мс;
для фонових задач поріг може бути 1–5 секунд;
у production не завжди варто одразу записувати кожен запит довший за 0 мс, оскільки це створює великий обсяг журналів.
Запис log_min_duration_statement = 0 означає журналювання всіх виконаних запитів. Значення -1 вимикає це журналювання.
Якщо запит довго не завершується, причина може бути не в самому плані виконання. Запит може чекати на блокування.
Поточні активні запити можна переглянути через pg_stat_activity:
SELECT
pid,
usename,
datname,
state,
wait_event_type,
wait_event,
clock_timestamp() - query_start AS duration,
query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;Важливі поля:
state — стан з’єднання;
query_start — час початку поточного запиту;
wait_event_type — категорія очікування;
wait_event — конкретна подія очікування;
duration — приблизна тривалість поточного запиту.
Наприклад, wait_event_type = 'Lock' може означати, що запит очікує завершення іншої транзакції.
pg_stat_statementsЖурнали показують окремі виконання, але для пошуку проблем часто важливіша агрегована статистика. Вона допомагає побачити запити, які:
мають найбільший загальний час виконання;
виконуються найдовше в середньому;
запускаються дуже часто;
читають багато блоків із диска або кешу.
Для цього використовується розширення pg_stat_statements.
Спочатку модуль потрібно додати до shared_preload_libraries. Це зазвичай робить адміністратор PostgreSQL:
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';Після цього сервер PostgreSQL потрібно перезапустити. Одного pg_reload_conf() недостатньо, оскільки shared_preload_libraries завантажуються під час запуску сервера.
У потрібній базі даних розширення створюється так:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;Статистика зберігається окремо для кожної бази даних, у якій доступне це розширення.
SELECT
calls,
total_exec_time,
mean_exec_time,
rows,
shared_blks_hit,
shared_blks_read,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;Значення:
calls — кількість виконань;
total_exec_time — сумарний час виконання в мілісекундах;
mean_exec_time — середній час одного виконання;
rows — сумарна кількість повернених рядків;
shared_blks_hit — кількість блоків, знайдених у кеші PostgreSQL;
shared_blks_read — кількість блоків, прочитаних із диска;
query — нормалізований текст запиту.
У різних версіях PostgreSQL набір доступних колонок може відрізнятися. Наприклад, у сучасних версіях використовуються total_exec_time і mean_exec_time.
Запит із найбільшим total_exec_time не обов’язково є найповільнішим одним виконанням. Він може просто запускатися дуже часто.
Розгляньмо три ситуації:
mean_exec_time великий, calls малий — окремий запит повільний;
mean_exec_time малий, calls дуже великий — запит недорогий, але його часте виконання створює значне навантаження;
shared_blks_read великий — запит часто читає дані з диска, тому варто перевірити план, індекси та обсяг оброблюваних даних.
Статистику можна обнулити, щоб виміряти новий проміжок часу:
SELECT pg_stat_statements_reset();Цю операцію слід виконувати обережно: вона видаляє накопичену статистику, тому перед цим варто зберегти потрібні результати.
EXPLAIN показує, як PostgreSQL планує виконувати запит:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;Приклад плану може містити вузли:
Seq Scan — послідовне читання таблиці;
Index Scan — пошук через індекс із читанням відповідних рядків;
Index Only Scan — читання даних лише з індексу;
Bitmap Index Scan і Bitmap Heap Scan — пошук багатьох рядків через bitmap;
Nested Loop — вкладені цикли для з’єднання таблиць;
Hash Join — з’єднання через хеш-таблицю;
Sort — сортування результату;
Aggregate — агрегація, наприклад COUNT або SUM.
Звичайний EXPLAIN лише будує план. Він не виконує запит:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;EXPLAIN ANALYZE фактично виконує запит і показує реальні вимірювання:
EXPLAIN (ANALYZE)
SELECT *
FROM orders
WHERE customer_id = 42;У результаті можна порівняти:
rows — кількість рядків, яку очікував планувальник;
actual rows — фактичну кількість рядків;
cost — внутрішню оцінку вартості;
actual time — фактичний час;
loops — кількість повторень вузла.
Параметр BUFFERS показує використання блоків:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;Він допомагає відрізнити роботу з кешем від читання з диска:
shared hit — блок знайдено в кеші;
shared read — блок прочитано з диска;
shared dirtied — блок змінено;
shared written — блок записано на диск.
Нижче наведено повний приклад, який можна виконати в тестовій базі даних.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
total_amount numeric(10, 2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (customer_id, status, total_amount, created_at)
SELECT
(random() * 10000)::integer + 1,
CASE
WHEN random() < 0.7 THEN 'paid'
WHEN random() < 0.9 THEN 'pending'
ELSE 'cancelled'
END,
round((random() * 1000)::numeric, 2),
now() - ((random() * 365)::integer || ' days')::interval
FROM generate_series(1, 200000);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount
FROM orders
WHERE customer_id = 42
AND status = 'paid';На цьому етапі PostgreSQL може вибрати Seq Scan, тобто послідовне читання всієї таблиці. Це не завжди помилка: якщо потрібно повернути велику частину таблиці, послідовне читання може бути дешевшим за індекс.
Створімо індекс, який відповідає умовам запиту:
CREATE INDEX orders_customer_status_idx
ON orders (customer_id, status);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount
FROM orders
WHERE customer_id = 42
AND status = 'paid';Після створення індексу планувальник може використати Bitmap Heap Scan, Index Scan або інший варіант. Конкретний план залежить від статистики, розміру таблиці та кількості відповідних рядків.
Важливо оцінювати не сам тип вузла, а фактичні показники:
чи зменшився actual time;
скільки блоків читається;
наскільки actual rows відрізняється від очікуваного rows;
чи не з’явилися додаткові дорогі операції.
Подивіться на actual time, кількість loops і кількість оброблених рядків. Вузол із великим часом не завжди очевидний: невеликий час, помножений на тисячі loops, може створити основну частину загальної затримки.
Наприклад:
rows=10 (actual rows=50000)Це означає, що планувальник очікував 10 рядків, але отримав 50 000. Така помилка може призвести до невдалого вибору індексу, типу з’єднання або порядку виконання операцій.
Причина часто пов’язана з неактуальною статистикою. Її можна оновити:
ANALYZE orders;Для всієї бази даних:
ANALYZE;Велика кількість shared read свідчить про читання з диска. Якщо запит часто виконується і щоразу читає багато блоків, потрібно перевірити фільтри, індекси та обсяг даних.
Вузол Sort може бути дорогим, якщо PostgreSQL не може виконати операцію в пам’яті. У плані можуть з’являтися ознаки використання тимчасових файлів, наприклад external merge.
Для діагностики можна додати параметр SETTINGS:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT *
FROM orders
ORDER BY created_at DESC;Не слід збільшувати work_mem без вимірювання: цей параметр може виділятися для багатьох операцій і паралельних з’єднань одночасно.
EXPLAIN ANALYZEEXPLAIN ANALYZE виконує запит. Для SELECT це зазвичай безпечно, але запити зі змінами даних справді змінюють таблиці.
Небезпечний приклад:
EXPLAIN ANALYZE
DELETE FROM orders
WHERE status = 'cancelled';Для перевірки такого запиту можна використати транзакцію та відкотити зміни:
BEGIN;
EXPLAIN ANALYZE
DELETE FROM orders
WHERE status = 'cancelled';
ROLLBACK;Однак навіть у транзакції запит може блокувати інші операції та створювати навантаження. Тому важкі запити краще аналізувати на тестовій копії або в контрольований час.
Практичний алгоритм можна звести до таких кроків:
З’ясувати, чи проблема стабільна або виникає час від часу.
Перевірити журнали та знайти запити, що перевищують прийнятний поріг.
Переглянути pg_stat_statements і визначити запити з найбільшим сумарним або середнім часом.
Перевірити pg_stat_activity, щоб виключити очікування блокувань.
Запустити EXPLAIN (ANALYZE, BUFFERS) для підозрілого запиту в безпечному середовищі.
Порівняти оцінені та фактичні рядки.
Перевірити індекси, статистику та обсяг читання.
Після зміни повторити вимірювання, а не покладатися лише на припущення.
total_exec_timeЗапит може мати найбільший сумарний час через дуже велику кількість викликів. Додатково перевіряйте mean_exec_time і calls.
Seq Scan є проблемоюПослідовне читання може бути оптимальним для невеликої таблиці або запиту, який повертає значну частину її рядків.
EXPLAIN ANALYZE для змін даних без транзакціїEXPLAIN ANALYZE не є лише аналізатором тексту — він виконує запит. Для INSERT, UPDATE і DELETE можна ненавмисно змінити дані.
Запит, який довго виконується, може більшу частину часу чекати на блокування. У такому випадку створення індексу не усуне основну причину затримки.
cost — це не мілісекунди, а внутрішня оцінка PostgreSQL. Для фактичної тривалості використовуйте actual time з EXPLAIN ANALYZE.
Після значних змін у таблиці планувальник може мати застарілу інформацію. Для оновлення статистики використовуйте ANALYZE.
Якщо одночасно створити кілька індексів, змінити пам’ять і переписати запит, буде складно зрозуміти, що саме усунуло проблему. Змінюйте один фактор і повторюйте вимірювання.
Журнали допомагають знаходити окремі повільні виконання через log_min_duration_statement.
pg_stat_activity показує поточні запити та можливі очікування блокувань.
pg_stat_statements накопичує статистику й допомагає знаходити запити з великим сумарним або середнім часом.
EXPLAIN показує план, а EXPLAIN (ANALYZE, BUFFERS) — фактичне виконання та використання блоків.
Порівнюйте очікувані й фактичні рядки, перевіряйте loops, actual time і читання блоків.
EXPLAIN ANALYZE виконує запит, тому для операцій зміни даних потрібна особлива обережність.
Оптимізацію слід підтверджувати повторним вимірюванням, а не лише виглядом плану.