При створенні PRIMARY KEY або UNIQUE автоматично створюється B-Tree індекс.

Приклад:

CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 email TEXT UNIQUE
);

Це означає, що:

• PRIMARY KEY (id) створить індекс для швидкого пошуку по id.
• UNIQUE (email) створить індекс, що запобігає дублювання.

Коли корисно:

• id часто використовується в JOIN.
• Пошук по email виконується часто.

Зовнішні ключі (FOREIGN KEY) самі собою не створюють індекси автоматично (наприклад, в PostgreSQL). Якщо за ними виконуються JOIN, індексація є обов’язковою.

Приклад:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Тепер JOIN users ON orders.user_id = users.id працює швидше.

Коли використовувати:

• Якщо зовнішній ключ бере участь у JOIN.
• Якщо часто використовується в WHERE.

Індексація допомагає, якщо запит містить WHERE, ORDER BY, GROUP BY.

Приклад:

CREATE INDEX idx_users_lastname ON users(last_name);

Тепер WHERE last_name = 'Smith' та ORDER BY last_name працюють швидше.

• Рідко використовувані індекси. Якщо стовпець рідко використовується в запитах, індекс тільки займає місце.
• Малі таблиці. Якщо таблиця містить менше 1000 рядків, повний перебір (Seq Scan) швидше індексу.
• Часті INSERT, UPDATE, DELETE. Індекси уповільнюють вставку та оновлення. Наприклад, якщо є 5 індексів, кожне оновлення має змінювати їх усі.

Приклад проблеми:

UPDATE users SET email = 'new@example.com' WHERE id = 100;

Якщо email проіндексовано, оновлення потребує зміни індексу.

Рішення:

• Уникати індексів на стовпцях, що часто змінюються.
• Використовувати часткові індекси (обмеження індексації WHERE).

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

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Тепер індекс застосовується лише до активних користувачів.

Індекси створюються за допомогою ‘CREATE INDEX’.

• Приклад звичайного B-Tree індексу:

CREATE INDEX idx_users_lastname ON users(last_name);

• Створення унікального індексу:

CREATE UNIQUE INDEX idx_users_email ON users(email);

• Створення багатоколонного індексу:

CREATE INDEX idx_orders_date_status ON orders(order_date, status);

Такий індекс прискорює:

• WHERE order_date = '2024-01-01' AND status = 'shipped'
• ORDER BY order_date, status
• Але не прискорює пошук тільки по ‘status’.

Якщо індекс більше не потрібний, його можна видалити:

DROP INDEX idx_users_lastname;

Або за необхідності перейменувати:

ALTER INDEX idx_users_lastname RENAME TO idx_users_lname;

Деякі СУБД створюють індекси автоматично:

• PostgreSQL – створює індекси для PRIMARY KEY та UNIQUE, але не для FOREIGN KEY.
• MySQL (InnoDB) – автоматично індексує PRIMARY KEY, UNIQUE та FOREIGN KEY.
• SQLite – індексує PRIMARY KEY та UNIQUE.

Перевірити індекси можна за допомогою (приклад PostgreSQL):

SELECT * FROM pg_indexes WHERE tablename = 'users';

Звичайний індекс зберігає лише ключові стовпці. Покриваючий індекс дозволяє додати додаткові дані, прискорюючи SELECT.

CREATE INDEX idx_users_email_include ON users(email) INCLUDE (name, created_at);

Тепер SELECT name, created_at FROM users WHERE email = 'user@example.com' не вимагає звернення до таблиці.

Індексувати лише частину даних – корисно великих таблиць.

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Тепер індекс застосовується лише до активних користувачів.

Перед оптимізацією важливо зрозуміти, чи запит запитує індекси. Для цього застосовується EXPLAIN:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Результат покаже, чи буде використано Index Scan або Seq Scan (повний перебір).

Для детального аналізу з виміром часу виконання додають ANALYZE:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

Це виконує запит та показує реальні витрати.

Якщо використовується Seq Scan – індекс не застосовується, можливі причини:

• Немає індексу на полі в WHERE.
• Фільтрація повертає занадто багато рядків (оптимальніше повний перебір).
• Умови запиту не відповідають структурі індексу.

Якщо використовується Index Scan або Index Only Scan – індекс задіяний:

• Index Scan – індекс використовується, але дані запитуються з таблиці.
• Index Only Scan – індекс повністю покриває запит (працює швидше).

• Селективність індексу. Індекс ефективний, якщо він відкидає більшу частину даних. Якщо індексується поле з невеликою кількістю унікальних значень (наприклад, is_active = true/false), індекс може не дати приросту.

• Правильний порядок полів у складових індексах. При використанні індексу на кількох стовпцях (CREATE INDEX idx_orders ON orders(status, created_at)) запит WHERE stat> = '< ^>2024-01-01<^>' буде ефективним, але WHERE created_at > '2024-01-01' індекс не використовує.

• Видаляти індекси, що не використовуються – можна перевірити статистику індексного доступу:

SELECT * FROM pg_stat_user_indexes WHERE schemaname = 'public';

• Використовувати VACUUM ANALYZE (PostgreSQL) – оновлює статистику планувальника запитів:

VACUUM ANALYZE;

• Не індексувати стовпці з низькою селективністю - індекс на gender (м/ж) марний.

• Використовувати часткові індекси – зменшує розмір індексу, прискорюючи роботу.

• Перебудовувати індекси (REINDEX) – видаляє фрагментацію, покращуючи продуктивність:

REINDEX TABLE users;