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

Навіть найсучасніша машина заглохне, якщо залити в неї замість бензину лимонад. Так само і з індексами в PostgreSQL. Вони — дуже потужний інструмент, але користуватись ним треба з розумом. Давайте подивимось на кілька типових проблем, пов’язаних з індексами.

Проблема 1: надмірне індексування

Для початку згадаємо тему з позапрошлої лекції. Коли у тебе забагато індексів на одну таблицю, PostgreSQL змушений працювати з кожним із них, щоб підтримувати їх актуальність. Це напряму впливає на операції запису, оновлення та видалення. Бо кожен індекс треба не тільки оновити, а ще й синхронізувати!

Уявімо, що у нас є таблиця students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    grade INTEGER
);

І ти вирішив створити індекси на кожен стовпець «про всяк випадок»:

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age ON students(age);
CREATE INDEX idx_students_grade ON students(grade);

Тепер уяви, що ти вставляєш 10 тисяч нових записів. PostgreSQL має не тільки записати дані в таблицю, а й оновити всі три індекси. Якщо даних багато, швидкість запису падає, а продуктивність системи страждає.

Як уникнути цієї проблеми? Перед створенням індексу задай собі два питання:

1. Як часто цей стовпець бере участь у фільтрації (WHERE), сортуванні (ORDER BY) чи групуванні (GROUP BY)?
2. Чи буде запит використовувати цей індекс, чи все одно зробить повне сканування таблиці?

Якщо відповідь на обидва питання — «рідко» або «ніколи», індекс тобі не потрібен.

Проблема 2: неправильний вибір стовпців для індексу

Створення індексу на маловаріативних даних — це як намагатись налити чай у чашку з уже закритою кришкою: результат буде майже марний. Якщо у стовпці всього 2-3 унікальних значення, PostgreSQL, скоріш за все, зробить повне сканування таблиці замість використання індексу.

Нехай у нас є таблиця courses:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    level VARCHAR(10) -- Може бути тільки 'Початковий', 'Середній' або 'Просунутий'
);

І ти вирішив створити індекс на стовпці level:

CREATE INDEX idx_courses_level ON courses(level);

Але запит:

SELECT * FROM courses WHERE level = 'Початковий';

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

Тому індекси мають сенс на стовпцях з високою кардинальністю (тобто з великою кількістю унікальних значень). Для маловаріативних даних краще використовувати інші механізми оптимізації, наприклад, партиціонування таблиць.

Проблема 3: застарілі індекси

Іноді індекси створюють, а потім забувають видалити, навіть якщо вони вже не використовуються. Це як файли на робочому столі: спочатку їх зовсім мало — два, три, п’ять. А потім раптом ловиш себе на тому, що витрачаєш дорогоцінний час, блукаючи поглядом від іконки до іконки в надії знайти потрібну... Знайомо?

Уявімо, ми створили індекс для старого функціоналу, а потім змінили логіку запитів і додали новий індекс. Старий індекс більше нікому не потрібен, але продовжує існувати, займати місце і гальмувати операції запису.

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

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Тут idx_scan показує, скільки запитів використало той чи інший індекс. Якщо значення дорівнює 0, значить індекс не використовується і його можна видалити:

DROP INDEX idx_courses_level;

Проблема 4: індекси на стовпцях з частими оновленнями

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

Уяви таблицю з даними про замовлення:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), -- Може змінюватись кілька разів (наприклад, "новий", "в процесі", "завершено")
    total NUMERIC(10, 2)
);

Ти створюєш індекс на стовпці status, щоб прискорити фільтрацію по статусах:

CREATE INDEX idx_orders_status ON orders(status);

Але якщо статус оновлюється десятки разів для кожного запису, то індекс починає псувати продуктивність.

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

CREATE INDEX idx_orders_status_partial
ON orders(status) 
WHERE status = 'в процесі';

Таким чином, індекс буде оновлюватись тільки для записів з вказаним значенням.

Проблема 5: Обмеження UNIQUE на непотрібних стовпцях

Унікальні індекси (UNIQUE) автоматично створюються для підтримки унікальності даних. Але якщо унікальність не є строго необхідною, ці індекси створюють зайве навантаження.

Уявімо, ми створили таблицю з логами:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP UNIQUE
);

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

Щоб все було ок, залишай обмеження UNIQUE тільки там, де це дійсно потрібно. У нашому прикладі, якщо унікальність по created_at не обов’язкова, заміни індекс на звичайний:

CREATE INDEX idx_logs_created_at ON logs(created_at);

Проблема 6: неправильне використання комбінованих індексів

Комбіновані індекси (multi-column indexes) корисні, якщо запити фільтруються чи сортуються одразу по кількох стовпцях. Але такі індекси треба створювати правильно, інакше вони залишаться невикористаними.

Уявімо, у нас є індекс:

CREATE INDEX idx_students_name_grade ON students(name, grade);

Цей індекс використовується, якщо запит фільтрує чи сортує по обох стовпцях:

SELECT * FROM students WHERE name = 'Аліса' AND grade = 90;

Але запит:

SELECT * FROM students WHERE grade = 90;

не використовує цей індекс, бо поле name йде першим.

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

Корисні поради

Моніторьте використання індексів. У PostgreSQL є системне представлення pg_stat_user_indexes, де можна побачити, які індекси використовуються, а які — ні.

Оптимізуйте запити разом з індексами. Погані запити залишаться поганими навіть з індексами.

Не забувайте про видалення. Застарілі індекси тільки займають місце і гальмують операції запису.

Ось і все, друзі! Індекси — це потужний інструмент, але завжди пам’ятайте: з великою силою приходить велика відповідальність. Використовуйте індекси свідомо, і ваша база даних буде працювати як ракета на базі SpaceX!