Пошук уроків, статей та іншого контенту
Створите часткові індекси для підмножин рядків і розберете вимоги до відповідності предиката запиту.
Частковий індекс — це індекс, який містить не всі рядки таблиці, а лише рядки, що відповідають певному предикату.
CREATE INDEX index_name
ON table_name (column_name)
WHERE condition;Наприклад, у таблиці замовлень більшість записів уже завершені, а застосунок часто шукає лише активні замовлення:
CREATE INDEX orders_active_created_at_idx
ON orders (created_at)
WHERE status = 'active';Такий індекс міститиме лише рядки, для яких status = 'active'.
Частковий індекс може бути корисним, якщо:
потрібна підмножина рядків значно менша за всю таблицю;
запити регулярно використовують умову, що відповідає предикату індексу;
потрібно зменшити розмір індексу;
потрібно зменшити витрати на оновлення індексу для рядків, які не входять до підмножини.
Розглянемо таблицю заявок:
CREATE TABLE support_tickets (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
status text NOT NULL,
priority integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
resolved_at timestamptz
);
INSERT INTO support_tickets (
customer_id,
status,
priority,
created_at,
resolved_at
)
SELECT
(random() * 10000)::bigint,
CASE
WHEN number <= 95000 THEN 'resolved'
WHEN number <= 99000 THEN 'open'
ELSE 'pending'
END,
(random() * 5 + 1)::integer,
now() - (random() * interval '365 days'),
CASE
WHEN number <= 95000
THEN now() - (random() * interval '180 days')
ELSE NULL
END
FROM generate_series(1, 100000) AS numbers(number);
ANALYZE support_tickets;Припустімо, що застосунок часто виконує такий запит:
SELECT id, customer_id, priority, created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 50;Створимо індекс лише для відкритих заявок:
CREATE INDEX support_tickets_open_created_at_idx
ON support_tickets (created_at DESC)
WHERE status = 'open';Тепер індекс містить лише заявки зі статусом open. Завершені та очікувані заявки до нього не потрапляють.
Предикат часткового індексу називають предикатом індексу. У попередньому прикладі це:
status = 'open'Щоб PostgreSQL міг використати частковий індекс, він має довести, що умова запиту гарантує виконання предиката індексу.
Наприклад, цей запит відповідає предикату:
SELECT id, customer_id, priority, created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 50;Умова status = 'open' означає, що всі рядки результату присутні в частковому індексі.
Перевірити план можна за допомогою EXPLAIN:
EXPLAIN
SELECT id, customer_id, priority, created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 50;Після оновлення статистики та за достатньої вибірковості в плані може з’явитися Index Scan або Index Only Scan з індексом:
Index Scan using support_tickets_open_created_at_idxТочний план залежить від розміру таблиці, статистики, вартості операцій і налаштувань PostgreSQL. Сам факт створення індексу не гарантує його використання в кожному запиті.
Предикат запиту має бути достатньо сильним, щоб із нього випливав предикат індексу.
Якщо індекс створено так:
CREATE INDEX support_tickets_open_created_at_idx
ON support_tickets (created_at DESC)
WHERE status = 'open';то PostgreSQL може використати його для запиту:
SELECT *
FROM support_tickets
WHERE status = 'open'
AND priority >= 4;Тут кожен рядок із результату має status = 'open', тому всі такі рядки можуть бути в індексі.
Також умова може бути записана через еквівалентний діапазон, якщо PostgreSQL може довести відповідність:
SELECT *
FROM support_tickets
WHERE status = 'open'
AND created_at >= now() - interval '1 day';Додаткова умова created_at >= ... не порушує відповідність: запит усе одно повертає лише відкриті заявки.
Водночас цей запит не має достатньої умови:
SELECT *
FROM support_tickets
WHERE priority >= 4;Він може повертати заявки з будь-яким статусом. PostgreSQL не може обмежити пошук лише частковим індексом для status = 'open', оскільки це змінило б результат.
На практиці найнадійніше створювати частковий індекс із тією самою формою умови, яку використовують запити.
Наприклад:
CREATE INDEX orders_unbilled_idx
ON orders (customer_id)
WHERE billed IS NOT TRUE;Запит із такою самою умовою добре відповідає предикату:
SELECT *
FROM orders
WHERE billed IS NOT TRUE
AND customer_id = 42;Але інша форма перевірки може бути менш очевидною для планувальника:
SELECT *
FROM orders
WHERE billed = false
AND customer_id = 42;Для значення boolean умови billed IS NOT TRUE та billed = false поводяться по-різному щодо NULL:
billed = false істинне лише для false;
billed IS NOT TRUE істинне для false і NULL.
Тому це не просто питання синтаксису. Потрібно, щоб предикат індексу точно відповідав бізнес-умові та умовам запитів.
Часткові індекси часто використовують для числових або часових діапазонів.
CREATE INDEX products_available_price_idx
ON products (price)
WHERE available = true;Запит:
SELECT id, name, price
FROM products
WHERE available = true
AND price BETWEEN 100 AND 500
ORDER BY price;може використовувати індекс, оскільки умова available = true присутня в запиті.
Інший приклад:
CREATE INDEX events_recent_idx
ON events (created_at)
WHERE created_at >= DATE '2026-01-01';Такий предикат має обмеження: вираз індексу повинен бути стабільним. Не можна безпосередньо створити предикат, який змінюється з часом, наприклад:
-- Такий підхід непридатний для поточного часу:
CREATE INDEX events_recent_idx
ON events (created_at)
WHERE created_at >= now() - interval '30 days';Індекс не оновлюється автоматично лише тому, що минув час. Рядок, який сьогодні є «свіжим», через 30 днів не зникне з індексу сам по собі.
Для змінних часових меж зазвичай використовують звичайний індекс на created_at і умову в запиті. Частковий індекс доречніший для властивості, яка змінюється разом із рядком, наприклад status, is_active або deleted_at IS NULL.
Часткові індекси можуть не використовуватися для параметризованих умов, якщо PostgreSQL не може довести потрібну відповідність під час побудови плану.
Наприклад:
PREPARE find_tickets(text) AS
SELECT *
FROM support_tickets
WHERE status = $1;
EXECUTE find_tickets('open');Планувальник має враховувати, що параметр $1 може мати будь-яке значення. Загалом із умови status = $1 не випливає, що status = 'open', тому частковий індекс для відкритих заявок може не бути придатним.
Натомість параметр можна застосовувати до іншої умови, якщо предикат часткового індексу заданий явно:
PREPARE find_open_tickets(integer) AS
SELECT *
FROM support_tickets
WHERE status = 'open'
AND priority >= $1;
EXECUTE find_open_tickets(4);Тут умова status = 'open' відома планувальнику незалежно від значення параметра.
Це особливо важливо для застосунків, які використовують підготовлені або параметризовані запити. Умова часткового індексу має бути присутня в тексті запиту як гарантована умова, а не лише передаватися як довільне значення параметра.
Частковий індекс може бути унікальним. Тоді унікальність контролюється лише для рядків, що відповідають предикату.
Наприклад, користувач може мати багато старих електронних адрес, але лише одну активну:
CREATE TABLE user_emails (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL,
email text NOT NULL,
is_active boolean NOT NULL DEFAULT true
);
CREATE UNIQUE INDEX user_emails_one_active_idx
ON user_emails (user_id)
WHERE is_active = true;Цей індекс забороняє додати дві активні адреси для одного user_id:
INSERT INTO user_emails (user_id, email, is_active)
VALUES (10, 'first@example.com', true);
-- Помилка порушення унікальності:
INSERT INTO user_emails (user_id, email, is_active)
VALUES (10, 'second@example.com', true);Неактивні записи не беруть участі в цьому обмеженні:
INSERT INTO user_emails (user_id, email, is_active)
VALUES (10, 'old@example.com', false);Унікальний частковий індекс — це не те саме, що глобальне обмеження UNIQUE для всієї таблиці. Він контролює лише рядки, які входять до його предиката.
PostgreSQL підтримує частковий індекс відповідно до поточного стану рядка.
Якщо рядок змінюється так, що починає відповідати предикату, PostgreSQL додає його до індексу:
UPDATE support_tickets
SET status = 'open'
WHERE id = 15;Якщо рядок перестає відповідати предикату, PostgreSQL видаляє його запис із часткового індексу:
UPDATE support_tickets
SET status = 'resolved',
resolved_at = now()
WHERE id = 15;Тому частковий індекс не означає, що оновлення відповідних рядків є безкоштовними. Проте рядки, які ніколи не входять до підмножини, не створюють записів у цьому індексі.
Предикат визначає, які рядки потрапляють до індексу, а список колонок після назви таблиці визначає, за якими значеннями ці рядки індексуються.
CREATE INDEX support_tickets_open_priority_idx
ON support_tickets (priority, created_at DESC)
WHERE status = 'open';Цей індекс:
містить лише відкриті заявки;
спочатку сортує їх за priority;
далі — за created_at у зворотному порядку.
Запит має відповідати і предикату, і структурі доступу:
SELECT id, customer_id, priority, created_at
FROM support_tickets
WHERE status = 'open'
AND priority = 5
ORDER BY created_at DESC;Індекс може допомогти знайти лише відкриті заявки з найвищим пріоритетом і повернути їх у потрібному порядку.
Порядок колонок індексу має відповідати найчастішим умовам фільтрації, сортування та з’єднання. Частковий індекс не замінює вибір правильних ключових колонок.
Для перевірки плану використовуйте EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, priority, created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 50;EXPLAIN ANALYZE фактично виконує запит, тому не використовуйте його безпосередньо для операцій, що змінюють дані, якщо це не передбачено тестовим середовищем.
Під час перевірки звертайте увагу на:
назву використаного індексу;
тип операції: Index Scan, Index Only Scan або інший;
кількість фактично прочитаних рядків;
наявність зайвого Filter;
фактичний час виконання;
різницю між оціненими та фактичними кількостями рядків.
Створення індексу не гарантує покращення. Якщо предикат охоплює майже всю таблицю або результат запиту все одно великий, звичайний індекс чи послідовне сканування може бути ефективнішим.
CREATE INDEX orders_unbilled_idx
ON orders (customer_id)
WHERE billed IS NOT TRUE;Але запити виконуються так:
SELECT *
FROM orders
WHERE customer_id = 42;Такий запит може повертати і оплачені, і неоплачені замовлення. Предикат часткового індексу не гарантований, тому індекс не підходить для повного результату.
Якщо 99% рядків відповідають предикату, індекс майже такий самий великий, як звичайний. У такому разі перевага в розмірі та вартості обслуговування буде незначною.
NULLУмови з NULL потрібно проєктувати уважно:
WHERE deleted_at IS NULLце не те саме, що:
WHERE deleted_at = NULLПорівняння = NULL не повертає true. Для перевірки NULL використовуйте IS NULL або IS NOT NULL.
Предикат на кшталт «останні 30 днів від поточного моменту» не підтримується як автоматично рухоме вікно. Індекс не перебудовує свій склад лише через плин часу.
Планувальник може обрати послідовне сканування, якщо воно дешевше. Рішення залежить від статистики та розподілу даних, тому корисність слід перевіряти через EXPLAIN (ANALYZE, BUFFERS).
Знайдіть запити, які часто виконуються.
Визначте стабільну підмножину рядків, потрібну цим запитам.
Переконайтеся, що умова цієї підмножини присутня в запитах.
Виберіть колонки для пошуку або сортування.
Створіть частковий індекс.
Оновіть статистику командою ANALYZE.
Перевірте реальний план через EXPLAIN (ANALYZE, BUFFERS).
Перевірте запити з параметрами, якщо застосунок використовує підготовлені оператори.
Частковий індекс містить лише рядки, що відповідають умові WHERE.
Він корисний для невеликої, часто використовуваної підмножини таблиці.
Запит має гарантувати предикат часткового індексу, інакше індекс може бути непридатним.
Додаткові умови запиту дозволені, якщо вони не порушують логічного включення.
У параметризованих запитах предикат індексу краще вказувати явно.
NULL, форма логічного виразу та відповідність діапазонів мають значення.
Унікальний частковий індекс забезпечує унікальність лише для рядків своєї підмножини.
Реальну користь індексу потрібно перевіряти за допомогою EXPLAIN (ANALYZE, BUFFERS).