Індекси в PostgreSQL: як прискорити запити

Індекс у PostgreSQL — це окрема структура даних, яка допомагає швидше знаходити рядки в таблиці. Без індексу PostgreSQL часто змушений переглядати всі рядки таблиці. Такий підхід називається послідовним скануванням (Seq Scan).

Індекс дає змогу спочатку знайти потрібні значення в компактній структурі, а потім звернутися лише до відповідних рядків таблиці.

Водночас індекси не є безкоштовними:

• займають місце на диску;

• уповільнюють INSERT, UPDATE і DELETE;

• потребують обслуговування;

• не гарантують прискорення кожного запиту.

Тому індекси потрібно створювати під конкретні сценарії доступу до даних.

Як PostgreSQL шукає дані без індексу

Розглянемо таблицю користувачів:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Якщо виконати запит:

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

і на стовпці email немає індексу, PostgreSQL може перевірити email кожного рядка. Для невеликої таблиці це майже непомітно, але мільйони рядків роблять такий пошук дорогим.

Первинний ключ id індексується автоматично. Для інших стовпців індекси потрібно створювати явно:

CREATE INDEX users_email_idx
ON users (email);

Після цього PostgreSQL може використати індекс, щоб швидше знайти рядки з потрібною електронною адресою.

Що таке B-tree індекс

За замовчуванням PostgreSQL створює індекси типу B-tree. Це збалансоване дерево, у якому значення зберігаються в упорядкованому вигляді.

B-tree добре підходить для:

• точного порівняння: =;

• діапазонів: >, >=, <, <=;

• сортування: ORDER BY;

• перевірки діапазонів дат;

• частини операцій із префіксним пошуком по рядках.

Наприклад, індекс на created_at може допомогти таким запитам:

SELECT *
FROM users
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-02-01';

Також він може бути корисним для:

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 20;

Індекс зберігає значення та посилання на відповідні рядки таблиці. PostgreSQL може пройти дерево від кореня до потрібного діапазону, а потім послідовно прочитати відповідні записи індексу.

Як перевірити, чи використовується індекс

Для аналізу плану виконання використовують EXPLAIN:

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

Щоб побачити фактичний час виконання та кількість оброблених рядків, використовуйте:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE email = 'olena@example.com';

Серед можливих вузлів плану можна побачити:

• Seq Scan — послідовне сканування таблиці;

• Index Scan — пошук через індекс із подальшим читанням рядків таблиці;

• Index Only Scan — PostgreSQL отримує необхідні дані без звернення до самої таблиці або з мінімальним зверненням;

• Bitmap Index Scan — пошук набору позицій через індекс;

• Bitmap Heap Scan — читання знайдених рядків із таблиці.

Наприклад:

Index Scan using users_email_idx on users
  Index Cond: (email = 'olena@example.com')

Це означає, що PostgreSQL використав індекс users_email_idx.

Важливо: наявність індексу не означає, що PostgreSQL завжди його використає. Планувальник порівнює приблизну вартість різних способів виконання запиту.

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

Таблиця маленька

Якщо таблиця містить лише кілька десятків або сотень рядків, послідовне сканування може бути швидшим. Витрати на відкриття та обхід індексу можуть перевищити користь від нього.

Запит повертає значну частину таблиці

Індекс особливо корисний, коли умова відбирає невелику кількість рядків. Якщо запит повертає половину або більшу частину таблиці, PostgreSQL може вибрати Seq Scan.

Наприклад:

SELECT *
FROM users
WHERE created_at >= '2020-01-01';

Якщо майже всі користувачі були створені після цієї дати, індекс навряд чи буде найкращим способом читання даних.

Статистика застаріла

Планувальник спирається на статистику про розподіл значень у таблиці. Її оновлює ANALYZE, який зазвичай запускається автоматично через autovacuum.

Для ручного оновлення статистики можна виконати:

ANALYZE users;

Умова змінює значення стовпця

Індекс на email не обов’язково допоможе цьому запиту:

SELECT *
FROM users
WHERE lower(email) = 'olena@example.com';

Запит застосовує функцію lower до стовпця. Звичайний індекс на email не має готових значень для такого виразу.

У цьому випадку можна створити індекс на вираз:

CREATE INDEX users_lower_email_idx
ON users (lower(email));

Після цього індекс відповідає виразу в умові запиту.

Типи даних або перетворення не збігаються

Необережні неявні приведення типів можуть ускладнити використання індексу. Особливо це помітно, коли стовпець і параметр запиту мають різні типи.

Бажано, щоб типи порівнюваних значень відповідали типу стовпця.

Створення та видалення індексів

Звичайний індекс створюють командою:

CREATE INDEX users_created_at_idx
ON users (created_at);

Якщо індекс більше не потрібен:

DROP INDEX users_created_at_idx;

Для створення індексу в робочій системі можна використати CONCURRENTLY:

CREATE INDEX CONCURRENTLY users_created_at_idx
ON users (created_at);

Такий варіант зменшує блокування звичайних операцій із таблицею, але має особливості:

• створення може тривати довше;

• команда не виконується всередині транзакції;

• після помилки може залишитися недійсний індекс, який потрібно видалити або виправити.

CONCURRENTLY не варто застосовувати автоматично в усіх випадках. Вибір залежить від розміру таблиці та вимог до доступності системи.

Унікальні індекси

Унікальний індекс забороняє дублювання значень:

CREATE UNIQUE INDEX users_email_unique_idx
ON users (email);

Для обмежень цілісності зазвичай краще використовувати UNIQUE:

ALTER TABLE users
ADD CONSTRAINT users_email_unique
UNIQUE (email);

PostgreSQL створить індекс для забезпечення цього обмеження.

Якщо в таблиці вже є дублікати, створення унікального індексу або обмеження завершиться помилкою.

Складені індекси

Складений індекс охоплює кілька стовпців:

CREATE INDEX orders_customer_status_idx
ON orders (customer_id, status);

Такий індекс корисний для запитів, які починаються з першого стовпця індексу:

SELECT *
FROM orders
WHERE customer_id = 42;

SELECT *
FROM orders
WHERE customer_id = 42
  AND status = 'paid';

Порядок стовпців має значення. Індекс (customer_id, status) не є повною заміною індексу лише на status.

Для запиту за другим стовпцем:

SELECT *
FROM orders
WHERE status = 'paid';

PostgreSQL іноді все ще може використати складений індекс, але зазвичай це не буде таким ефективним, як спеціальний індекс на status.

Як вибирати порядок стовпців

Порядок залежить від реальних запитів. Часто на початку ставлять стовпці, за якими виконують точну фільтрацію, а далі — стовпці для діапазону або сортування.

Наприклад:

CREATE INDEX orders_customer_created_at_idx
ON orders (customer_id, created_at DESC);

Цей індекс добре відповідає запиту:

SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

PostgreSQL може знайти замовлення конкретного клієнта вже в потрібному порядку та зупинитися після перших 20 рядків.

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

Частковий індекс містить лише рядки, які відповідають певній умові.

Припустімо, у таблиці замовлень часто шукають лише активні записи:

CREATE INDEX orders_active_customer_idx
ON orders (customer_id)
WHERE status = 'active';

Тепер індекс менший за індекс усієї таблиці й може бути ефективнішим для запиту:

SELECT *
FROM orders
WHERE customer_id = 42
  AND status = 'active';

Умова запиту має бути логічно сумісною з умовою часткового індексу. Якщо PostgreSQL не може довести, що індекс містить усі потрібні рядки, він може його не використати.

Часткові індекси особливо корисні для:

• активних або невидалених записів;

• черг завдань;

• рядків зі статусом, який часто повторюється;

• таблиць із логічним видаленням.

Індекси на вирази

Іноді запити працюють не безпосередньо зі значенням стовпця, а з результатом функції або арифметичного виразу.

Наприклад:

CREATE INDEX products_normalized_name_idx
ON products (lower(name));

Тоді PostgreSQL може використати індекс для:

SELECT *
FROM products
WHERE lower(name) = 'ноутбук';

Індекс на вираз потрібно створювати саме для виразу, який використовується в запитах. Індекс на name та індекс на lower(name) — це різні індекси для різних умов.

Сортування та індекси

B-tree індекси зберігають значення в порядку. Тому вони можуть допомагати запитам із ORDER BY.

Наприклад:

CREATE INDEX articles_published_at_idx
ON articles (published_at DESC);

Це може бути корисним для:

SELECT id, title, published_at
FROM articles
WHERE published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 10;

Для великих таблиць поєднання фільтрації, сортування та LIMIT часто є одним із найкорисніших сценаріїв для індексу.

Однак індекс не завжди усуває сортування. Якщо запит має умови або порядок, які не відповідають структурі індексу, PostgreSQL може додатково виконати операцію Sort.

Індекс і NULL

B-tree індекси підтримують пошук NULL, наприклад:

SELECT *
FROM tasks
WHERE completed_at IS NULL;

Для такого сценарію індекс на completed_at може бути корисним, особливо якщо NULL мають лише частина рядків.

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

CREATE INDEX tasks_open_idx
ON tasks (id)
WHERE completed_at IS NULL;

Конкретний вибір стовпця та умови залежить від запитів і розподілу даних.

Вартість індексів під час змін даних

Коли рядок додається до таблиці, PostgreSQL зазвичай має додати відповідний запис у кожен індекс. Під час оновлення індексованого стовпця запис в індексі також потрібно змінити.

Через це велика кількість індексів може:

• зменшити швидкість вставки;

• збільшити час оновлень;

• збільшити розмір резервних копій;

• потребувати більше пам’яті під час обслуговування;

• створювати зайве навантаження на autovacuum.

Не варто створювати індекс для кожного стовпця таблиці. Кожен індекс має відповідати реальному запиту або обмеженню цілісності.

Як аналізувати запит на практиці

Практичний процес може виглядати так:

1. Виберіть повільний запит.

2. Запустіть EXPLAIN (ANALYZE, BUFFERS).

3. Знайдіть операції, які обробляють найбільше рядків або займають найбільше часу.

4. Перевірте умови WHERE, JOIN, ORDER BY і LIMIT.

5. Створіть індекс, який відповідає реальному шаблону запиту.

6. Повторіть вимірювання після створення індексу.

7. Переконайтеся, що прискорення не створило неприйнятні витрати для операцій запису.

Приклад:

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

Можливий індекс для такого запиту:

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

Після створення індексу план потрібно перевірити повторно. Не слід оцінювати ефект лише за припущенням.

Index Only Scan

У деяких випадках PostgreSQL може отримати всі потрібні значення без читання основної таблиці. Це називається Index Only Scan.

Наприклад, якщо запит читає лише стовпці, які є в індексі:

CREATE INDEX users_created_at_id_idx
ON users (created_at, id);

Запит:

SELECT id, created_at
FROM users
WHERE created_at >= '2026-01-01';

може використовувати лише індекс.

Фактична ефективність залежить також від visibility map та стану таблиці. Тому сам факт наявності всіх стовпців в індексі не гарантує, що звернення до таблиці не буде зовсім.

Покриття запитів за допомогою INCLUDE

PostgreSQL підтримує додаткові стовпці в індексі через INCLUDE:

CREATE INDEX orders_customer_idx
ON orders (customer_id)
INCLUDE (status, total);

customer_id є ключем індексу, а status і total зберігаються як додаткові значення. Це може допомогти запитам, які фільтрують за customer_id, але також повертають інші стовпці.

Додавати багато стовпців через INCLUDE не варто без вимірювань. Індекс стане більшим, а зміни таблиці — дорожчими.

Поширені помилки

Створення індексу без аналізу запитів

Індекс має бути обґрунтований конкретними запитами. Створення індексів «про всяк випадок» збільшує витрати на запис і не обов’язково покращує продуктивність.

Ігнорування порядку стовпців у складеному індексі

Індекси (a, b) і (b, a) не є взаємозамінними. Порядок потрібно визначати на основі фільтрації, діапазонів і сортування.

Надмірна кількість індексів

Кілька схожих індексів можуть дублювати один одного. Перед створенням нового індексу варто перевірити вже наявні.

Перевірка лише на маленькому наборі даних

На маленькій таблиці Seq Scan може бути швидшим за індекс. Поведінку потрібно перевіряти на даних, близьких до реального обсягу.

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

PostgreSQL сам вибирає план виконання. Не варто намагатися примусово змусити його використовувати індекс без розуміння причини. Якщо план здається неправильним, спочатку перевірте статистику, типи даних і структуру запиту.

Використання функції без індексу на вираз

Індекс на email не обов’язково допоможе для lower(email). Для повторюваного виразу потрібен індекс на відповідний вираз.

Забування про оновлення статистики

Після значних змін у таблиці планувальнику може знадобитися актуальна статистика:

ANALYZE orders;

Додавання індексу для стовпця з низькою вибірковістю

Стовпець зі значеннями на кшталт true або false часто має низьку вибірковість. Звичайний індекс на ньому не завжди корисний. У такій ситуації частковий індекс для рідкісного значення може бути кращим рішенням.

Підсумок

B-tree — універсальний тип індексу PostgreSQL для пошуку за рівністю, діапазонами та сортуванням.

Під час проєктування індексів варто:

• аналізувати реальні запити через EXPLAIN (ANALYZE, BUFFERS);

• враховувати умови WHERE, JOIN, ORDER BY і LIMIT;

• правильно вибирати порядок стовпців у складених індексах;

• використовувати часткові індекси для підмножин даних;

• створювати індекси на вирази, якщо запити застосовують функції;

• пам’ятати про вартість індексів для операцій запису;

• перевіряти результат вимірюваннями, а не припущеннями.

Хороший індекс — це не просто індекс на часто використовуваному стовпці, а структура, яка відповідає конкретному способу читання даних.