Пошук уроків, статей та іншого контенту
Дізнаєтеся, як планувальник обирає індекси та які умови запитів дають змогу їх ефективно застосовувати.
Індекс — це окрема структура даних, яка допомагає PostgreSQL швидше знаходити рядки в таблиці.
Без індексу PostgreSQL може перевірити кожен рядок таблиці:
SELECT *
FROM users
WHERE email = 'olena@example.com';Такий спосіб називається послідовним скануванням таблиці, або Seq Scan.
Якщо на стовпці email є індекс, PostgreSQL може спочатку знайти потрібне значення в індексі, а потім звернутися лише до відповідних рядків таблиці.
CREATE INDEX users_email_idx ON users (email);Індекс не зберігає копію всієї таблиці для звичайних запитів. Він зберігає спеціально організовані значення та посилання на рядки таблиці.
PostgreSQL має планувальник запитів. Він аналізує SQL-запит і вибирає план виконання.
Наприклад, планувальник порівнює такі варіанти:
прочитати всю таблицю;
використати індекс;
використати кілька індексів;
виконати з'єднання таблиць у певному порядку;
відсортувати результати або використати вже впорядковані дані індексу.
Планувальник оцінює приблизну вартість кожного варіанта. Вартість не є часом у мілісекундах. Це внутрішня оцінка кількості операцій, читання даних та інших витрат.
Тому наявність індексу не означає, що PostgreSQL використовуватиме його в кожному запиті.
EXPLAINЩоб побачити план виконання запиту, використовуйте EXPLAIN:
EXPLAIN
SELECT *
FROM users
WHERE email = 'olena@example.com';У результаті можна побачити, наприклад:
Index Scan using users_email_idx on usersЦе означає, що PostgreSQL планує використати індекс users_email_idx.
Інший можливий результат:
Seq Scan on usersЦе означає, що планувальник вибрав послідовне читання всієї таблиці.
Для перевірки фактичного виконання використовуйте EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'olena@example.com';EXPLAIN ANALYZE справді виконує запит і показує фактичну кількість рядків та час виконання.
Для SELECT це безпечно. Для UPDATE, DELETE або інших запитів зі змінами даних потрібно бути уважними: EXPLAIN ANALYZE виконає ці зміни.
Нижче наведено приклад, який можна виконати в PostgreSQL.
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
country text NOT NULL,
created_at date NOT NULL
);
INSERT INTO users (email, country, created_at)
SELECT
'user' || number || '@example.com',
CASE
WHEN number % 3 = 0 THEN 'UA'
WHEN number % 3 = 1 THEN 'PL'
ELSE 'DE'
END,
DATE '2020-01-01' + (number % 2000)
FROM generate_series(1, 10000) AS numbers(number);
CREATE INDEX users_email_idx ON users (email);
-- Оновлюємо статистику таблиці для планувальника
ANALYZE users;
-- Перевіряємо план пошуку за індексованим стовпцем
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'user5000@example.com';
-- Умова повертає багато рядків, тому планувальник
-- може вибрати послідовне сканування
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE country = 'UA';Для пошуку конкретної електронної адреси індекс зазвичай є корисним, адже результат містить дуже мало рядків.
Для пошуку всіх користувачів із країною UA індекс на country не створено. Навіть якби він існував, результат міг би містити значну частину таблиці, тому послідовне читання могло б бути вигіднішим.
B-tree — тип індексу за замовчуванням у PostgreSQL. Він добре підходить для умов:
WHERE email = 'olena@example.com'Також він підходить для порівнянь:
WHERE created_at >= DATE '2025-01-01'WHERE price > 100WHERE id BETWEEN 100 AND 200Індекс B-tree зберігає значення у впорядкованому вигляді. Тому він може допомогти із сортуванням:
SELECT *
FROM users
ORDER BY created_at;Особливо корисно, коли запит повертає невелику кількість перших рядків:
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 20;Для такого запиту PostgreSQL може прочитати перші потрібні значення з індексу, не сортуючи всю таблицю.
Індекси добре працюють із пошуком діапазону:
SELECT *
FROM users
WHERE created_at >= DATE '2024-01-01'
AND created_at < DATE '2025-01-01';Планувальник може знайти в індексі початок діапазону та послідовно прочитати потрібну його частину.
B-tree-індекс може використовуватися для пошуку рядків, які починаються з певного тексту:
SELECT *
FROM users
WHERE email LIKE 'user50%';Тут відомий початок значення, тому індекс може обмежити діапазон пошуку.
Умова з шаблоном на початку зазвичай не дає такої переваги:
SELECT *
FROM users
WHERE email LIKE '%@example.com';Щоб перевірити такий шаблон, PostgreSQL не може просто перейти до одного відомого початку індексу.
Якщо таблиця містить мало рядків, послідовне читання може бути швидшим за звернення до індексу.
Використання індексу складається щонайменше з двох кроків:
прочитати індекс;
знайти відповідні рядки в таблиці.
Для маленької таблиці простіше прочитати її повністю.
Припустімо, що таблиця має мільйон рядків, а умова повертає 700 тисяч із них.
У такій ситуації PostgreSQL може вирішити, що дешевше прочитати таблицю послідовно, ніж переходити від індексу до великої кількості рядків.
Індекс найкорисніший, коли умова відбирає невелику частину даних. Цю властивість називають вибірковістю умови.
Планувальник використовує статистику про розподіл значень у таблиці. PostgreSQL оновлює її автоматично під час ANALYZE, але після значних змін даних статистика може ще не відображати поточний стан.
Оновити статистику вручну можна так:
ANALYZE users;Без актуальної статистики планувальник може неправильно оцінити кількість рядків і вибрати невдалий план.
Індекс не завжди означає менше роботи. Після знаходження значень в індексі PostgreSQL може виконати багато звернень до різних сторінок таблиці.
Якщо потрібні рядки розташовані в різних місцях, послідовне читання таблиці іноді вигідніше.
Індекс можна створити за кількома стовпцями:
CREATE INDEX users_country_created_at_idx
ON users (country, created_at);Такий індекс особливо корисний для запитів, які використовують перший стовпець:
SELECT *
FROM users
WHERE country = 'UA';SELECT *
FROM users
WHERE country = 'UA'
AND created_at >= DATE '2024-01-01';Порядок стовпців має значення. Для індексу (country, created_at) умова за country є його лівою, або першою, частиною.
Запит лише за другим стовпцем:
SELECT *
FROM users
WHERE created_at >= DATE '2024-01-01';не використовує такий індекс так само ефективно, як запит із умовою за country.
Зазвичай стовпці, за якими часто виконують точний пошук, розміщують перед стовпцями для діапазонів. Наприклад:
CREATE INDEX orders_customer_date_idx
ON orders (customer_id, created_at);Такий індекс підходить для запитів про замовлення конкретного клієнта за певний період:
SELECT *
FROM orders
WHERE customer_id = 42
AND created_at >= DATE '2025-01-01';Якщо індекс створено для email, умова безпосередньо за значенням може використовувати його:
WHERE email = 'olena@example.com'Але функція змінює значення перед порівнянням:
WHERE lower(email) = 'olena@example.com'Звичайний індекс на email не призначений для такого виразу. Для цього можна створити індекс саме на виразі:
CREATE INDEX users_lower_email_idx
ON users (lower(email));Після цього PostgreSQL може використовувати його для:
SELECT *
FROM users
WHERE lower(email) = 'olena@example.com';Порівняння має відповідати типу індексованого стовпця. Неочікуване перетворення типів може завадити ефективному використанню індексу.
Найкраще передавати параметри запиту правильного типу, а не перетворювати стовпець у текст або інший тип у кожному рядку.
Індекс допомагає, коли запит має фільтр, сортування або іншу операцію, для якої він підходить. Запит, що повертає всю таблицю, зазвичай не отримує значної переваги від індексу:
SELECT *
FROM users;NULLЗвичайний B-tree-індекс може зберігати значення NULL і допомагати для умов:
WHERE some_column IS NULLWHERE some_column IS NOT NULLОднак користь індексу все одно залежить від кількості відповідних рядків та загальної вартості виконання.
Під час перевірки запиту звертайте увагу на такі дії:
Створіть індекс на стовпці, який використовується в умові.
Виконайте ANALYZE.
Перевірте план через EXPLAIN.
Для реального вимірювання використайте EXPLAIN ANALYZE.
Порівнюйте план і час на даних, наближених до робочих.
Приклад:
CREATE INDEX users_created_at_idx
ON users (created_at);
ANALYZE users;
EXPLAIN ANALYZE
SELECT id, email
FROM users
WHERE created_at >= DATE '2024-01-01'
AND created_at < DATE '2024-02-01';Не варто примусово змушувати PostgreSQL використовувати індекс лише тому, що він існує. Планувальник може обрати послідовне сканування, і це може бути правильним рішенням для конкретного обсягу та розподілу даних.
Індекс прискорює лише ті операції, для яких він підходить. Надмірна кількість індексів збільшує витрати на INSERT, UPDATE і DELETE, адже індекси також потрібно оновлювати.
На маленькій таблиці PostgreSQL часто вибирає Seq Scan. Це не означає, що індекс марний на великій таблиці.
Перевіряйте запити на достатньому обсязі даних.
Індекс (a, b) і індекс (b, a) — різні індекси. Вони найкраще підходять для різних запитів.
Індекс на email не завжди допомагає для lower(email). Якщо пошук завжди виконується через вираз, індекс потрібно створювати для цього виразу.
Напис Index Scan сам по собі не гарантує, що запит працює швидко. Дивіться на фактичний час, кількість прочитаних рядків і кількість рядків, які залишилися після фільтрації.
Індекс допомагає PostgreSQL швидко знаходити частину рядків, сортувати дані та виконувати пошук за діапазоном.
Планувальник сам обирає між індексом і послідовним скануванням.
Рішення залежить від розміру таблиці, вибірковості умови, статистики та вартості читання.
EXPLAIN показує запланований спосіб виконання, а EXPLAIN ANALYZE — фактичний результат виконання.
B-tree добре підходить для =, порівнянь, діапазонів, сортування та пошуку за початком рядка.
Для складених індексів важливий порядок стовпців.
Функції над стовпцями, шаблони з % на початку та умови, що повертають багато рядків, можуть обмежити користь індексу.
Статистику таблиці можна оновити командою ANALYZE.