Як обрати підходящий тип індексу

Ми вже занурились у теорію індексів, познайомились з їх видами, навчилися створювати та видаляти, а ще розібралися, як індексувати складні типи даних, типу масивів і JSONB. Тепер час поговорити про те, як вибрати саме той індекс, який буде реально ефективно працювати саме для твоїх задач — бо неправильний вибір може вилізти боком.

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

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

Сьогодні розберемо, як правильно підібрати індекс, щоб твої запити літали, а база не втомлювалась. Результат буде сумний: запити гальмують, ресурси з’їдаються, бібліотекар (PostgreSQL) сидить у депресії.

Критерії вибору індексу: чекліст

Коли обираєш індекс, задай собі кілька питань:

1. Який тип даних у тебе в цьому стовпчику?
   • Наприклад, числа INTEGER, FLOAT часто потребують індексу B-TREE, масиви — GIN, текстові поля — тут вже все залежить від задачі.

2. Які запити ти виконуєш найчастіше?

   • WHERE field = value? Прямий пошук? Тобі, скоріш за все, підійде B-TREE або HASH.
   • Пошук по масивах чи JSONB? Дивись у бік GIN.
   • Геодані, діапазони? Подумай про GiST.

3. Що відбувається з твоїми даними?

   • Якщо у тебе таблиця з частими вставками й оновленнями, уникай зайвого індексування, бо це збільшить накладні витрати.

4. Чи треба забезпечити унікальність?

   • У такому випадку доведеться використовувати індекс з атрибутом UNIQUE.

Кейси: реальні приклади вибору індексу

Давай подивимось на кілька реальних сценаріїв.

1. Простий пошук по рівності

Ти працюєш з базою студентів і хочеш швидко знаходити студента по його email:

SELECT * FROM students WHERE email = 'student@example.com';

Що тут важливо? Ми шукаємо по рівності. Кращим вибором буде B-TREE індекс, бо він чудово справляється з пошуком точних збігів.

CREATE INDEX idx_students_email ON students (email);

Або, якщо email має бути унікальним:

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. Пошук по діапазону

Тепер уяви, що ти хочеш знайти студентів старших за 18 років:

SELECT * FROM students WHERE age > 18;

Для діапазонного пошуку B-TREE теж ідеально підходить, бо його структура спеціально створена для пошуку по порядку.

CREATE INDEX idx_students_age ON students (age);

3. Фільтрація по масивах

У тебе є таблиця courses, де в одному зі стовпців зберігається масив з ID студентів, записаних на курс. Ти хочеш знайти всі курси, на які записаний студент з ID 123.

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

Для таких запитів ідеально підходить індекс GIN, бо він оптимізований для роботи з масивами.

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. Витяг даних з JSONB

Припустимо, у тебе є таблиця з JSONB-даними, де зберігається інформація про замовлення. Ти хочеш знайти всі замовлення, де клієнт з міста "Moscow":

SELECT * FROM orders WHERE data->>'city' = 'Moscow';

Тут підійде GIN індекс, який дозволяє ефективно шукати по ключах і значеннях JSONB.

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. Географічні дані

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

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

Порівняння продуктивності різних індексів

Візьмемо реальний приклад з пошуком студентів по email. У таблиці 1 мільйон записів. Спробуємо виконати запит з різними індексами і без індексу:

Висновок: у цьому випадку використання індексу B-TREE збільшує швидкість запиту більше ніж у 500 разів.

Помилки при виборі індексів

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

Ще одна типова помилка — вибір невідповідного типу індексу. Припустимо, ти використовуєш HASH індекс для діапазонного пошуку, і твої запити раптом стають дуже повільними. Все тому, що HASH індекс заточений тільки під точний пошук.

Рекомендації по вибору індексу

• Якщо ти часто робиш пошук по рівності або сортуванню, використовуй B-TREE.
• Для точних співпадінь, але з мінімальним споживанням пам’яті, можна використовувати HASH.
• Якщо працюєш з масивами чи JSONB, твій вибір — GIN.
• Для діапазонів або географічних даних застосовуй GiST.

І нарешті, головна порада: не забувай аналізувати свої запити! Використовуй команду EXPLAIN і EXPLAIN ANALYZE, щоб зрозуміти, як PostgreSQL використовує індекси і які покращення можна внести.

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

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