Пошук уроків, статей та іншого контенту
Сформуєте алгоритм вибору методу, колонок і операторного класу на основі запитів та розподілу даних.
Правильний індекс визначається не лише типом колонки. Потрібно врахувати:
оператори у WHERE;
умови JOIN;
ORDER BY і LIMIT;
кількість різних значень;
розподіл значень;
кореляцію даних із фізичним порядком рядків;
частоту виконання запиту;
обсяг таблиці;
вартість підтримки індексу під час INSERT, UPDATE і DELETE.
Одна й та сама колонка може потребувати різних індексів для різних типів запитів.
Наприклад:
WHERE email = 'user@example.com'і
WHERE description LIKE '%database%'використовують різні механізми пошуку. Для першого випадку зазвичай підходить B-tree, а для другого — індекс на основі триграм.
Зручно рухатися за таким алгоритмом:
Визначити форму предиката:
рівність;
діапазон;
сортування;
префіксний пошук;
пошук за масивом, jsonb або діапазоном;
просторовий або спеціалізований пошук.
Оцінити розподіл даних:
кількість різних значень;
частку рядків, які повертає запит;
наявність популярних значень;
кореляцію з фізичним порядком рядків.
Обрати метод доступу.
Визначити порядок колонок у складеному індексі.
Обрати операторний клас, якщо стандартний не відповідає операції.
Перевірити план через EXPLAIN (ANALYZE, BUFFERS).
Перевірити не лише один запит, а всю робочу групу запитів.
Індекс не є корисним автоматично лише тому, що його можна створити.
B-tree — стандартний вибір для:
=;
<, <=, >, >=;
діапазонів;
ORDER BY;
префіксного пошуку LIKE 'prefix%' за відповідного операторного класу;
перевірки IS NULL та IS NOT NULL у багатьох практичних випадках;
умов, які комбінуються з LIMIT.
Приклад:
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);Такий індекс може бути корисним для запиту:
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;B-tree зручний тому, що підтримує одночасно пошук і порядок результатів. Планувальник може уникнути окремого сортування.
Однак B-tree не є універсальним. Він не є хорошим вибором для пошуку підрядка:
WHERE description LIKE '%database%'Hash оптимізований для рівності:
WHERE customer_id = 42Приклад:
CREATE INDEX orders_customer_hash_idx
ON orders USING hash (customer_id);На практиці B-tree часто залишається кращим загальним вибором навіть для =:
він підтримує більше операторів;
він може використовуватися для сортування;
він підтримує діапазони;
він краще комбінується з іншими умовами.
Hash варто розглядати лише тоді, коли індекс точно використовується для рівності, а його характеристики справді вигідні для конкретного навантаження. Не слід створювати Hash лише через наявність оператора =.
GIN призначений для значень, які містять набір елементів:
масиви;
jsonb;
повнотекстовий пошук;
триграмні індекси для пошуку тексту.
GIN особливо корисний, коли один рядок може відповідати пошуку за багатьма внутрішніми елементами.
Приклад для jsonb:
CREATE INDEX events_payload_gin_idx
ON events USING gin (payload);Такий індекс може підтримувати, зокрема, умову:
WHERE payload @> '{"type": "payment"}'GIN зазвичай дорожчий у побудові та підтримці, ніж B-tree. Його не варто створювати для простого пошуку рівності у звичайній колонці.
GiST — узагальнений метод для структур, де важливі відношення між значеннями:
діапазони;
геометричні типи;
деякі спеціалізовані пошукові структури;
оператори перетину, включення та близькості.
Для діапазонів:
CREATE INDEX reservations_period_gist_idx
ON reservations USING gist (reserved_period);Індекс може бути корисним для запиту:
SELECT *
FROM reservations
WHERE reserved_period && tstzrange(
'2026-09-01 10:00+00',
'2026-09-01 11:00+00'
);Оператор && перевіряє перетин діапазонів. Звичайний B-tree не моделює таку операцію належним чином.
SP-GiST підходить для структур із природним розбиттям простору або значень на неперекривні області. Він може бути корисним для окремих геометричних і спеціалізованих типів даних.
Його не слід вибирати лише як альтернативу GiST. Потрібно перевірити, чи відповідає конкретний тип даних і оператори підтримуваному класу.
BRIN зберігає узагальнену інформацію про діапазони фізичних сторінок таблиці, а не окремий запис для кожного рядка.
Він добре підходить для:
дуже великих таблиць;
append-only або майже append-only даних;
колонок, значення яких корелюють із фізичним порядком рядків;
часових колонок у таблицях, куди дані додаються послідовно.
Приклад:
CREATE INDEX logs_created_at_brin_idx
ON logs USING brin (created_at);BRIN може бути набагато меншим за B-tree, але він не забезпечує таку саму точність пошуку. Якщо рядки з близькими значеннями розподілені випадково по всій таблиці, BRIN втрачає перевагу.
Селективність показує, яку частку таблиці повертає умова.
Умови з високою селективністю повертають мало рядків:
WHERE order_id = 987654Умови з низькою селективністю повертають значну частину таблиці:
WHERE status = 'active'Якщо запит повертає більшість рядків, послідовне читання таблиці часто дешевше за:
читання індексу;
перехід від індексу до таблиці для кожного рядка;
випадкове читання великої кількості сторінок.
Тому індекс на колонці з двома значеннями не обов’язково марний, але його корисність залежить від конкретного предиката. Наприклад, частковий індекс для рідкісного статусу може бути ефективним:
CREATE INDEX orders_pending_idx
ON orders (created_at)
WHERE status = 'pending';Цей індекс менший за повний індекс і призначений для запитів, які містять сумісну умову:
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;Планувальник повинен мати змогу довести, що умова запиту гарантує предикат часткового індексу. Надто складні або непрямі вирази можуть завадити такому доведенню.
Планувальник використовує статистику, зібрану командою ANALYZE.
ANALYZE orders;Для перегляду базової статистики:
SELECT
attname,
n_distinct,
most_common_vals,
most_common_freqs,
histogram_bounds,
correlation
FROM pg_stats
WHERE tablename = 'orders'
AND attname IN ('customer_id', 'status', 'created_at');Важливі поля:
n_distinct — оцінка кількості різних значень;
most_common_vals — найпоширеніші значення;
most_common_freqs — частоти найпоширеніших значень;
histogram_bounds — приблизний розподіл значень;
correlation — кореляція логічного порядку значень із фізичним порядком рядків.
Висока за модулем кореляція може бути важливою для BRIN і для оцінки вартості читання індексу.
Якщо розподіл нерівномірний, середня селективність може бути оманливою. Запит для рідкісного значення і запит для популярного значення тієї самої колонки можуть мати різні оптимальні плани.
Для колонок із нерівномірним розподілом можна збільшити статистичну ціль:
ALTER TABLE orders
ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;Це збільшує обсяг статистики, але також підвищує вартість ANALYZE.
Для індексу:
CREATE INDEX orders_customer_status_created_idx
ON orders (customer_id, status, created_at DESC);порядок колонок має значення.
Зазвичай спочатку розміщують колонки з умовами рівності, а після них — колонку діапазону або сортування:
WHERE customer_id = 42
AND status = 'paid'
AND created_at >= now() - interval '30 days'Для такого запиту порядок є природним:
customer_id → status → created_atІндекс також може бути корисним для запитів за лівим префіксом:
WHERE customer_id = 42Але індекс із першою колонкою customer_id не є рівнозначним індексу, який починається зі status.
Важливо розрізняти:
колонки ключа індексу — беруть участь у пошуку та порядку;
колонки INCLUDE — зберігаються в індексі для потенційного index-only scan, але не визначають порядок пошуку.
Приклад:
CREATE INDEX orders_customer_created_covering_idx
ON orders (customer_id, created_at DESC)
INCLUDE (total, status);Такий індекс може повернути total і status без звернення до heap, якщо умови видимості дозволяють index-only scan. Проте INCLUDE не допомагає знайти рядки за status.
Операторний клас визначає, як конкретний тип даних інтерпретується певним методом індексації та які оператори він підтримує.
Тип колонки сам по собі не визначає оптимальний операторний клас.
Для запиту:
WHERE username LIKE 'admin%'B-tree може бути придатним, але для баз із локаллю, де порядок сортування не відповідає побайтовому порядку, часто потрібен text_pattern_ops:
CREATE INDEX users_username_pattern_idx
ON users (username text_pattern_ops);Цей індекс призначений саме для порівняння текстових значень за шаблоном. Якщо той самий стовпець також використовується для звичайного сортування відповідно до локалі, може знадобитися окремий індекс зі стандартним операторним класом.
Для varchar існує відповідний клас varchar_pattern_ops.
Префіксний пошук:
WHERE username LIKE 'admin%'відрізняється від пошуку підрядка:
WHERE username LIKE '%min%'text_pattern_ops не перетворює другий запит на ефективний пошук у B-tree.
jsonbДля jsonb стандартний GIN-клас jsonb_ops підтримує ширший набір операторів, зокрема пошук існування ключів.
CREATE INDEX events_payload_ops_idx
ON events USING gin (payload jsonb_ops);Якщо застосунок переважно виконує пошук вкладених структур за @>, @? або @@, можна розглянути jsonb_path_ops:
CREATE INDEX events_payload_path_idx
ON events USING gin (payload jsonb_path_ops);jsonb_path_ops зазвичай спеціалізованіший і не є взаємозамінним із jsonb_ops. Перед вибором потрібно перелічити фактичні оператори запитів.
Для пошуку довільного підрядка або схожості тексту можна використати розширення pg_trgm і GIN або GiST:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm_idx
ON products USING gin (name gin_trgm_ops);Після цього індекс може бути використаний для запиту:
SELECT id, name
FROM products
WHERE name ILIKE '%database%';Такий індекс не є заміною text_pattern_ops: він вирішує іншу задачу — пошук підрядка, а не лише префікса.
Корисна початкова відповідність виглядає так:
точна рівність звичайного значення — B-tree;
діапазон або сортування — B-tree;
рівність без потреби в сортуванні — зазвичай спочатку розглянути B-tree, а не автоматично Hash;
префіксний текстовий пошук — B-tree з відповідним операторним класом;
пошук довільного підрядка — GIN або GiST із gin_trgm_ops чи gist_trgm_ops;
масиви та множини — GIN;
jsonb — GIN із класом, що відповідає операторам;
перетин або включення діапазонів — GiST;
великі корельовані таблиці — BRIN;
просторові або спеціалізовані структури — GiST або SP-GiST після перевірки підтримуваних операторів.
Це не замінює вимірювання. Остаточне рішення приймається за планом і фактичним часом виконання.
Наведений приклад створює таблицю замовлень, додає дані з різним розподілом статусів і перевіряє індекс для пошуку останніх замовлень клієнта.
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() * 10000)::integer,
CASE
WHEN random() < 0.02 THEN 'pending'
WHEN random() < 0.12 THEN 'cancelled'
ELSE 'paid'
END,
round((10 + random() * 990)::numeric, 2),
now() - (random() * interval '365 days')
FROM generate_series(1, 200000);
ANALYZE orders;
-- Спочатку фіксуємо рівність за клієнтом, потім порядок за датою.
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- Окремий малий індекс для рідкісного статусу.
CREATE INDEX orders_pending_created_idx
ON orders (created_at DESC)
WHERE status = 'pending';
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;У першому запиті індекс починається з customer_id, бо це точна умова. created_at використовується для порядку та обмеження LIMIT.
У другому запиті частка pending мала. Частковий індекс не містить усі рядки таблиці, тому він може бути значно дешевшим за повний індекс.
Фактичний план залежить від статистики, версії PostgreSQL, обсягу таблиці та налаштувань вартості. Можливими результатами є:
Index Scan;
Bitmap Index Scan разом із Bitmap Heap Scan;
Index Only Scan;
Seq Scan, якщо планувальник вважає його дешевшим.
Seq Scan не означає автоматично помилку. Для великої частки таблиці це часто правильне рішення.
Не слід порівнювати індекси лише за їхнім розміром. Перевіряйте:
фактичний час виконання;
Buffers: shared hit і shared read;
кількість прочитаних і повернутих рядків;
відповідність estimated rows та actual rows;
чи зникло зайве сортування;
чи використано індекс у запитах із реальним розподілом параметрів.
Приклад:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, total
FROM orders
WHERE customer_id = 42
AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 10;Якщо оцінка кількості рядків сильно відрізняється від фактичної, проблема може бути не в індексі, а в застарілій або недостатній статистиці.
Для корельованих умов кількох колонок можна створити розширену статистику:
CREATE STATISTICS orders_customer_status_stats
(dependencies, mcv)
ON customer_id, status
FROM orders;
ANALYZE orders;Це допомагає планувальнику оцінювати спільний розподіл колонок, коли їхні значення не є незалежними.
Кожен індекс має операційну ціну:
займає місце на диску;
збільшує обсяг запису до WAL;
уповільнює вставки;
може уповільнювати оновлення;
потребує обслуговування;
збільшує час побудови та відновлення.
Тому індекс потрібно оцінювати як частину робочого навантаження:
виграш від прискорення читання
мінус
вартість записів, пам’яті та обслуговуванняІндекс, який прискорює рідкісний запит на кілька мілісекунд, може бути невиправданим на таблиці з дуже інтенсивними вставками.
WHEREНаявність колонки в WHERE не гарантує, що для неї потрібен окремий індекс. У складеному запиті важливі комбінації умов, порядок колонок і фактична селективність.
Індекси (a, b) і (b, a) не рівнозначні. Порядок слід обирати за типовою формою запитів:
рівності перед діапазонами;
умови, що визначають групу рядків, перед сортуванням;
часті ліві префікси мають бути підтримані першими колонками.
%substring%Оператор LIKE '%text%' не є префіксним пошуком. Для нього потрібен інший підхід, наприклад триграмний індекс.
jsonb_ops і jsonb_path_ops підтримують різні набори операцій. Так само text_pattern_ops не замінює стандартну підтримку сортування тексту.
BRIN ефективний, коли значення в сусідніх сторінках мають близький діапазон. Якщо дані перемішані випадково, узагальнення сторінок стає занадто широким.
Якщо умова повертає значну частину таблиці, Seq Scan може бути швидшим. Не потрібно примусово змушувати PostgreSQL використовувати індекс.
ANALYZEПлан без фактичного виконання показує оцінки, а не реальну поведінку. Для перевірки потрібно використовувати:
EXPLAIN (ANALYZE, BUFFERS)Запит із ANALYZE виконується, тому його слід обережно застосовувати до UPDATE, DELETE та інших операцій зі зміною даних.
Алгоритм вибору індексу можна звести до кількох правил:
Починайте з фактичних запитів, а не з назв колонок.
Для рівностей, діапазонів і сортування спочатку розглядайте B-tree.
Для наборів, jsonb і триграмного пошуку розглядайте GIN.
Для перетинів діапазонів і спеціальних відношень розглядайте GiST.
Для великих корельованих таблиць перевіряйте BRIN.
Порядок колонок у складеному індексі визначайте за умовами рівності, діапазонами та сортуванням.
Операторний клас обирайте за фактичними операторами, а не лише за типом даних.
Враховуйте реальний розподіл значень і якість статистики.
Для рідкісних підмножин розглядайте часткові індекси.
Перевіряйте рішення через EXPLAIN (ANALYZE, BUFFERS) і вимірюйте вартість індексу для записів.