Пошук уроків, статей та іншого контенту
Проєктуйте складені, часткові та покривні індекси й оцінюйте їхню користь через EXPLAIN.
Індекс прискорює пошук рядків, але не є безкоштовним:
займає дисковий простір;
збільшує вартість INSERT, UPDATE і DELETE;
потребує обслуговування під час VACUUM;
може не використовуватися планувальником;
дублює дані, якщо кілька індексів мають схожу структуру.
Оптимізація індексів — це не створення якомога більшої їх кількості. Завдання полягає в тому, щоб індекси відповідали реальним запитам і зменшували загальну вартість роботи системи.
Основні інструменти цієї оптимізації:
складені індекси;
часткові індекси;
покривні індекси;
EXPLAIN та EXPLAIN ANALYZE.
Складений індекс містить кілька стовпців:
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);Такий індекс особливо корисний для запитів, які фільтрують за customer_id, а потім використовують created_at:
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
AND created_at >= TIMESTAMP '2026-01-01';Порядок стовпців має значення. Індекс:
(customer_id, created_at)не є повною заміною індексу:
(created_at, customer_id)Для B-tree-індексу зазвичай корисно розміщувати:
стовпці з умовами точного порівняння (=);
стовпець із діапазоном (>, <, BETWEEN);
стовпці для сортування.
Наприклад:
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);Цей індекс добре підходить для запиту:
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;Спочатку PostgreSQL знаходить записи конкретного клієнта зі статусом paid, а потім читає їх у потрібному порядку.
Розглянемо два індекси:
CREATE INDEX idx_a
ON orders (customer_id, created_at);
CREATE INDEX idx_b
ON orders (created_at, customer_id);Для цього запиту:
SELECT *
FROM orders
WHERE customer_id = 42
AND created_at >= TIMESTAMP '2026-01-01';обидва індекси можуть бути корисними. Але якщо типовий запит має вигляд:
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;кращим вибором буде:
(customer_id, created_at DESC)Причина — PostgreSQL може одразу перейти до діапазону одного клієнта та читати записи в потрібному порядку.
Водночас не слід механічно ставити «найселективніший» стовпець першим. Потрібно враховувати повний шаблон запитів:
які поля використовуються в WHERE;
чи є рівність або діапазон;
чи потрібен ORDER BY;
чи використовується LIMIT;
чи часто фільтрується лише перший стовпець індексу.
Для індексу:
(customer_id, status, created_at)найкраще підтримуються запити, які використовують ліву частину індексу:
WHERE customer_id = 42
WHERE customer_id = 42
AND status = 'paid'
WHERE customer_id = 42
AND status = 'paid'
AND created_at >= TIMESTAMP '2026-01-01'Запит лише за created_at може не отримати істотної користі від цього індексу:
SELECT *
FROM orders
WHERE created_at >= TIMESTAMP '2026-01-01';У такій ситуації окремий індекс на created_at може бути доречнішим, але це потрібно перевірити через план виконання та реальні навантаження.
Частковий індекс містить лише рядки, які відповідають предикату:
CREATE INDEX idx_orders_paid_created
ON orders (created_at DESC)
WHERE status = 'paid';Такий індекс менший за повний індекс на created_at і дешевший у підтримці, якщо оплачений статус мають лише частина замовлень.
Він підходить для запитів, предикат яких узгоджується з умовою індексу:
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 100;Індекс може бути непридатним для запиту без обмеження status:
SELECT id, customer_id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 100;Планувальник не може використати індекс, який містить лише оплачені замовлення, якщо запит потенційно потребує замовлення з будь-яким статусом.
Предикат має бути достатньо стабільним і відповідати умовам запитів:
CREATE INDEX idx_active_users_email
ON users (email)
WHERE is_active = true;Добрий запит:
SELECT id, name
FROM users
WHERE is_active = true
AND email = 'user@example.com';Предикат може містити кілька умов:
CREATE INDEX idx_orders_paid_large
ON orders (customer_id, created_at DESC)
WHERE status = 'paid'
AND total >= 1000;Такий індекс зменшує розмір структури, але його користь обмежена лише запитами, які працюють із цим підмножиною даних.
Не варто створювати частковий індекс для випадку, який постійно змінюється через поточну дату, наприклад на основі CURRENT_DATE. Межа часткового індексу не оновлюється автоматично щодня. Для часових даних зазвичай використовують інший підхід до обслуговування індексів або партиціювання, але це окрема задача.
Покривний індекс містить усі дані, необхідні запиту. У PostgreSQL додаткові стовпці можна додати через INCLUDE:
CREATE INDEX idx_orders_customer_created_covering
ON orders (customer_id, created_at DESC)
INCLUDE (id, total, status);customer_id і created_at є ключовими частинами індексу. Вони беруть участь у пошуку та сортуванні.
id, total і status є включеними стовпцями. Вони зберігаються в листках індексу, але не визначають порядок записів.
Запит:
SELECT id, total, status, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;може бути виконаний без читання самої таблиці. Такий план називається Index Only Scan.
Покривний індекс не гарантує Index Only Scan. PostgreSQL перевіряє visibility map — структуру, яка показує, чи всі транзакції можуть бачити сторінки таблиці без додаткової перевірки.
Якщо сторінки часто змінюються, PostgreSQL може виконати звичайний Index Scan і додатково звертатися до таблиці.
Інші обмеження:
великі значення в INCLUDE збільшують розмір індексу;
додавання багатьох стовпців підвищує вартість змін даних;
покривний індекс не завжди кращий за компактний індекс;
необхідні регулярні VACUUM та коректна статистика.
INCLUDE слід застосовувати для невеликої кількості часто потрібних стовпців, а не копіювати в індекс усю таблицю.
EXPLAIN показує план, який PostgreSQL обрав для запиту:
EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;У плані важливо звертати увагу на:
тип сканування;
оцінку кількості рядків;
фактичну кількість рядків;
вартість;
наявність сортування;
кількість звернень до таблиці.
Основні типи сканування:
Seq Scan — послідовне читання таблиці;
Index Scan — читання індексу з доступом до таблиці;
Index Only Scan — читання лише індексу;
Bitmap Index Scan — пошук позицій у індексі;
Bitmap Heap Scan — читання відповідних сторінок таблиці.
EXPLAIN ANALYZE фактично виконує запит і додає реальні показники:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;Приклад фрагмента плану:
Limit
-> Index Only Scan using idx_orders_customer_created_covering on orders
Index Cond: (customer_id = 42)
Heap Fetches: 0Heap Fetches: 0 означає, що для отримання результату не знадобилося читати рядки з таблиці.
Якщо план містить:
Sort
Sort Key: created_at DESCце може означати, що індекс не відповідає потрібному порядку або не використовується для сортування.
Опції BUFFERS допомагають оцінити роботу з буферами:
shared hit — сторінка вже була в кеші PostgreSQL;
shared read — сторінку довелося прочитати;
велика кількість прочитаних сторінок може свідчити про неефективний план.
EXPLAIN ANALYZE для INSERT, UPDATE або DELETE змінює дані. Для безпечної перевірки можна використовувати транзакцію:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE created_at < TIMESTAMP '2024-01-01';
ROLLBACK;Нижче наведено самодостатній приклад для перевірки складеного, часткового та покривного індексів.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
total numeric(12, 2) NOT NULL,
created_at timestamptz NOT NULL
);
INSERT INTO orders (customer_id, status, total, created_at)
SELECT
1 + floor(random() * 1000)::integer,
CASE
WHEN random() < 0.60 THEN 'paid'
WHEN random() < 0.85 THEN 'pending'
ELSE 'cancelled'
END,
round((10 + random() * 4990)::numeric, 2),
now() - (random() * interval '730 days')
FROM generate_series(1, 300000);
-- Оновлюємо статистику перед аналізом планів.
ANALYZE orders;
-- Складений індекс для клієнта, фільтрації статусу та сортування за датою.
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);
-- Частковий індекс лише для оплачених замовлень.
CREATE INDEX idx_orders_paid_created
ON orders (created_at DESC)
WHERE status = 'paid';
-- Покривний індекс для списку замовлень конкретного клієнта.
CREATE INDEX idx_orders_customer_created_covering
ON orders (customer_id, created_at DESC)
INCLUDE (id, total, status);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, status, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 100;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC;Для першого запиту потенційно підходить покривний індекс. Для другого — частковий індекс на status = 'paid'. Для третього планувальник порівнюватиме кілька доступних варіантів.
Назви планів можуть відрізнятися залежно від версії PostgreSQL, розміру таблиці, розподілу даних і статистики. Важливо аналізувати не лише тип вузла, а й:
actual time;
rows;
Buffers;
кількість Heap Fetches;
різницю між оціненими та фактичними рядками.
Наявність індексу ще не доводить його користь. Перевіряйте запити в умовах, наближених до продуктивного середовища.
Спочатку виконайте:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;Потім створіть індекс або змініть його структуру та повторіть запит.
Порівнюйте:
загальний час виконання;
кількість прочитаних сторінок;
кількість оброблених рядків;
наявність сортування;
використання таблиці після пошуку в індексі.
Не варто оцінювати індекс лише за значенням cost. Це внутрішня оцінка планувальника, а не час у мілісекундах. Для фактичної оцінки потрібен ANALYZE.
Планувальник приймає рішення на основі статистики. Після значних змін у таблиці виконайте:
ANALYZE orders;Якщо оцінка rows сильно відрізняється від actual rows, планувальник може вибрати невдалий план.
Наприклад:
rows=10 (actual rows=50000)означає, що статистика або її деталізація недостатні для цього розподілу даних. У таких випадках потрібно спершу перевірити статистику та запити, а не одразу додавати нові індекси.
Для довготривалого аналізу можна переглядати статистику використання індексів:
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan, indexrelname;idx_scan = 0 не завжди означає, що індекс непотрібний. Статистика могла бути скинута, а рідкісний запит може бути критично важливим. Перед видаленням індексу перевірте період спостереження, резервні сценарії та всі типи навантаження.
Індекси:
(customer_id)
(customer_id, created_at)можуть частково дублювати один одного. Другий індекс часто здатний підтримувати пошук лише за customer_id, оскільки цей стовпець є його лівою частиною.
Але рішення про видалення першого індексу потрібно приймати лише після перевірки реальних планів і статистики використання.
Індекс:
(created_at, customer_id)може бути невдалим для запиту, який найчастіше вибирає одного клієнта та сортує його замовлення за датою.
У такому випадку слід перевірити варіант:
(customer_id, created_at DESC)Додавання десятків стовпців через INCLUDE збільшує індекс і вартість змін даних. Покривний індекс має покривати конкретний важливий запит, а не всі можливі запити.
Індекс:
CREATE INDEX idx_paid_orders
ON orders (created_at)
WHERE status = 'paid';не допоможе запиту, який не містить умови status = 'paid'.
Також потрібно враховувати точний логічний зв’язок між предикатом індексу та предикатом запиту. Не кожен складний вираз планувальник зможе довести як сумісний.
Для маленької таблиці Seq Scan часто швидший за індекс, бо послідовне читання кількох сторінок дешевше за навігацію індексом.
Тому тестувати індекси потрібно на обсязі та розподілі даних, близьких до реальних.
Запит із ORDER BY ... LIMIT може бути значно швидшим, якщо індекс одразу повертає рядки в потрібному порядку. Інакше PostgreSQL може спочатку знайти багато рядків, а потім сортувати їх окремим вузлом Sort.
EXPLAIN показує прогноз, але не фактичний результат. Для перевірки продуктивності використовуйте:
EXPLAIN (ANALYZE, BUFFERS)і пам’ятайте, що цей варіант виконує запит.
Складений індекс проєктують під повний шаблон запиту, а не лише під окремий стовпець.
Для B-tree важливий порядок стовпців: спочатку рівності, потім діапазони та сортування.
Частковий індекс зберігає лише рядки, що відповідають предикату, тому він може бути компактнішим і швидшим.
Покривний індекс із INCLUDE може дозволити Index Only Scan, але це залежить також від visibility map.
EXPLAIN (ANALYZE, BUFFERS) допомагає порівняти прогноз планувальника з фактичним виконанням.
Користь індексу потрібно оцінювати за реальними запитами, обсягом даних, кількістю читань і вартістю змін.
Надлишкові та широкі індекси погіршують запис і збільшують витрати на обслуговування.