Senior: питання на співбесіді з теми «Продуктивність запитів»

Повільний запит - не лише той, що виконується 5 секунд один раз. Часто більше шкодить запит на 20 мс, який виконується мільйон разів на день. Тому дивляться на сумарний час.

PostgreSQL - pg_stat_statements. Розширення збирає статистику по кожному нормалізованому запиту (параметри замінено на $1): кількість викликів, сумарний і середній час, прочитані блоки.

-- shared_preload_libraries = 'pg_stat_statements' у postgresql.conf, потім:
CREATE EXTENSION pg_stat_statements;

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Доповнення:

• log_min_duration_statement = 500 - записувати в лог усі запити довші за 500 мс, з параметрами.
• auto_explain - автоматично логувати плани повільних запитів. Без цього план на проді, де дані й статистика інші, ніж локально, часто не відтворити.
• pg_stat_activity - що виконується просто зараз, хто кого чекає.

MySQL: slow query log (long_query_time), performance_schema і sys.statement_analysis - аналог pg_stat_statements.

На рівні застосунку APM (Sentry Performance, New Relic, Telescope локально) пов'язує запит з місцем у коді, яке його породило. Часто проблема - не сам запит, а N+1: сто швидких запитів там, де мав бути один.

Порядок дій: знайти топ за сумарним часом → EXPLAIN (ANALYZE, BUFFERS) на реальних параметрах → індекс, переписаний запит чи кеш → порівняти статистику після змін.

Докладніше в документації: pg_stat_statements

Кожне з'єднання з PostgreSQL - це окремий процес сервера з власною пам'яттю. Сотні з'єднань споживають багато ресурсів, а встановлення нового з'єднання помітно повільне. Тим часом PHP-FPM з 200 воркерами на кількох серверах легко відкриває тисячу з'єднань, більшість з яких простоює.

PgBouncer стоїть між застосунком і базою: тисячі клієнтських з'єднань він обслуговує невеликим пулом серверних.

Режими:

• Session pooling - серверне з'єднання видається клієнту на всю сесію. Безпечно, але виграш малий.
• Transaction pooling - з'єднання видається лише на час транзакції (або окремого запиту поза транзакцією). Головна економія - і головні сюрпризи.
• Statement pooling - на окремий запит; транзакції з кількох запитів неможливі.

Що ламається в transaction pooling: усе, що прив'язане до сесії, бо наступний запит може піти іншим серверним з'єднанням:

• SET (часовий пояс, search_path, statement_timeout) без LOCAL - налаштування «перетече» до іншого клієнта.
• Сесійні advisory-блокування, LISTEN/NOTIFY, тимчасові таблиці.
• Підготовлені запити (prepared statements) - PgBouncer підтримує їх на рівні протоколу лише з версії 1.21 і з налаштуванням max_prepared_statements. До того PDO з емуляцією вимкненою отримував помилки на кшталт prepared statement does not exist.

Альтернативи й доповнення: керовані пули (RDS Proxy, Supavisor у Supabase), довгоживучі процеси (Octane), що тримають сталі з'єднання, і просто обмеження кількості PHP-воркерів під реальну ємність бази.

Докладніше в документації: Можливості PgBouncer

За замовчуванням один запит виконується одним процесом на одному ядрі. Паралельний запит ділить роботу між кількома процесами-воркерами: кожен обробляє частину таблиці, а головний процес збирає результати.

Finalize Aggregate
  -> Gather  (Workers Planned: 2, Workers Launched: 2)
       -> Partial Aggregate
            -> Parallel Seq Scan on orders

Що вміє працювати паралельно: послідовне й індексне сканування, Hash Join і Nested Loop, агрегати, сортування з об'єднанням (Gather Merge), створення B-tree індексів.

Ключові налаштування:

• max_parallel_workers_per_gather (за замовчуванням 2) - воркерів на один вузол запиту;
• max_parallel_workers і max_worker_processes - загальні ліміти на сервер;
• min_parallel_table_scan_size - менші таблиці не розпаралелюються;
• parallel_setup_cost, parallel_tuple_cost - вартість запуску й передачі рядків, з якою планувальник порівнює виграш.

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

Коли не допомагає чи шкодить:

• OLTP-запити (знайти користувача за ID) - запуск воркерів коштує більше, ніж сам запит; планувальник їх і не розпаралелить.
• Високе паралельне навантаження: якщо сервер і так зайнятий сотнями запитів, воркери конкурують з ними за ядра. Сумарна пропускна здатність може впасти.
• Запити, що змінюють дані (UPDATE, DELETE), майже не розпаралелюються, а INSERT ... SELECT - лише частково.
• Функції, позначені PARALLEL UNSAFE (за замовчуванням для власних функцій), забороняють паралельний план.

Як діагностувати: Workers Planned проти Workers Launched у EXPLAIN ANALYZE - якщо запущено менше запланованого, на сервері забракло вільних воркерів.

Докладніше в документації: Паралельні запити

Підготовлений запит (prepared statement) розбирається один раз, а виконується багато разів з різними параметрами. PDO і Laravel використовують їх для прив'язки параметрів.

PREPARE find_orders (text) AS SELECT * FROM orders WHERE status = $1;
EXECUTE find_orders('pending');

Для виконання PostgreSQL може використати два види плану:

• Custom plan - будується для конкретних значень параметрів. Точний, але планування на кожне виконання коштує час.
• Generic plan - один план для будь-яких значень, без урахування конкретних. Будується раз і перевикористовується.

Як PostgreSQL обирає (plan_cache_mode = auto): перші п'ять виконань - custom-плани. Потім він порівнює їхню середню вартість з вартістю generic-плану і, якщо generic не набагато гірший, переходить на нього.

Де це ламається - перекошені дані:

-- status = 'pending'   - 0.1% рядків → ідеальний Index Scan
-- status = 'completed' - 95% рядків → потрібен Seq Scan

Generic-план не знає, яке значення прийде, і обирає «середній» варіант. Запит, що спершу виконувався за мілісекунди, після п'ятого разу раптом стає повільним для частини значень. Класична загадка «запит повільний лише в застосунку, а в psql - швидкий» (у psql ви виконуєте його з конкретним значенням - custom план).

Що робити:

SET plan_cache_mode = force_custom_plan;   -- для сесії чи ролі

Або точково - для конкретних запитів з перекошеними даними.

Як діагностувати: EXPLAIN EXECUTE find_orders('completed') після шостого виконання показує, чи план уже generic (параметри в ньому виглядають як $1); auto_explain у логах продакшену.

Нюанс PHP: PDO з PDO::ATTR_EMULATE_PREPARES = true підставляє параметри сам і надсилає готовий текст - тоді на сервері підготовлених запитів немає взагалі, і проблеми generic-планів теж. Laravel за замовчуванням для PostgreSQL використовує справжні підготовлені запити.

Докладніше в документації: PREPARE

JIT (з PostgreSQL 11, увімкнено за замовчуванням з 12) компілює частини виконання запиту - обчислення виразів у WHERE, агрегатів, розбір рядків - у машинний код через LLVM. Замість інтерпретації виразу для кожного рядка виконується скомпільований код.

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

Як вирішується, чи вмикати: за оцінною вартістю запиту.

• jit_above_cost (100 000) - вмикати JIT для запитів, дорожчих за це значення;
• jit_inline_above_cost, jit_optimize_above_cost (500 000) - вмикати вбудовування й агресивну оптимізацію.

Чому JIT часто вимикають на OLTP-серверах:

• Компіляція займає час - десятки чи сотні мілісекунд. Для запиту, що сам виконується за 50 мс, JIT робить його втричі повільнішим.
• Помилкові оцінки: якщо планувальник переоцінив кількість рядків (застаріла статистика, складні умови), вартість перевищує поріг, і JIT вмикається для запиту, якому він не потрібен. Так з'являються «дивні» запити, що іноді виконуються на 200 мс довше без видимої причини.
• Компіляція на кожне виконання: скомпільований код не кешується між запитами.

Як побачити в плані:

JIT:
  Functions: 18
  Timing: Generation 2.1 ms, Inlining 45.3 ms, Optimization 98.7 ms, Emission 60.2 ms, Total 206.3 ms
Execution Time: 248.9 ms

Тут на JIT пішло понад 80% часу запиту.

Що роблять:

ALTER SYSTEM SET jit = off;                -- для типового веб-застосунку
ALTER ROLE analytics SET jit = on;         -- лишити для аналітики

або піднімають пороги jit_above_cost, щоб JIT вмикався лише для справді важких запитів.

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

Докладніше в документації: Що таке JIT у PostgreSQL