Основні види індексів: B-TREE, HASH, GIN, GiST

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

У PostgreSQL основні види індексів включають:

• B-TREE індекси: універсальні індекси для більшості задач.
• HASH індекси: оптимізовані для точного порівняння.
• GIN індекси: ідеальні для пошуку по масивах і JSONB.
• GiST індекси: використовуються для складних типів даних, наприклад, географічних.

Індекси створені для прискорення пошуку рядків. Вони мають 4 різних типи оптимізації: кожен тип прискорює певні дії і краще працює з певними типами даних.

Ти ніяк не можеш керувати індексами. Все, що ти можеш — це просто вибрати тип індексу: ніякого або один із вищеперелічених. Зараз розберемо кожен із них, щоб зрозуміти, коли і як їх юзати.

Індекси B-TREE

B-TREE (скорочення від "balanced tree") — це найпоширеніший тип індексу, основа роботи PostgreSQL. Цей індекс створює деревоподібну структуру, де дані організовані так, щоб пришвидшити пошук, сортування і фільтрацію.

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

Коли їх використовувати?

Та майже завжди! B-TREE індекси особливо корисні для:

• Пошуку по діапазонах: WHERE price > 100.
• Сортування: ORDER BY name ASC.
• Пошуку по рівності: WHERE id = 42.

Ось приклад створення:

-- Створюємо індекс B-TREE для колонки price таблиці products:
CREATE INDEX idx_price ON products(price);

Коли у запиті ти напишеш щось типу WHERE price > 100, PostgreSQL зможе звернутись до цього індексу і не буде сканувати всю таблицю.

Індекси HASH

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

Це як картотека, де кожна картка має чіткий номер. Ти шукаєш картку номер 42, і бібліотекар одразу її знаходить. Але якщо ти скажеш: «покажи мені картки з номерами від 40 до 50», тобі відмовлять.

HASH індекси підходять тільки для точного пошуку:

• WHERE email = 'user@example.com'.
• SELECT ... WHERE id = 123.

Якщо тобі потрібні діапазони чи сортування, то HASH не підійде.

Приклад створення:

-- Створюємо hash-індекс для колонки email таблиці users:
CREATE INDEX idx_email_hash ON users USING HASH (email);

Тепер PostgreSQL буде використовувати цей індекс для запитів типу WHERE email = 'user@example.com'.

Зверни увагу: HASH індекси підходять для специфічних випадків і використовуються рідше, ніж B-TREE.

Індекси GIN (Generalized Inverted Index)

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

GIN індекси потрібні для:

• Пошуку по масивах: @> (містить), <@ (міститься).
• JSONB даних: WHERE jsonb_data @> '{"key": "value"}'.

Приклад створення

-- Створюємо GIN-індекс для колонки tags, яка містить масиви:
CREATE INDEX idx_tags_gin ON products USING GIN (tags);

Тепер PostgreSQL зможе ефективно знаходити продукти, де тегами є, наприклад, "електроніка" і "рекомендовані".

Індекси GiST (Generalized Search Tree)

GiST індекси — це потужний інструмент для роботи зі складнішими типами даних, включаючи географічні координати і діапазони. Вони будують дерева, які оптимізовані для просторового пошуку і пошуку по діапазонах.

Уяви собі карту міста, де кожна точка відмічена за її координатами. Ти можеш швидко знайти всі точки в радіусі 5 км від поточної.

GiST підходять для:

• Географічних даних: SELECT ... FROM locations WHERE ST_DWithin(geom, point, distance).
• Пошуку по діапазонах: WHERE date_range && '[2023-01-01, 2023-12-31]'.

Приклад:

-- Створюємо GiST-індекс для колонки location, яка містить геодані:
CREATE INDEX idx_location_gist ON places USING GiST (location);

Тепер можна виконувати складні географічні запити, наприклад, пошук найближчих точок.

Таблиця порівняння індексів

Тепер ти знайомий з основними типами індексів у PostgreSQL, а також їх використанням. Запам’ятай: вибір індексу — це стратегічний крок, від якого залежить швидкість твоїх запитів. Шах і мат, гальма!