🟢 Junior Level

Складений (композитний) індекс — це індекс, створений на кількох колонках одночасно.

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

Проста аналогія: Уявіть телефонну книгу, де контакти відсортовані спочатку за прізвищем, потім за ім’ям. Спочатку ви шукаєте потрібне прізвище, а всередині нього — потрібне ім’я.

Основна ідея:

• Один індекс може прискорити пошук за кількома полями
• Порядок колонок в індексі критично важливий
• Працює правило лівого префікса

Приклад:

-- Створюємо складений індекс
CREATE INDEX idx_users_city_age ON users(city, age);

-- ✅ Буде використаний (по city)
SELECT * FROM users WHERE city = 'Moscow';

-- ✅ Буде використаний (по city та age)
SELECT * FROM users WHERE city = 'Moscow' AND age = 25;

-- ❌ НЕ буде використаний (тільки age)
SELECT * FROM users WHERE age = 25;

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

• Коли часто шукаєте за комбінацією полів
• Коли запити використовують різні комбінації одних і тих самих полів
• Для оптимізації сортування за кількома полями

---

🟡 Middle Level

Як це працює

Складений індекс сортує дані ієрархічно:

1. Спочатку по першій колонці
2. Всередині однакових значень першої — по другій
3. І так далі

Правило лівого префікса: Індекс (A, B, C) може використовуватися для пошуку за:

• ✅ A
• ✅ A, B
• ✅ A, B, C
• ❌ B (без A)
• ❌ C (без A та B)
• ❌ B, C (без A)

Золоті правила порядку колонок

1. Рівність перед діапазоном (= before >)

-- Запит: WHERE city = 'Moscow' AND age > 25

-- ✅ Правильний порядок
CREATE INDEX idx_city_age ON users(city, age);

-- ❌ Неправильний порядок (age як діапазон зробить city непотрібним)
CREATE INDEX idx_age_city ON users(age, city);

2. Сортування (ORDER BY)

-- Індекс покриває ORDER BY у тому ж порядку
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- ✅ Використовує індекс
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- ❌ Не використовує індекс для сортування
SELECT * FROM orders ORDER BY created_at DESC; -- немає фільтра по user_id

Типові помилки

1. Неправильний порядок колонок

   -- ❌ Якщо city використовується з '=', а age з '>', city повинен бути першим
   CREATE INDEX idx_age_city ON users(age, city); -- Неправильно
   

2. Дублювання індексів

   -- ❌ Індекс (A, B) вже покриває (A)
   CREATE INDEX idx_a ON users(a);           -- Зайвий
   CREATE INDEX idx_a_b ON users(a, b);      -- Покриває обидва випадки
   

3. Занадто багато колонок
   • PostgreSQL підтримує до 32 колонок в індексі
   • На практиці: більше 5 колонок — червоний прапорець
   • Великий індекс → потрібно більше RAM для утримання в кеші → якщо RAM не вистачає → Cache Misses → Random I/O → падіння продуктивності

Порівняння з окремими індексами

Запит	Індекс (A) + (B)	Індекс (A, B)
WHERE A = 1	✅ Використовує (A)	✅ Використовує (A,B)
WHERE B = 2	✅ Використовує (B)	❌ Не використовує
WHERE A = 1 AND B = 2	⚠️ Bitmap або один з них	✅ Повністю використовує

Практичний приклад

-- Типовий e-commerce сценарій
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at DESC);

-- Запит 1: Усі замовлення клієнта
SELECT * FROM orders WHERE customer_id = 123;
-- Plan: Index Scan (по customer_id)

-- Запит 2: Останні замовлення клієнта
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;
-- Plan: Index Scan + Limit (дуже швидко!)

---

🔴 Senior Level

Internal Implementation

Лексикографічне сортування: Ключ складного індекса — це фактично один великий рядок. В B-tree зберігається конкатенація значень колонок.

Структура ключа в Leaf Node:

┌─────────────────────────────────────────┐
│ City="Moscow" | Age=25 | TID=(123,456)  │
├─────────────────────────────────────────┤
│ City="Moscow" | Age=26 | TID=(123,457)  │
├─────────────────────────────────────────┤
│ City="SPb"     | Age=20 | TID=(124,100) │
└─────────────────────────────────────────┘

Стиснення префіксів: PostgreSQL може стискати ключі в Internal вузлах, зберігаючи лише частину, необхідну для навігації.

Архітектурні Trade-offs

Правило порядку колонок:

[Equi-columns] → [Range-column] → [Include-columns]
     (WHERE =)      (WHERE >,<)     (SELECT only)

Приклад:

-- Запит: WHERE status = 'ACTIVE' AND city = 'Moscow' AND created_at > '2024-01-01'
-- ORDER BY created_at DESC

-- ✅ Оптимальний індекс
CREATE INDEX idx_orders_optimal
ON orders(status, city, created_at DESC);

-- status (=) → city (=) → created_at (>) → ідеальний порядок

Змішані напрямки (PG 13+):

-- Для ORDER BY a ASC, b DESC
CREATE INDEX idx_mixed ON table_name(a ASC, b DESC);

-- Звичайний індекс (ASC, ASC) НЕ покриє такий ORDER BY

На PG 12 і нижче змішані напрямки (ASC + DESC) в одному індексі не оптимізуються коректно — індекс буде відсортований в одному напрямку. Рішення: окремий індекс під конкретний ORDER BY або додаткове сортування в запиті.

Емуляція Index Skip Scan

PostgreSQL не має вбудованого Skip Scan (на відміну від Oracle/MySQL 8.0). Але можна емулювати:

-- Проблема: індекс (status, user_id), але шукаємо лише по user_id
-- status має мало унікальних значень (ACTIVE, INACTIVE, DELETED)

-- ✅ Recursive CTE емуляція Skip Scan
WITH RECURSIVE skip_scan AS (
  (SELECT MIN(status) AS status FROM orders)
  UNION ALL
  SELECT (SELECT MIN(status) FROM orders WHERE status > s.status)
  FROM skip_scan s WHERE s.status IS NOT NULL
)
SELECT * FROM orders o
JOIN skip_scan s ON o.status = s.status
WHERE o.user_id = 123;

Продуктивність: На таблиці зі 100 млн записів та 3 унікальними status:

• Без Skip Scan: Seq Scan ~30 секунд
• З емуляцією: ~50 мілісекунд (різниця в 600 разів!)

Edge Cases

1. NULL у складному індексі

   -- NULL сортується останнім за замовчуванням (NULLS LAST)
   CREATE INDEX idx_a_b ON t(a, b);
   
   -- WHERE a IS NULL → шукаємо в кінці індекса
   -- WHERE a = 1 AND b IS NULL → шукаємо в кінці групи a=1
   

2. Клас операторів (OpClass)

   -- OpClass (Operator Class) — набір правил, що визначає, як значення колонки
   -- порівнюються та сортуються в індексі. За замовчуванням для text використовується
   -- звичайне посимвольне порівняння, але можна використовувати, наприклад,
   -- порівняння за хешем або без урахування регістру
   CREATE INDEX idx_name ON users(name COLLATE "C");
   
   -- Для специфічних операцій
   CREATE INDEX idx_tags ON posts USING gin(tags);
   

3. Index-Only Scan та Visibility Map
   • Навіть якщо всі колонки є в індексі, PG може піти в Heap
   • Причина: Сторінки не позначені у Visibility Map
   • Рішення: VACUUM оновлює Visibility Map

Продуктивність

Вплив на запис:

• Кожна колонка в індексі збільшує розмір ключа
• Великий ключ → менше записів на сторінку → більше рівнів дерева
• Для 100 млн записів:
  • Індекс (int): ~2 ГБ
  • Індекс (int, text(50)): ~8 ГБ
  • Індекс (int, text(50), text(100)): ~15 ГБ

Консолідація індексів:

-- ❌ Було (3 індекси, 15 ГБ сумарно)
CREATE INDEX idx_a ON t(a);           -- 2 ГБ
CREATE INDEX idx_a_b ON t(a, b);      -- 5 ГБ
CREATE INDEX idx_a_b_c ON t(a, b, c); -- 8 ГБ

-- ✅ Стало (1 індекс, 8 ГБ)
CREATE INDEX idx_a_b_c ON t(a, b, c); -- Покриває всі префікси!

Production Experience

Реальний сценарій:

• SaaS платформа: 200 млн записів замовлень
• Проблема: 12 окремих індексів, 80 ГБ сумарно
• Симптом: INSERT > 500ms, RAM не вистачає, Cache Hit rate < 60%
• Аналіз через pg_stat_user_indexes:
  • 8 індексів мають idx_scan < 10 за місяць
  • 4 індекси дублюють префікси
• Рішення:
  • Consolidate до 3 складних індексів
  • Додали INCLUDE для Index-Only Scan гарячих запитів
  • Результат: 15 ГБ індексів, INSERT < 50ms, Cache Hit > 95%

Monitoring

-- Перевірка використання складних індексів
SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- Перевірка порядку колонок
SELECT
    i.relname as table_name,
    ix.indexrelid::regclass as index_name,
    array_agg(a.attname ORDER BY k.n) as columns
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indrelid
JOIN pg_class idx ON idx.oid = ix.indexrelid
JOIN generate_series(0, array_length(ix.indkey, 1)-1) as k(n) ON true
JOIN pg_attribute a ON a.attrelid = i.oid AND a.attnum = ix.indkey[k.n]
WHERE i.relname = 'orders'
GROUP BY i.relname, ix.indexrelid;

-- Перевірка дублікатів префіксів
-- (потрібне розширення pg_stat_statements або кастомний запит до pg_index)

Best Practices

1. Порядок колонок: = → діапазони → INCLUDE
2. Консолідація: Один індекс (a,b,c) замінює (a) та (a,b)
3. Обмежте розмір: Максимум 3-5 колонок в індексі
4. INCLUDE: Для колонок тільки в SELECT використовуйте INCLUDE
5. Змішані напрямки: Створюйте індекси під конкретні ORDER BY
6. Моніторинг: Регулярно перевіряйте pg_stat_user_indexes на невикористовувані індекси

Резюме для Senior

• Складений індекс — це лексикографічно відсортований рядок-ключ
• Порядок колонок критичний: Equi → Range → Include
• Емюлюйте Skip Scan через Recursive CTE для низкокардинальних перших колонок
• Консолідуйте індекси для економії RAM та прискорення запису
• Visibility Map впливає на Index-Only Scan — слідкуйте за VACUUM
• Завжди перевіряйте план через EXPLAIN (ANALYZE, BUFFERS)

---

🎯 Шпаргалка для співбесіди

Обов’язково знати:

• Складений індекс сортує дані ієрархічно: 1-ша колонка → 2-га → …
• Правило лівого префікса: (A, B, C) працює для A, A,B, A,B,C але НЕ для B, C
• Порядок колонок: = (equality) → діапазони (>, <) → INCLUDE (тільки SELECT)
• Консолідація: (a), (a,b), (a,b,c) → один (a,b,c) економить місце
• PostgreSQL не має Skip Scan → емуляція через Recursive CTE
• Максимум 3-5 колонок на практиці (PG підтримує до 32)
• Змішані напрямки: (a ASC, b DESC) — PG 13+

Часті уточнюючі питання:

• «Чому WHERE age = 25 не використовує індекс (city, age)?» → Лівий префікс: city перший
• «Як замінити 3 окремих індекси одним?» → Складний (a,b,c) покриває всі префікси
• «Що таке Skip Scan і чому PG його не має?» → Пропуск першої колонки; в PG немає, емулюємо CTE
• «Як ORDER BY впливає на порядок колонок?» → Індекс повинен збігатися з ORDER BY

Червоні прапорці (НЕ говорити):

• ❌ «Порядок колонок не важливий» (критично важливий!)
• ❌ «Створю окремий індекс на кожне поле» (консолідуйте!)
• ❌ «32 колонки в індексі — нормальна практика» (максимум 3-5 на практиці)

Пов’язані теми:

• [[Для чого потрібні індекси]] → коли створювати індекси
• [[Як працює B-tree індекс]] → внутрішня структура
• [[Що таке кардинальність індексу]] → порядок колонок за кардинальністю
• [[Які недоліки є у індексів]] → вартість запису