Вступ до аналізу продуктивності запитів

Давай почнемо з реальної історії. Уяви, що ти працюєш розробником на великій платформі інтернет-магазину. Все працює ідеально, окрім однієї дрібниці: звіт про місячні продажі вантажиться так повільно, що твої користувачі встигають зробити ще одне замовлення, поки він завершиться. Ти нервуєш, твій менеджер нервує, сервер PostgreSQL закипає, як чайник, і все це трохи нагадує апокаліпсис. А тепер уяви, що ти міг би одним махом знайти причину і прибрати її.

Аналіз продуктивності запитів — це як медичне обстеження твого PostgreSQL. Він допомагає знаходити «вузькі місця» (болі) і виправляти їх для покращення досвіду користувачів і економії системних ресурсів.

Як запити працюють під капотом PostgreSQL?

Коли ти пишеш простий запит типу:

SELECT * FROM products WHERE price > 100;

PostgreSQL не кидається одразу витягати дані. Він спочатку аналізує твій запит, вигадує, як його краще виконати, і тільки потім береться до справи.

Основні етапи виконання запитів:

1. Парсинг. PostgreSQL перевіряє твій запит на синтаксичні помилки і перетворює його у проміжне представлення.
2. Оптимізація. Оптимізатор запитів оцінює кілька варіантів виконання запиту і вибирає найдешевший (за часом і ресурсами).
3. Виконання. Сервер діє згідно з обраним планом, витягаючи дані.

Що таке «вузьке місце»?

«Вузьке місце» — це частина запиту, яка гальмує все інше. Це може бути операція, яка несподівано з'їдає найбільше часу або ресурсів. Наприклад, якщо PostgreSQL замість швидкого звернення по індексу робить повне сканування таблиці (Seq Scan), запит сповільнюється. Або якщо даних виявилося набагато більше, ніж планувалося, і сервер витрачає купу часу на сортування, об'єднання, фільтрацію.

Такі моменти і називаються вузькими місцями — саме їх треба шукати і оптимізувати в першу чергу.

Інструменти аналізу продуктивності запитів

У PostgreSQL є кілька потужних інструментів, які допоможуть тобі ідентифікувати проблеми у запитах:

1. EXPLAIN і EXPLAIN ANALYZE. Ці команди показують тобі, як PostgreSQL збирається виконувати запит, або навіть виконують його, щоб виміряти реальну продуктивність.

• EXPLAIN: відображає план виконання запиту без реального його виконання.
• EXPLAIN ANALYZE: виконує запит і показує реальний план виконання з фактичними часовими метриками.

Приклад використання EXPLAIN:

EXPLAIN SELECT * FROM products WHERE price > 100;

Вивід:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Тут показано, що запит виконує «Seq Scan» — повне сканування таблиці, що неефективно для великих таблиць.

2. pg_stat_statements. Це додаткове розширення, яке веде облік виконуваних запитів. Воно показує:

• Які запити виконуються на сервері.
• Скільки часу витрачається на кожен запит.
• Скільки рядків повертає запит і скільки ресурсів споживає.

Щоб увімкнути pg_stat_statements, треба:

1. Увімкнути розширення:

CREATE EXTENSION pg_stat_statements;

2. Налаштувати конфігурацію PostgreSQL: У файлі postgresql.conf додай:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. Перезапусти PostgreSQL.

Тепер ти можеш аналізувати запити:

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Це покаже 5 найважчих запитів у порядку спадання загального часу виконання.

3. Логування повільних запитів. Ти можеш налаштувати PostgreSQL на запис запитів, які виконуються занадто довго (наприклад, більше 1 секунди), у лог-файл.

Для цього у postgresql.conf встанови:

log_min_duration_statement = 1000  # Час у мілісекундах (1 секунда)

Тепер повільні запити будуть зберігатися у логи PostgreSQL.

Основні метрики для аналізу продуктивності

Коли ти аналізуєш продуктивність запитів, звертай увагу на такі ключові метрики:

1. Час виконання. Основний показник часу, витраченого на виконання запиту. Чим швидше, тим краще.
2. Кількість рядків. Якщо твій запит повертає або сканує більше рядків, ніж ти очікував, це може бути проблемою.
3. Використання індексів. Якщо запит має використовувати індекс, а замість цього виконує послідовне сканування (Seq Scan), це сигнал для оптимізації.
4. Буфери і дискові операції. Запити, які активно взаємодіють з диском, працюють повільніше, ніж ті, що використовують дані з пам'яті.

Як ці знання застосовуються на практиці?

Приклад 1: Повільний запит

Ти пишеш запит для вибірки всіх товарів дорожче 100:

SELECT * FROM products WHERE price > 100;

Ти помічаєш, що запит виконується занадто довго. Використовуєш EXPLAIN і бачиш:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

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

Рішення:

Створи індекс:

CREATE INDEX idx_price ON products(price);

Тепер запит використовує Index Scan:

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

Приклад 2: Виявлення повільних запитів з pg_stat_statements

За допомогою команди:

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

Ти знаходиш запит, який витрачає багато часу на виконання. Ти відкриваєш його через EXPLAIN ANALYZE, виправляєш, і він стає швидшим.

Коли ти почнеш використовувати EXPLAIN, pg_stat_statements та інші інструменти, твої запити почнуть працювати швидше, а твій PostgreSQL-сервер буде працювати як швейцарський годинник. У наступній лекції ми зануримося у деталі параметрів EXPLAIN, таких як cost, rows і width, щоб почати читати плани виконання запиту, як відкриту книгу.