Junior: питання на співбесіді з теми «Індекси»

Індекс - окрема відсортована структура (найчастіше B-дерево), яка зберігає значення колонки й посилання на рядки таблиці. Як предметний покажчик у книжці: щоб знайти сторінку, не треба гортати всю книгу.

Без індексу запит WHERE email = 'a@b.com' змушує базу переглянути всю таблицю (Seq Scan / full table scan). З індексом пошук займає кілька кроків по дереву навіть на мільйонах рядків.

CREATE INDEX users_email_idx ON users (email);

Ціна індексу:

• Запис сповільнюється: кожен INSERT, UPDATE індексованої колонки й DELETE змушує базу оновити ще й індекс. Десять індексів на таблиці - десять додаткових записів на кожну вставку.
• Місце на диску і в пам'яті.

Що індексувати: колонки з WHERE, JOIN і ORDER BY у частих запитах, зовнішні ключі. Не варто - колонки, за якими не шукають, і таблиці на кілька сотень рядків.

Важливо: індекс корисний, коли умова відсіює більшість рядків. Колонка is_active, де 95% значень true, мало що дасть - базі все одно читати майже всю таблицю.

Докладніше в документації: Індекси

Складений індекс (a, b, c) впорядковує рядки спершу за a, всередині однакових a - за b, далі за c. Як телефонний довідник: за прізвищем, потім за ім'ям.

CREATE INDEX orders_user_status_idx ON orders (user_id, status, created_at);

Правило лівого префікса: індекс допомагає, коли умова використовує колонки з початку:

• WHERE user_id = 5 - так.
• WHERE user_id = 5 AND status = 'paid' - так.
• WHERE user_id = 5 AND status = 'paid' ORDER BY created_at - так, і сортування береться з індексу.
• WHERE status = 'paid' - ні (або лише дуже повільним повним обходом індексу): шукати за ім'ям у довіднику, впорядкованому за прізвищем, не вийде.

Як обирати порядок:

1. Спершу колонки з рівністю (=), потім ті, що в діапазоні (>, BETWEEN) чи в ORDER BY. Після колонки з діапазоном наступні колонки для пошуку вже майже не працюють.
2. Серед колонок з рівністю - ті, що використовуються в більшості запитів.

Наслідок: індекс (user_id, status) робить окремий індекс на user_id зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.

Докладніше в документації: Багатоколонкові індекси

PostgreSQL має кілька видів індексів, кожен під свій тип запитів:

• B-tree (за замовчуванням) - рівність, діапазони (<, >, BETWEEN), сортування, LIKE 'початок%'. Покриває більшість випадків.
• Hash - лише рівність (=). З PostgreSQL 10 надійний (записується в WAL), але виграш над B-tree рідко помітний.
• GIN (Generalized Inverted Index) - для значень, що містять багато елементів: масиви, jsonb, повнотекстовий пошук (tsvector), триграми (pg_trgm). Шукає «рядки, де є цей елемент».
• GiST - для геометрії й діапазонів: «перетинається», «містить», «найближчий» (PostGIS, tsrange, exclusion constraints, триграми з пошуком найближчих).
• SP-GiST - для нерівномірно розподілених даних: IP-адреси, телефонні префікси, квадродерева.
• BRIN - крихітний індекс для дуже великих таблиць, де значення фізично впорядковані на диску (час вставки в журналі подій).

CREATE INDEX orders_user_idx ON orders (user_id);                       -- B-tree
CREATE INDEX products_tags_idx ON products USING gin (tags);            -- масив
CREATE INDEX docs_search_idx ON docs USING gin (to_tsvector('simple', body));
CREATE INDEX bookings_period_idx ON bookings USING gist (period);       -- tsrange
CREATE INDEX events_created_brin ON events USING brin (created_at);

Як обирати: почати з B-tree. Інші типи - коли запит використовує оператори, які B-tree не підтримує (@>, &&, @@, <->), або коли таблиця настільки велика, що розмір індексу стає проблемою (BRIN).

Нюанс GIN: швидкий на читання, але дорожчий на запис. Тому він накопичує зміни в «відкладеному списку» (fastupdate), який потім вливається в індекс - на таблицях з інтенсивним записом це варто враховувати.

Докладніше в документації: Типи індексів

Які індекси є:

-- у psql
\d orders

-- або запитом
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Чи використовуються - статистика pg_stat_user_indexes, яку PostgreSQL збирає з моменту останнього скидання статистики:

SELECT relname AS table,
       indexrelname AS index,
       idx_scan AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

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

Перш ніж видаляти «невикористаний» індекс:

• Перевірити термін статистики: якщо її скинули вчора, щомісячний звіт ще не встиг скористатися індексом.
• Перевірити всі репліки: статистика збирається окремо на кожному сервері. Індекс може не використовуватися на primary, але бути критичним для звітів, що йдуть на репліку.
• Унікальні індекси й первинні ключі не видаляють, навіть з idx_scan = 0: вони забезпечують цілісність, а не швидкість.
• Видаляти через DROP INDEX CONCURRENTLY, щоб не блокувати таблицю.

Окремо корисно: seq_scan і seq_tup_read у pg_stat_user_tables показують таблиці, які часто читаються повністю, - кандидати на новий індекс.

Докладніше в документації: Статистика індексів