Індекси в PostgreSQL: B-tree, GIN і як читати EXPLAIN ANALYZE

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

Почніть з EXPLAIN ANALYZE

Перш ніж створювати індекс, подивіться, що робить запит:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=48211.10..48211.15 rows=20 width=16) (actual time=412.3..412.3 rows=20 loops=1)
  ->  Sort  (cost=48211.10..48215.31 rows=1684 width=16) (actual time=412.3..412.3 rows=20 loops=1)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..48166.00 rows=1684 width=16) (actual time=0.03..411.8 rows=1702 loops=1)
              Filter: ((customer_id = 4821) AND (status = 'paid'))
              Rows Removed by Filter: 2398298
        Buffers: shared hit=1204 read=22762
Execution Time: 412.5 ms

Як читати план:

• знизу вгору і зсередини назовні — найглибший вузол виконується першим;
• Seq Scan + Rows Removed by Filter: 2398298 — прочитали 2.4 млн рядків, щоб знайти 1702;
• cost — оцінка планувальника, actual time — реальність. Велика різниця між rows в оцінці і в реальності — ознака застарілої статистики (ANALYZE orders);
• Buffers: read — сторінки, прочитані з диска, а не з кешу.

Обережно

EXPLAIN ANALYZE реально виконує запит. Для UPDATE/DELETE загортайте його в транзакцію з ROLLBACK.

B-tree: індекс за замовчуванням

B-tree підходить для =, <, >, BETWEEN, IN, ORDER BY і пошуку за префіксом (LIKE 'abc%' з text_pattern_ops або C-колацією).

Для запиту вище ідеальний складений індекс:

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

Limit  (actual time=0.05..0.09 rows=20 loops=1)
  ->  Index Scan using orders_customer_status_created_idx on orders
        Index Cond: ((customer_id = 4821) AND (status = 'paid'))
Execution Time: 0.12 ms

412 мс → 0.12 мс. Індекс одночасно фільтрує і віддає рядки вже відсортованими — сортування зникло з плану.

Порядок колонок важливий

Індекс (customer_id, status, created_at) допомагає запитам, що фільтрують за лівою частиною: customer_id, або customer_id + status. Запиту лише за status він майже не допоможе.

Порада

Правило: спочатку колонки з рівністю (=), потім ті, що в діапазоні або сортуванні.

Частинні й покривні індекси

-- Частинний: індексуємо лише актуальні рядки — індекс у рази менший
CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) WHERE status = 'pending';

-- Покривний: потрібні колонки лежать в індексі → Index Only Scan без звернення до таблиці
CREATE INDEX CONCURRENTLY orders_customer_cover_idx
  ON orders (customer_id) INCLUDE (total, status);

Інші типи індексів

-- Пошук за підрядком у будь-якому місці: ILIKE '%ivan%'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY customers_name_trgm_idx
  ON customers USING gin (name gin_trgm_ops);

Чому PostgreSQL «не використовує мій індекс»

1. Запит повертає велику частку таблиці. Читати 30% рядків через індекс дорожче, ніж послідовно. Планувальник правий.
2. Функція або приведення типу над колонкою. WHERE lower(email) = ... не використає індекс на email — потрібен індекс на вираз lower(email).
3. Невідповідність типів. Колонка bigint, а параметр приходить як text.
4. Застаріла статистика. Після масового завантаження запустіть ANALYZE.
5. Неліва частина складеного індексу — див. вище.

Ціна індексів

Кожен індекс оновлюється при INSERT/UPDATE/DELETE. Знайдіть ті, що не використовуються:

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

Увага

На робочій базі створюйте індекси з CONCURRENTLY — звичайний CREATE INDEX блокує запис у таблицю на весь час побудови. Якщо CONCURRENTLY впав, залишиться невалідний індекс — його треба видалити і створити знову.