Аналіз повільних запитів з pg_stat_statements

Коли ти працюєш з реальними проєктами, з твоїм додатком одночасно можуть взаємодіяти тисячі користувачів. Вони шлють запити до бази, додають дані, читають їх, оновлюють... І тут ти помічаєш, що твій сервер починає "стогнати". Це сигнал, що твої запити далеко не ідеальні. Іноді запит, який "на папері" виглядав круто, на ділі може стати катастрофою для продуктивності. Ось тут і з'являється pg_stat_statements.

pg_stat_statements дозволяє тобі:

1. Відслідковувати повільні запити.
2. Розуміти, скільки разів виконувались ті чи інші запити.
3. Дізнаватись, скільки часу вони займали.
4. Бачити середній час виконання запиту.
5. Не зробити фатальну помилку і не переписати все додаток!

Вивчаємо структуру pg_stat_statements

Після активації розширення у твоїй базі з'являється спеціальний в'ю pg_stat_statements. Тут зберігаються всі дані про виконані запити. Для початку розберемось, що там є:

SELECT * FROM pg_stat_statements LIMIT 1;

Результат може виглядати так (спрощена версія):

Короткі пояснення:

• query — сам SQL-запит.
• calls — скільки разів виконувався цей запит.
• total_exec_time — скільки часу, загалом, витратив запит.
• rows — кількість рядків, які повернув запит.
• shared_blks_read — кількість прочитаних блоків (звертаємось до диску, якщо не використовуєш кеш).

Аналіз результатів

Тепер, коли pg_stat_statements увімкнено, давай подивимось, як знайти повільні запити.

Найповільніші запити

Щоб знайти, які запити витрачають найбільше часу, можна використати такий запит:

SELECT query, total_exec_time, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Тут:

• mean_exec_time — це середній час виконання одного запиту (total_exec_time / calls).
• ORDER BY total_exec_time DESC — сортуємо по загальному часу виконання.

Часто виконувані запити

Іноді проблема не у повільних запитах, а у тих, що виконуються занадто часто. Наприклад:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Оптимізація запитів

1. Використовуй індексацію

Якщо бачиш, що запити по певних стовпцях виконуються повільно, перевір, чи є індекс для цих стовпців. Наприклад, у тебе є таблиця students з великою кількістю рядків, і ти часто звертаєшся до поля last_name. Варто створити індекс:

CREATE INDEX idx_students_last_name ON students (last_name);

2. Перепиши запит

Припустимо, ти бачиш, що запит типу SELECT * FROM orders WHERE amount > 1000 займає забагато часу. Скоріш за все, замість "все про все" треба вибирати тільки потрібні стовпці:

SELECT order_id, amount FROM orders WHERE amount > 1000;

Очищення статистики

Іноді, щоб побачити тільки нові результати (наприклад, після оптимізації), треба очистити дані у pg_stat_statements. Це робиться командою:

SELECT pg_stat_statements_reset();

Працює як кнопка "Скинути" у твоєму калькуляторі. Після виконання статистика буде збиратись заново.

Пошук проблемних запитів

Уяви, що ти адміністратор бази для універу, і студенти масово скаржаться, що їх особистий кабінет завантажується дуже довго. Ти вирішуєш перевірити pg_stat_statements:

Крок 1: Пошук найповільніших запитів

SELECT query, total_exec_time, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 1;

Ти бачиш, що запит типу SELECT * FROM students WHERE status = 'active' займає 30 секунд. Вау. Треба щось робити.

Крок 2: Перевірка індексації Проаналізувавши таблицю students, ти розумієш, що у стовпця status немає індексу. Ти це виправляєш:

CREATE INDEX idx_students_status ON students (status);

Крок 3: Перевірка результату Після оптимізації ти знову перевіряєш pg_stat_statements і бачиш, що запит тепер виконується за 0.5 секунди. Перемога!

Часті помилки при використанні pg_stat_statements

Іноді адміністратори роблять помилки при аналізі запитів:

1. Неактивоване розширення. Якщо ти забув увімкнути pg_stat_statements у shared_preload_libraries, статистика просто не збереться.
2. Ігнорування індексації. Навіть якщо запити виглядають повільно, проблема може вирішитись додаванням правильних індексів.
3. Відсутність скидання статистики. Якщо ти не виконуєш pg_stat_statements_reset(), старі дані заважають аналізувати поточні.

Використання pg_stat_statements у твоїй роботі — це як GPS-навігатор для бази: він точно каже, де ти застряг у "заторі", і навіть підказує, як об'їхати. Правильно налаштувавши цей інструмент, ти зможеш серйозно прокачати продуктивність своїх баз.