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

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

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 20;

Limit  (actual time=812.4..812.5 rows=20 loops=1)
  ->  Sort  (actual time=812.4..812.4 rows=20 loops=1)
        Sort Method: top-N heapsort
        ->  Seq Scan on orders  (rows=1200 ... actual rows=950000 loops=1)
              Filter: (status = 'pending')
              Rows Removed by Filter: 4050000
Buffers: shared read=73000

На що дивитися:

• Оцінка проти факту: rows=1200 очікувалося, actual rows=950000 вийшло. Велика розбіжність означає застарілу статистику (ANALYZE orders) або корельовані колонки - і планувальник обирає поганий план.
• Rows Removed by Filter - база прочитала мільйони рядків, щоб відкинути більшість. Підказка до індексу.
• loops - скільки разів виконувався вузол. Час у вузлі вказано за один прохід: 0.5 мс × 20 000 loops у Nested Loop - це 10 секунд.
• BUFFERS - скільки сторінок прочитано з кешу (hit) і з диска (read).

Обережно: EXPLAIN ANALYZE справді виконує запит. Для UPDATE/DELETE загортайте його в транзакцію з ROLLBACK.

MySQL має EXPLAIN ANALYZE з версії 8.0.18 - вивід у вигляді дерева з фактичним часом.

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

LIMIT 20 OFFSET 100000 не «перескакує» на потрібне місце. База мусить знайти й відкинути перші 100 000 рядків і лише потім віддати 20. Чим далі сторінка, тим довший запит - навіть з індексом.

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

Keyset (cursor) пагінація - замість номера сторінки запам'ятовують значення останнього рядка й продовжують від нього:

-- Перша сторінка
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Наступна: від останнього показаного рядка
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ('2026-10-01 12:00:00', 4821)
ORDER BY created_at DESC, id DESC
LIMIT 20;

З індексом (created_at, id) кожна сторінка - кілька кроків по індексу, хоч перша, хоч мільйонна. id додано для однозначності, коли created_at однаковий.

Обмеження keyset: немає переходу на довільну сторінку «47» і загальної кількості сторінок. Для стрічок, нескінченного скролу, API й експорту це не проблема.

У Laravel: cursorPaginate() реалізує саме keyset, paginate() - OFFSET плюс окремий COUNT(*), simplePaginate() - OFFSET без підрахунку.

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

PostgreSQL має три алгоритми з'єднання таблиць, і планувальник обирає один для кожного JOIN за оцінками кількості рядків.

Nested Loop - для кожного рядка зовнішньої таблиці шукати відповідні рядки у внутрішній.

• Чудовий, коли зовнішніх рядків мало, а у внутрішній таблиці є індекс на ключі з'єднання: 20 замовлень → 20 пошуків по індексу користувачів.
• Катастрофічний, коли зовнішніх рядків багато: мільйон × пошук = мільйон звернень.

Hash Join - побудувати хеш-таблицю з меншого набору, потім пройти більший і шукати збіги в хеш-таблиці.

• Для великих наборів без потрібного порядку - типовий вибір для аналітики й звітів.
• Лише для умов рівності (=).
• Хеш-таблиця будується в пам'яті (work_mem × hash_mem_multiplier); якщо не вміщується - ділиться на частини на диску, і з'єднання сповільнюється.

Merge Join - обидва набори впорядковані за ключем з'єднання, і їх «зшивають» за один прохід, як дві відсортовані колоди.

• Ефективний, коли обидва набори вже відсортовані (наприклад, читаються з індексів) або результат усе одно потрібен відсортованим.
• Якщо сортування треба робити окремо, часто програє Hash Join.

Як читати в EXPLAIN ANALYZE:

Nested Loop  (actual rows=950000 loops=1)
  -> Seq Scan on orders (actual rows=950000)
  -> Index Scan using users_pkey on users (actual rows=1 loops=950000)

loops=950000 - сигнал: планувальник очікував кілька рядків зовні, а отримав майже мільйон. Майже завжди причина - помилкова оцінка кількості рядків (застаріла статистика, корельовані умови), а не сам алгоритм.

Виправляють не алгоритм, а оцінки: ANALYZE, розширена статистика, переписаний запит. Для перевірки гіпотези в сесії можна вимкнути алгоритм (SET enable_nestloop = off), але на проді так не залишають.

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

work_mem - скільки пам'яті може використати одна операція запиту: сортування (ORDER BY, DISTINCT, Merge Join), хеш-таблиця (Hash Join, GROUP BY через хеш). За замовчуванням - 4 МБ.

Якщо даних більше, операція переходить на тимчасові файли на диску - на порядки повільніше.

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

Sort Method: external merge  Disk: 285440kB     -- сортування на диску
Sort Method: quicksort  Memory: 3120kB          -- у пам'яті
Hash ... Batches: 16  Memory Usage: 4096kB      -- хеш поділено на частини через нестачу пам'яті

Ще - лог тимчасових файлів (log_temp_files = 0) і temp_files/temp_bytes у pg_stat_database.

Чому не поставити 1 ГБ - пастка множення. Ліміт діє на кожну операцію, а не на з'єднання:

• один запит може мати кілька сортувань і хешів одночасно;
• паралельний запит множить на кількість воркерів;
• хеш-операції можуть брати work_mem × hash_mem_multiplier (за замовчуванням ×2);
• і все це - на кожне з сотень з'єднань.

200 з'єднань × 3 операції × 256 МБ = 150 ГБ потенційно - сервер піде в OOM під навантаженням.

Як налаштовувати:

• Глобально - помірне значення (16-64 МБ залежно від пам'яті й кількості з'єднань).
• Точково більше - для важких звітів і аналітики:

SET LOCAL work_mem = '512MB';   -- лише в поточній транзакції
ALTER ROLE reporting SET work_mem = '256MB';

• Для обслуговування (CREATE INDEX, VACUUM) - окремий параметр maintenance_work_mem.

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

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

shared_buffers - власний кеш сторінок даних PostgreSQL у спільній пам'яті. Кожне читання спершу шукає сторінку тут; якщо її немає - читає з файлу.

Подвійне кешування. PostgreSQL читає файли через звичайну файлову систему, тож сторінки, яких немає в shared_buffers, часто вже лежать у кеші ОС (page cache). Тому:

• shared_buffers не має забирати всю пам'ять - решту ефективно використовує ОС;
• типова рекомендація документації - близько 25% оперативної пам'яті на виділеному сервері бази (більше дає менший виграш, бо дублюється з кешем ОС);
• effective_cache_size - підказка планувальнику, скільки кешу всього (shared_buffers + кеш ОС), зазвичай 50-75% пам'яті. Пам'ять він не виділяє, лише робить індексні плани «дешевшими» в оцінках.

# сервер з 32 ГБ, лише під PostgreSQL
shared_buffers = 8GB
effective_cache_size = 24GB

Значення за замовчуванням - лише 128 МБ - розраховане на запуск будь-де, а не на продуктивність. На сервері для продакшену його майже завжди треба змінити (і перезапустити PostgreSQL).

Як оцінити, чи вистачає кешу:

SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_percent
FROM pg_stat_database
WHERE datname = current_database();

Для OLTP-навантаження зазвичай очікують 99%+. Але blks_read рахує читання повз shared_buffers, а ці сторінки могли прийти з кешу ОС - низький відсоток не завжди означає читання з диска. Точніше - EXPLAIN (ANALYZE, BUFFERS) для конкретних запитів і метрики вводу-виводу ОС.

Ще пов'язані параметри: huge_pages (для великих shared_buffers зменшує накладні витрати пам'яті), wal_buffers. У керованих базах (RDS, Cloud SQL) розумні значення вже виставлені під розмір інстанса.

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

Найповільніший спосіб - окремий INSERT на кожен рядок в autocommit: кожен рядок - окрема транзакція з записом у WAL і синхронізацією з диском.

Від швидкого до найшвидшого:

1. Одна транзакція і масові INSERT:

BEGIN;
INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', now()), (2, 'login', now()), /* ...тисяча рядків... */;
-- ще порції
COMMIT;

Порції по 500-5000 рядків в одному INSERT - у десятки разів швидше за поодинокі. У Laravel - DB::table('events')->insert($batch).

2. COPY - спеціальний протокол масового завантаження, найшвидший спосіб:

COPY events (user_id, type, created_at) FROM '/data/events.csv' WITH (FORMAT csv, HEADER true);

COPY ... FROM читає файл на сервері бази; з клієнта - \copy у psql чи Pdo\Pgsql::copyFromFile() / copyFromArray() у PHP 8.4+ (раніше - методи pgsqlCopyFrom* на самому PDO).

3. Для величезних одноразових завантажень:

• Створити індекси й зовнішні ключі після завантаження - побудувати індекс один раз швидше, ніж оновлювати його мільйон разів.
• Збільшити maintenance_work_mem для швидшої побудови індексів.
• UNLOGGED-таблиця для проміжних даних - без запису в WAL, але не переживає збій і не реплікується.
• Тимчасово збільшити max_wal_size, щоб рідше робилися контрольні точки.

Після завантаження - обов'язково ANALYZE, інакше планувальник працюватиме зі статистикою порожньої таблиці. А для таблиць, куди більше не писатимуть, - VACUUM (FREEZE, ANALYZE), щоб пізніше не було масового «заморожування».

Обережно на проді: масове завантаження генерує багато WAL - репліки відстають, а сервер з архівуванням WAL заповнює сховище. Великі імпорти ділять на порції з паузами й стежать за затримкою реплікації.

Докладніше в документації: Наповнення бази

Звичайне подання (view) - збережений запит: щоразу при зверненні він виконується заново.

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

CREATE MATERIALIZED VIEW sales_by_month AS
SELECT date_trunc('month', created_at) AS month,
       product_id,
       sum(total) AS revenue,
       count(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY 1, 2;

CREATE UNIQUE INDEX sales_by_month_key ON sales_by_month (month, product_id);

Оновлення:

REFRESH MATERIALIZED VIEW sales_by_month;                -- блокує читання на весь час оновлення
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_by_month;   -- читання не блокується

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

Коли доречно:

• Важкі агрегати для дашбордів і звітів, де допустимі дані «станом на кілька хвилин чи годин тому».
• Денормалізовані дані для пошуку чи API, які дорого збирати з багатьох таблиць на кожен запит.

Обмеження:

• Немає автоматичного оновлення - лише REFRESH за розкладом (cron, планувальник Laravel) чи за подією.
• Оновлюється цілком: навіть при зміні одного рядка запит перераховується повністю. Для дуже великих агрегатів - інкрементальні підходи (окрема таблиця з оновленням за тригером чи пакетними задачами, розширення на кшталт pg_ivm).
• Займає місце, як звичайна таблиця, і потребує індексів під свої запити.

Альтернатива на рівні застосунку - кеш результату запиту (Redis). Матеріалізоване подання краще, коли результат великий, його фільтрують і з'єднують з іншими таблицями прямо в SQL.

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