Пошук уроків, статей та іншого контенту
Розберіть принцип роботи Index Scan, його вартість і умови ефективного використання.
Index Scan — це спосіб виконання запиту, за якого PostgreSQL спочатку знаходить потрібні значення в індексі, а потім отримує відповідні рядки з таблиці.
Без індексу PostgreSQL зазвичай змушений переглянути всю таблицю:
SELECT *
FROM users
WHERE email = 'anna@example.com';Якщо в таблиці мільйони рядків, такий пошук може бути повільним. Індекс дає змогу швидше знайти позицію потрібного рядка.
Типова послідовність роботи Index Scan:
PostgreSQL відкриває індекс.
Знаходить у ньому значення, яке відповідає умові WHERE.
Отримує адресу потрібного рядка в таблиці.
Читає рядок із таблиці.
Повертає результат запиту.
Найчастіше для цього використовується індекс типу B-tree, який є стандартним типом індексу в PostgreSQL.
Створимо тимчасову таблицю з великою кількістю рядків:
CREATE TEMP TABLE orders (
order_id integer,
customer_id integer,
status text,
amount numeric
);
-- Додаємо тестові замовлення
INSERT INTO orders (order_id, customer_id, status, amount)
SELECT
number,
number,
CASE
WHEN number % 3 = 0 THEN 'paid'
WHEN number % 3 = 1 THEN 'new'
ELSE 'cancelled'
END,
(number % 1000) + 0.99
FROM generate_series(1, 200000) AS number;
-- Створюємо індекс для пошуку за customer_id
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);
-- Оновлюємо статистику таблиці
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 12345;У плані виконання можна побачити приблизно таку структуру:
Index Scan using orders_customer_id_idx on orders
Index Cond: (customer_id = 12345)Назва індексу може відрізнятися, а числові значення вартості та часу залежать від конкретного середовища.
Рядок Index Scan using orders_customer_id_idx означає, що PostgreSQL використав індекс orders_customer_id_idx.
Рядок:
Index Cond: (customer_id = 12345)показує умову, яку PostgreSQL зміг виконати безпосередньо через індекс.
EXPLAIN та EXPLAIN ANALYZEДля перегляду плану запиту використовують EXPLAIN:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 12345;EXPLAIN лише будує план і не виконує сам запит.
Для перевірки фактичного виконання використовують EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 12345;EXPLAIN ANALYZE дійсно виконує запит і додає до плану фактичні показники:
скільки рядків було оцінено;
скільки рядків повернуто;
скільки часу зайняло виконання;
скільки разів виконувався кожен вузол плану.
Наприклад:
Index Scan using orders_customer_id_idx on orders
(cost=0.42..8.44 rows=1 width=...)
(actual time=... rows=1 loops=1)
Index Cond: (customer_id = 12345)У плані PostgreSQL вартість записується у форматі:
cost=startup_cost..total_costНаприклад:
cost=0.42..8.44startup_costЦе приблизна вартість підготовки до повернення першого рядка.
Для Index Scan вона включає, зокрема:
пошук потрібної частини індексу;
підготовку доступу до таблиці.
total_costЦе приблизна загальна вартість отримання всіх рядків, які поверне цей вузол.
Вартість PostgreSQL не є часом у мілісекундах. Це внутрішня оцінка планувальника, яка допомагає порівнювати різні плани.
Наприклад, PostgreSQL може порівнювати:
послідовне читання всієї таблиці;
пошук через індекс;
інші доступні варіанти виконання.
Планувальник обирає не обов’язково план з найменшим фактичним часом, а план із найменшою оціненою вартістю.
Index Scan зазвичай добре працює, коли запит повертає невелику частину таблиці.
Наприклад:
SELECT *
FROM orders
WHERE customer_id = 12345;Якщо умові відповідає один або кілька рядків, PostgreSQL:
швидко знаходить їх в індексі;
читає лише потрібні рядки таблиці.
Індекс особливо корисний для:
пошуку за унікальним значенням;
пошуку за первинним ключем;
пошуку невеликої кількості рядків;
умов з операторами =, <, <=, >, >=;
сортування або обмеження результату в запитах, для яких індекс відповідає потрібному порядку.
Приклад пошуку за діапазоном:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id BETWEEN 1000 AND 1010;B-tree-індекс може швидко знайти початок діапазону та послідовно пройти потрібні значення.
Наявність індексу не означає, що PostgreSQL використовуватиме його в кожному запиті.
Для маленької таблиці послідовне читання може бути дешевшим за звернення до індексу.
Використання індексу має власні витрати:
потрібно прочитати індекс;
потім перейти до таблиці;
отримати рядок із таблиці.
Якщо таблиця вміщується на кількох сторінках, простіше прочитати її повністю.
Розглянемо запит:
SELECT *
FROM orders
WHERE customer_id > 10;Якщо умові відповідає більшість рядків, Index Scan може бути невигідним. PostgreSQL довелося б багато разів переходити з індексу до таблиці.
У такій ситуації послідовне читання таблиці може бути швидшим.
Планувальник використовує статистику, щоб оцінити кількість рядків, які відповідають умові.
Після значних змін у таблиці статистику потрібно оновити:
ANALYZE orders;Зазвичай PostgreSQL виконує ANALYZE автоматично через autovacuum, але іноді після великого імпорту даних корисно запустити його вручну.
Якщо індекс створено для customer_id, він не допоможе напряму для пошуку за іншим стовпцем:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);
SELECT *
FROM orders
WHERE status = 'paid';Для такого пошуку потрібен індекс, що відповідає умові:
CREATE INDEX orders_status_idx
ON orders (status);Однак рішення про створення індексу потрібно приймати з урахуванням реальних запитів і розподілу значень. Індекс на стовпці з дуже малою кількістю різних значень не завжди буде ефективним.
WHEREЩоб PostgreSQL міг використати індекс, умова повинна бути придатною для пошуку через цей індекс.
Індекс для customer_id добре підходить для такого запиту:
SELECT *
FROM orders
WHERE customer_id = 12345;Також для діапазону:
SELECT *
FROM orders
WHERE customer_id >= 10000
AND customer_id < 10100;Але обробка значення функцією може змінити ситуацію:
SELECT *
FROM orders
WHERE customer_id::text = '12345';Тут стовпець перетворюється на текст, а звичайний індекс на customer_id не обов’язково можна використати для такого порівняння.
Краще порівнювати значення з відповідним типом:
SELECT *
FROM orders
WHERE customer_id = 12345;Звичайний Index Scan використовує індекс для пошуку, але самі дані часто читає з таблиці.
Індекс містить:
значення індексованого стовпця;
посилання на відповідний рядок таблиці.
Тому для запиту:
SELECT *
FROM orders
WHERE customer_id = 12345;PostgreSQL знаходить customer_id в індексі, а потім звертається до таблиці, щоб отримати status, amount та інші стовпці.
Це пояснює, чому Index Scan може бути невигідним, якщо потрібно повернути дуже багато рядків: доводиться виконувати багато звернень до таблиці.
Для перевірки плану:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 12345;Шукайте у результаті назву:
Index Scanі рядок:
Index CondДля перевірки фактичного виконання:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 12345;Опція BUFFERS показує інформацію про читання сторінок даних. Вона допомагає зрозуміти, скільки даних PostgreSQL читав із кешу або диска.
Не потрібно вимагати Index Scan для кожного запиту. Якщо PostgreSQL обрав Seq Scan, це може бути правильним рішенням для конкретного обсягу даних і конкретної умови.
Індекс — це інструмент, а не гарантія певного плану. Планувальник може обрати послідовне читання, якщо воно дешевше.
Значення cost=... не є мілісекундами. Для фактичного часу потрібно дивитися на actual time у результаті EXPLAIN ANALYZE.
Після великої зміни даних оцінки планувальника можуть бути неточними. У такому випадку виконайте:
ANALYZE orders;Індекс займає місце та потребує оновлення під час INSERT, UPDATE і DELETE. Його варто створювати для умов, які дійсно використовуються в запитах.
Індекс на одному стовпці не допомагає автоматично для всіх умов, функцій і перетворень типів. Потрібно перевіряти фактичний план через EXPLAIN.
Index Scan знаходить потрібні рядки через індекс, а потім читає їх із таблиці.
Він найефективніший, коли запит повертає невелику частину даних.
Вартість у EXPLAIN є оцінкою PostgreSQL, а не часом у мілісекундах.
EXPLAIN ANALYZE показує фактичний результат виконання запиту.
PostgreSQL може обрати Seq Scan, якщо таблиця мала або умова повертає багато рядків.
Актуальна статистика допомагає планувальнику правильно оцінювати Index Scan.
Наявність індексу не гарантує його використання — завжди перевіряйте план конкретного запиту.