Пошук уроків, статей та іншого контенту
Пояснюємо, як працюють B-tree індекси та коли вони справді допомагають.
Без індексу PostgreSQL змушений виконувати повне сканування таблиці (sequential scan) — перевіряти кожен рядок по черзі, щоб знайти ті, що відповідають умові запиту. Для таблиці з десятком рядків це непомітно; для таблиці з мільйонами рядків — секунди на запит, який мав би виконуватись за мілісекунди.
Індекс — це окрема структура даних, яка зберігається поряд із таблицею й дозволяє знаходити потрібні рядки, не перебираючи всю таблицю. За замовчуванням PostgreSQL створює B-tree індекс — збалансоване дерево, у якому пошук, вставка й видалення виконуються за логарифмічний час незалежно від розміру таблиці.
CREATE INDEX idx_users_email ON users (email);Стовпці, які часто використовуються у WHERE, JOIN або ORDER BY.
Стовпці з високою кардинальністю (багато унікальних значень) — наприклад, email, а не boolean-прапорець зі значеннями лише true/false.
Зовнішні ключі — майже завжди варто індексувати, бо вони часто беруть участь у JOIN.
Індекс може охоплювати кілька стовпців одразу — такий індекс ефективний для запитів, що фільтрують за першими стовпцями індексу (у тому порядку, в якому їх перелічено при створенні), але марний для запитів, що фільтрують лише за пізнішими стовпцями:
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Використає індекс повністю:
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- Використає індекс частково (лише по user_id):
SELECT * FROM orders WHERE user_id = 42;
-- НЕ використає цей індекс:
SELECT * FROM orders WHERE status = 'paid';Практичне правило: у композитному індексі стовпець, за яким найчастіше фільтрують окремо (або за яким фільтрація відсікає найбільше рядків), зазвичай варто ставити першим.
B-tree покриває більшість практичних випадків, але PostgreSQL підтримує й інші типи індексів для спеціалізованих задач: GIN — для повнотекстового пошуку та стовпців-масивів/JSONB, де в одному рядку потрібно індексувати кілька значень; Hash — трохи швидший за B-tree, але підтримує лише перевірку на точну рівність (=), без сортування чи діапазонів. Для звичайних WHERE/JOIN/ORDER BY за скалярними значеннями B-tree за замовчуванням — правильний вибір у переважній більшості випадків.
Індекси не безкоштовні: кожен додатковий індекс сповільнює INSERT/UPDATE/DELETE, бо базі даних потрібно оновлювати й структуру кожного індексу на таблиці, а не лише сам рядок даних. Не додавайте індекси «про всяк випадок» — кожен новий індекс варто обґрунтувати конкретним повільним запитом.
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';EXPLAIN ANALYZE покаже реальний план виконання запиту разом із фактичним часом — якщо там Seq Scan замість Index Scan там, де ви очікували індекс, варто перевірити, чи індекс узагалі існує, чи умова запиту дозволяє його використати (наприклад, функція над стовпцем — WHERE lower(email) = ... — не використає звичайний індекс на email без окремого функціонального індексу), і чи статистика таблиці не застаріла (ANALYZE users; оновлює її вручну).
Індексувати кожен стовпець «про всяк випадок» — сповільнює всі операції запису й займає місце на диску без реальної користі, якщо стовпець не бере участі в частих запитах.
Неправильний порядок стовпців у композитному індексі — індекс (status, user_id) марний для запитів, що фільтрують лише за user_id, хоча логічно виглядає схожим на (user_id, status).
Індексувати стовпці з низькою кардинальністю (boolean-прапорці, статуси з 2-3 значеннями) — планувальник запитів часто ігнорує такий індекс і все одно обирає Seq Scan, бо відсіює замало рядків, щоб виправдати додаткові операції читання індексу.
Довіряти плану запиту «на око», а не перевіряти EXPLAIN ANALYZE — оптимізатор PostgreSQL іноді обирає Seq Scan навіть за наявності підходящого індексу, якщо статистика підказує, що умова відбере велику частку таблиці.
Індекс (найчастіше B-tree) дозволяє PostgreSQL знаходити рядки без повного сканування таблиці — критично для стовпців, які часто фігурують у WHERE/JOIN/ORDER BY та мають високу кардинальність. Композитні індекси ефективні лише для запитів, що фільтрують за їхніми першими стовпцями в заданому порядку. Кожен індекс сповільнює запис, тому додавайте їх обґрунтовано, а не «про всяк випадок» — і завжди перевіряйте реальний ефект через EXPLAIN ANALYZE, а не припущення.