Оптимізація запитів PostgreSQL: Плани виконання, покриваючі індекси та налаштування Autovacuum

Реляційні бази даних є основою більшості масштабованих архітектур сучасних додатків. У міру зростання навантаження затримки при читанні та записі в базу даних стають основним фактором уповільнення роботи інтерфейсу. У PostgreSQL запит, який виконується за 5 мілісекунд на 10,000 рядків, може легко вирости до 15 секунд, коли обсяг таблиці перевищить 50 мільйонів рядків.

Оптимізація таких запитів вимагає набагато більшого, ніж просто випадкового додавання індексів. Потрібно розуміти логіку роботи планувальника PostgreSQL, організацію даних на диску, алгоритми виконання операцій та вплив моделі багатоверсійності (MVCC) на читання даних. У цій статті ми проаналізуємо плани виконання запитів, навчимося проектувати складені та покриваючі індекси, розберемо алгоритми з'єднання таблиць, прискоримо підрахунок кількості рядків та налаштуємо параметри autovacuum.

---

1. Глибоке профілювання: Вчимося читати EXPLAIN (ANALYZE, BUFFERS)

Для оптимізації будь-якого повільного запиту необхідно спочатку вивчити план його виконання. За замовчуванням використання оператора EXPLAIN дає лише приблизну оцінку вартості запиту. Щоб отримати реальні дані про роботу бази, завжди використовуйте команду EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at 
FROM users 
WHERE company_id = 45 
ORDER BY created_at DESC 
LIMIT 50;

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

                      Дерево виконання плану запиту
                      
               +--------------------------------------+
               |          1. Limit (Реальний час)     |
               +--------------------------------------+
                                  |
               +--------------------------------------+
               |       2. Sort (Ключ: created_at)     |
               +--------------------------------------+
                                  |
               +--------------------------------------+
               |    3. Bitmap Heap Scan (company_id)   |
               +--------------------------------------+
                                  |
               +--------------------------------------+
               |   4. Bitmap Index Scan (idx_company) |
               +--------------------------------------+

Ключові операції у звіті планувальника:

• Seq Scan (Послідовне сканування): База даних виконує лінійний пошук, зчитуючи всі блоки таблиці з диска один за одним. На великих таблицях це найповільніша операція.
• Index Scan (Сканування за індексом): PostgreSQL спочатку читає індексне дерево для пошуку потрібних посилань, а потім звертається до відповідних сторінок таблиці (heap) на диску.
• Index Only Scan (Сканування тільки за індексом): Якщо всі колонки, зазначені в SELECT та WHERE, присутні в самому індексі, PostgreSQL взагалі не звертається до основної таблиці, читаючи дані безпосередньо з індексу. Це найшвидша операція читання.
• Shared Hit проти Shared Read:
  • Shared Hit: Блоки даних були зчитані з кешу оперативної пам'яті (shared buffers). Це працює дуже швидко.
  • Shared Read: Блоки даних довелося зчитувати безпосередньо з диска (найповільніша частина запиту).
  • Велика кількість Shared Read вказує на те, що база даних перевантажена операціями дискового введення-виведення.

Ваша мета при оптимізації - звести запити до операцій Index Only Scan з високим показником Shared Hit.

---

2. Складені та покриваючі індекси: Правило черговості колонок

Для ефективного пошуку індекси потрібно проектувати під конкретні типи запитів.

Правило черговості колонок у складених індексах

При створенні індексу на кілька колонок діє важливе правило: Колонки точної відповідності (equality) мають йти першими, колонки діапазонів (range) - другими.

Розглянемо запит:

SELECT id FROM orders WHERE user_id = 1054 AND created_at >= '2024-01-01';

Якщо створити індекс у такому порядку:

-- Неефективно: Пошук за датами завадить швидкому пошуку user_id
CREATE INDEX idx_orders_bad ON orders(created_at, user_id);

База спочатку шукатиме всі дати у вказаному діапазоні, а потім всередині них перебиратиме user_id. Натомість правильним буде такий варіант:

-- Ефективно: Точна рівність йде першою
CREATE INDEX idx_orders_good ON orders(user_id, created_at);

У цьому випадку PostgreSQL миттєво перейде до потрібної гілки user_id у B-Tree дереві та лінійно зчитає потрібний хронологічний діапазон дат.

Покриваючі індекси (Covering Indexes) та речення INCLUDE

Якщо вам потрібно отримати швидкість Index Only Scan, але ви не хочете перевантажувати індексне дерево зайвими ключами (що збільшує його розмір і сповільнює операції запису), скористайтеся реченням INCLUDE. Воно додає додаткові колонки тільки в кінцеві листи B-Tree дерева:

CREATE INDEX idx_users_company_covering 
ON users(company_id) 
INCLUDE(email, created_at);

Завдяки цьому запит SELECT email, created_at FROM users WHERE company_id = 45 буде виконано миттєво через Index Only Scan, оскільки всі необхідні дані знаходяться всередині самого індексу.

---

3. Внутрішні алгоритми JOIN: Nested Loops, Hash та Merge

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

А. Nested Loop (Вкладений цикл)

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

• Коли працює: Малі обсяги даних, де внутрішня таблиця має індекс на колонці з'єднання.
• Швидкість: $O(N \times M)$ без індексів, $O(N \log M)$ з індексами.

Б. Hash Join (Хеш-з'єднання)

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

• Коли працює: Великі несортовані таблиці без відповідних індексів.
• Швидкість: $O(N + M)$, але потребує багато оперативної пам'яті (work_mem). Якщо хеш-таблиця перевищує ліміт пам'яті, PostgreSQL почне писати тимчасові файли на повільний диск, що призведе до падіння швидкості.

В. Merge Join (З'єднання злиттям)

Обидві таблиці сортуються за ключем з'єднання, а потім скануються паралельно для знаходження відповідностей.

• Коли працює: Великі таблиці, які вже відсортовані або мають індекси на колонках з'єднання.
• Швидкість: $O(N \log N + M \log M)$ на сортування, або $O(N + M)$ якщо дані вже відсортовані за допомогою індексу.

Лайфхак для відладки: Якщо ви бачите у плані повільні Hash Join з використанням тимчасових файлів на диску, збільшіть ліміт оперативної пам'яті для поточної сесії:

-- Збільшити робочу пам'ять до 64 МБ для складних з'єднань
SET work_mem = '64MB';

---

4. Чому COUNT(*) повільний та як це виправити

У деяких базах даних підрахунок кількості рядків відбувається миттєво, бо вони зберігають загальне значення окремо. У PostgreSQL запит SELECT COUNT(*) FROM orders на мільйонах записів завжди триватиме довго.

Причина повільного рахунку

Через модель багатоверсійності MVCC кожна транзакція бачить свій набір рядків. База повинна фізично прочитати кожну сторінку таблиці з диска та перевірити видимість кожного рядка для поточної транзакції.

Рішення: Швидкий приблизний підрахунок

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

SELECT reltuples::bigint AS approximate_count 
FROM pg_class 
WHERE relname = 'orders';

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

---

5. Запобігання роздуванню бази (Bloat) та налаштування Autovacuum

PostgreSQL використовує модель MVCC для керування паралельними транзакціями. Коли ви оновлюєте (UPDATE) рядок, стара версія не видаляється фізично, а позначається як неактивна ("мертвий кортеж", або dead tuple). Нові дані вставляються поруч. Видалення запису (DELETE) також лише позначає його як мертвий.

Проблема роздування (Bloat)

Якщо ці мертві кортежі не прибирати, вони накопичуються в таблиці. При наступних запитах базі доведеться сканувати ці пусті мертві клітинки, витрачаючи ресурси диска.

Процес autovacuum працює у фоні та очищує ці мертві записи. Проте на високонавантажених проектах стандартні параметри autovacuum занадто консервативні, через що таблиці починають швидко роздуватися.

Тюнінг параметрів Autovacuum для таблиць

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

-- Запускати очищення при оновленні 10% рядків (за замовчуванням 20%)
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.10);

-- Запускати очищення після 500 змінених рядків
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 500);

-- Зменшити затримку між циклами очищення до 2 мілісекунд (за замовчуванням 20мс)
ALTER TABLE orders SET (autovacuum_vacuum_cost_delay = 2);

-- Дозволити autovacuum обробляти більше даних за один крок
ALTER TABLE orders SET (autovacuum_vacuum_cost_limit = 1000);

---

6. Оптимізація віконних функцій та усунення фази сортування

Аналітичні запити часто використовують віконні функції (ROW_NUMBER(), RANK(), SUM() OVER(...)) для обчислення рангів або накопичувальних підсумків. Проте вони можуть працювати повільно, оскільки вимагають від бази групувати та сортувати дані в оперативній пам'яті.

При аналізі запитів з віконними функціями у звіті EXPLAIN ANALYZE часто можна побачити важкі операції Sort або Incremental Sort:

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, amount,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as row_num
FROM orders;

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

Трюк з усунення сортування за допомогою індексів

Ви можете повністю прибрати важку фазу сортування, створивши складений індекс, який відповідає структурі блоків PARTITION BY та ORDER BY вашої віконної функції. Планувальник зчитуватиме дані з індексу вже у потрібному порядку, минаючи сортування.

Створіть складений індекс за таким шаблоном:

• Колонки PARTITION BY на першому місці
• Колонки ORDER BY на другому місці

-- Оптимізація для: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
CREATE INDEX idx_orders_window_opt ON orders(user_id, created_at DESC);

Після додавання цього індексу операція Sort у плані зникне - її замінить швидка операція WindowAgg, яка отримує дані безпосередньо з Index Scan. Це знижує навантаження на процесор та зменшує час виконання складних запитів до 10 разів на великих таблицях.

---

7. Підсумки та обслуговування індексів

Оптимізація бази даних - це не одноразова процедура, а безперервний процес моніторингу та профілювання. Окрім налаштування autovacuum, важливо пам'ятати, що самі індекси також схильні до накопичення сміття (index bloat) при частих операціях запису та видалення. Процес autovacuum очищує таблиці, але не завжди може ефективно повернути місце на диску всередині дерева B-Tree.

Для боротьби з цим рекомендується періодично (наприклад, під час технічних вікон раз на місяць) виконувати команду перебудови індексів:

-- Перебудувати всі індекси таблиці orders у фоновому режимі (без блокування читання та запису)
REINDEX TABLE CONCURRENTLY orders;

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