Пошук уроків, статей та іншого контенту
Навчитеся будувати складені індекси та визначати правильний порядок колонок для кількох умов запиту.
Складений індекс містить дві або більше колонок таблиці. Наприклад:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);Такий індекс зберігає значення колонок у вказаному порядку:
customer_id;
status усередині однакових значень
customer_idСкладені індекси корисні, коли запити часто використовують кілька колонок у WHERE, JOIN або ORDER BY.
Приклад запиту:
SELECT *
FROM orders
WHERE customer_id = 42
AND status = 'paid';Для цього запиту індекс (customer_id, status) може бути ефективнішим, ніж два окремі індекси:
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);Водночас складений індекс не є автоматично кращим у будь-якій ситуації. Важливі порядок колонок і фактичні шаблони запитів.
Для B-tree-індексу PostgreSQL порядок колонок має значення. Індекс:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);найкраще працює для таких умов:
WHERE customer_id = 42WHERE customer_id = 42
AND status = 'paid'WHERE customer_id = 42
ORDER BY statusПерша колонка індексу є його лівим префіксом. У цьому випадку лівий префікс — це customer_id.
Запит лише за другою колонкою:
WHERE status = 'paid'зазвичай не може ефективно використати індекс (customer_id, status) для пошуку потрібних рядків, оскільки PostgreSQL не знає, серед яких значень customer_id шукати.
Тому індекс:
(customer_id, status)і індекс:
(status, customer_id)— це різні індекси з різними сценаріями використання.
Важливо: PostgreSQL може обрати послідовне сканування таблиці або інший план, якщо вважає його дешевшим. Наявність індексу не гарантує, що він буде використаний.
Порядок колонок потрібно визначати не за загальним правилом «найселективніша колонка першою», а за конкретними запитами.
Поставте собі такі запитання:
Які колонки найчастіше використовуються першими в умовах?
Чи є серед умов перевірки на точну рівність?
Чи є діапазонні умови?
Чи потрібно повертати дані в певному порядку?
Чи використовує запит колонку без першої колонки індексу?
Поширений шаблон запиту:
SELECT *
FROM events
WHERE tenant_id = 10
AND event_type = 'error'
AND created_at >= timestamp '2026-01-01';Для нього природним буде індекс:
CREATE INDEX idx_events_tenant_type_created
ON events (tenant_id, event_type, created_at);Тут:
tenant_id фільтрується за точним значенням;
event_type фільтрується за точним значенням;
created_at використовується для діапазону.
Такий порядок дозволяє спочатку знайти конкретного орендаря й тип події, а потім пройти лише потрібний діапазон дат.
Загальний шаблон:
колонки з =
колонки з IN
колонки з діапазономНаприклад:
WHERE account_id = 7
AND category = 'books'
AND price BETWEEN 100 AND 500може використовувати індекс:
(account_id, category, price)Якщо всі умови використовують точну рівність:
WHERE country = 'UA'
AND status = 'active'то обидва варіанти можуть бути корисними:
(country, status)і:
(status, country)Якщо запити завжди містять обидві умови через =, вибір порядку не завжди суттєво впливає на фільтрацію. У такому разі порядок можна визначати з урахуванням:
інших запитів;
потрібного ORDER BY;
можливості використовувати індекс для запитів лише за першою колонкою;
частоти й важливості запитів.
Наприклад, якщо часто виконується:
WHERE status = 'active'то логічніше розмістити status першою колонкою:
(status, country)Розглянемо запит:
SELECT *
FROM products
WHERE category_id = 5
AND price >= 100
AND price <= 500;Для нього підходить:
CREATE INDEX idx_products_category_price
ON products (category_id, price);category_id обмежує пошук до однієї категорії, а price визначає діапазон усередині цієї категорії.
Якщо після діапазону додати ще одну колонку:
WHERE category_id = 5
AND price >= 100
AND price <= 500
AND brand_id = 3;індекс:
(category_id, price, brand_id)усе ще може бути корисним. Проте колонки після першої діапазонної умови зазвичай не звужують діапазон пошуку так само ефективно. PostgreSQL може перевіряти умову brand_id = 3 під час сканування знайдених записів.
Тому часто краще розташувати колонки з точними умовами перед колонками з діапазонами:
(category_id, brand_id, price)ORDER BYСкладений індекс може допомогти не лише відфільтрувати рядки, а й повернути їх у потрібному порядку.
Нехай є запит:
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;Для нього підходить індекс:
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);PostgreSQL може знайти рядки клієнта 42 і прочитати їх уже в потрібному порядку.
Порядок напрямку має значення для кількох колонок. Наприклад:
CREATE INDEX idx_orders_sort
ON orders (customer_id ASC, created_at DESC);Такий індекс природно відповідає сортуванню:
ORDER BY customer_id ASC, created_at DESCДля простого індексу PostgreSQL може читати його у зворотному напрямку, але це змінює напрямок усіх колонок. Окремо задані ASC і DESC корисні, коли напрямки для колонок різні.
Наведений приклад створює таблицю подій, додає дані, створює складений індекс і показує план виконання запиту.
DROP TABLE IF EXISTS events;
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id integer NOT NULL,
event_type text NOT NULL,
created_at timestamp NOT NULL,
payload text NOT NULL
);
INSERT INTO events (tenant_id, event_type, created_at, payload)
SELECT
(n % 20) + 1,
CASE
WHEN n % 10 = 0 THEN 'error'
WHEN n % 3 = 0 THEN 'warning'
ELSE 'info'
END,
timestamp '2026-01-01 00:00:00'
+ (n % 365) * interval '1 day',
'Подія ' || n
FROM generate_series(1, 100000) AS numbers(n);
CREATE INDEX idx_events_tenant_type_created
ON events (tenant_id, event_type, created_at);
ANALYZE events;
EXPLAIN (COSTS OFF)
SELECT id, event_type, created_at
FROM events
WHERE tenant_id = 7
AND event_type = 'error'
AND created_at >= timestamp '2026-06-01'
ORDER BY created_at;Оскільки план залежить від версії PostgreSQL, статистики та параметрів сервера, точний результат може відрізнятися. Для великої таблиці план часто міститиме Index Scan або Bitmap Index Scan.
Той самий індекс не обов’язково буде оптимальним для запиту:
SELECT id, tenant_id, event_type
FROM events
WHERE event_type = 'error';У ньому немає умови за першою колонкою tenant_id. PostgreSQL може виконати послідовне сканування або використати інший індекс, якщо він існує.
Для перевірки плану використовуйте:
EXPLAIN
SELECT *
FROM events
WHERE tenant_id = 7
AND event_type = 'error';Щоб побачити фактичний час і кількість прочитаних рядків:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM events
WHERE tenant_id = 7
AND event_type = 'error';EXPLAIN ANALYZE справді виконує запит. Для SELECT це безпечно з погляду зміни даних, але для INSERT, UPDATE або DELETE потрібно враховувати реальне виконання операції.
Під час аналізу звертайте увагу на:
чи використовується очікуваний індекс;
скільки рядків планувальник очікував знайти;
скільки рядків було знайдено фактично;
чи не читається майже вся таблиця;
чи не є послідовне сканування дешевшим для цього запиту.
Наприклад, для невеликої таблиці Seq Scan може бути правильним планом. Індекс не завжди швидший, оскільки перехід між індексом і таблицею також має вартість.
Припустімо, що є такі запити:
-- Пошук подій конкретного орендаря
SELECT *
FROM events
WHERE tenant_id = 7;
-- Пошук помилок конкретного орендаря
SELECT *
FROM events
WHERE tenant_id = 7
AND event_type = 'error';
-- Пошук усіх помилок
SELECT *
FROM events
WHERE event_type = 'error';Індекс:
(tenant_id, event_type)добре підходить для перших двох запитів.
Індекс:
(event_type, tenant_id)підходить для другого і третього запитів, але не є таким природним вибором для пошуку лише за tenant_id.
Якщо важливі всі три сценарії, одного складеного індексу може бути недостатньо. Потрібно оцінити реальне навантаження і, за потреби, створити окремий індекс для найважливішого запиту.
Складений індекс не замінює всі окремі індекси.
Наприклад:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);не є повною заміною індексу:
CREATE INDEX idx_orders_status
ON orders (status);Якщо часто виконується запит лише за status, окремий індекс може бути потрібним.
PostgreSQL іноді комбінує кілька індексів за допомогою операцій BitmapAnd або BitmapOr. Наприклад, він може об’єднати окремі індекси для умов:
WHERE customer_id = 42
AND status = 'paid';Проте це не означає, що завжди слід створювати окремі індекси замість складеного. Вибір потрібно перевіряти на реальних запитах за допомогою EXPLAIN (ANALYZE, BUFFERS).
Індекси:
(a, b)і:
(b, a)не взаємозамінні. Порядок має відповідати типовим умовам запитів.
Індекс на великій кількості колонок:
(a, b, c, d, e, f)може:
займати багато місця;
сповільнювати INSERT, UPDATE і DELETE;
бути корисним лише для запитів, які починаються з a.
Додавайте лише ті колонки, які потрібні важливим запитам.
Для запиту:
WHERE tenant_id = 7
AND event_type = 'error'
AND created_at >= timestamp '2026-06-01'індекс:
(created_at, tenant_id, event_type)зазвичай гірший за:
(tenant_id, event_type, created_at)У другому варіанті точні умови звужують пошук перед переходом до діапазону дат.
Планувальник може вибрати Seq Scan, якщо:
таблиця маленька;
умова повертає велику частину таблиці;
статистика застаріла;
послідовне читання дешевше;
індекс не відповідає формі запиту.
Теоретично правильний порядок колонок ще не доводить практичну ефективність. Перевіряйте запит за допомогою:
EXPLAIN (ANALYZE, BUFFERS)і використовуйте дані, схожі на виробничі.
Складений індекс містить кілька колонок у визначеному порядку.
Для B-tree-індексів важливе правило лівого префікса.
Індекс (a, b) найкраще працює, коли запит використовує a, а ще краще — a і b.
Для запитів із точними умовами та діапазоном зазвичай розміщуйте колонки з = перед діапазонною колонкою.
Якщо кілька колонок використовуються через =, їхній порядок потрібно визначати з урахуванням інших запитів і ORDER BY.
Складений індекс може допомогти як фільтрації, так і сортуванню.
Остаточне рішення потрібно перевіряти за допомогою EXPLAIN (ANALYZE, BUFFERS).
Не створюйте індекси без урахування реальних запитів і вартості підтримки індексу.