Індекси PostgreSQL: B-tree, GIN і коли вони допомагають

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

Докладніше: Без індексу PostgreSQL читає для запиту всю таблицю (seq scan). Індекс швидко знаходить рядки, як предметний покажчик у книзі.

Подробиці

Типи індексів

• B-tree (за замовчуванням): рівність, діапазони (<, >, BETWEEN), сортування (ORDER BY), префікс LIKE 'abc%'.
• Hash: лише рівність.
• GIN: багатозначні дані (масиви, JSONB, повнотекстовий tsvector).
• GiST/SP-GiST: геодані, діапазони, схожість.
• BRIN: дуже великі, впорядковані за часом таблиці, крихітний.
• pgvector (HNSW/IVFFlat): векторний пошук.

CREATE INDEX idx_user_email ON users (email);
CREATE UNIQUE INDEX uq_user_email ON users (lower(email));
CREATE INDEX idx_order_customer_date ON orders (customer_id, created DESC);
CREATE INDEX idx_active ON users (id) WHERE active;       -- частковий
CREATE INDEX idx_tags ON articles USING gin (tags);

Коли індекс окуповується?

• Стовпці в WHERE, JOIN (зовнішні ключі!), ORDER BY.
• Вибіркові умови (мало збігів).
• Складені індекси: порядок важливий; (customer_id, created) допомагає за WHERE customer_id = … і … AND created …, але не за фільтра лише за created (правило лівого префікса).
• Без індексу на крихітних таблицях, на стовпцях із малою кількістю значень (так/ні) самих по собі або якщо в таблицю постійно інтенсивно пишуть.
• Вартість: кожен індекс сповільнює INSERT/UPDATE/DELETE, займає сховище та обслуговування.

Перевірка через EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created DESC LIMIT 10;

Seq Scan на великій таблиці = підозра; Index Scan/Index Only Scan/Bitmap Heap Scan = індекс використано. Планувальник обирає за статистикою (ANALYZE); іноді за багатьох збігів seq scan швидший. pg_stat_user_indexes показує ніколи не використовувані індекси (видаліть їх). CREATE INDEX CONCURRENTLY будує без довгого блокування.

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

Функції над стовпцями (WHERE lower(email) = …) заважають звичайному індексу (→ функціональний індекс). Перетворення типів, провідний % у LIKE, OR за різними стовпцями. Відсутність індексу на зовнішньому ключі сповільнює з’єднання та видалення. Забагато індексів. У безсерверних базах даних враховується й час обчислень: менше seq scan = менше годин CPU.

Див. також: PostgreSQL, Планувальник запитів і EXPLAIN, Оптимізація запитів з EXPLAIN, Проблема N+1, Векторні бази даних