Пошук уроків, статей та іншого контенту
Розглянете призначення індексів, їхню структуру та компроміси між швидкістю читання і витратами на зберігання.
Індекс — це окрема структура даних, яка допомагає PostgreSQL швидше знаходити рядки в таблиці.
Без індексу база даних може перевіряти рядки один за одним:
SELECT *
FROM users
WHERE email = 'anna@example.com';Якщо в таблиці мільйони рядків, такий пошук може вимагати перегляду всієї таблиці. Індекс дає PostgreSQL змогу швидше визначити, де розташований потрібний рядок.
Індекс схожий на покажчик у книжці:
без покажчика потрібно переглядати сторінки послідовно;
з покажчиком можна одразу перейти до потрібного розділу;
сам покажчик також займає місце і потребує оновлення.
Індекс створюють командою CREATE INDEX:
CREATE INDEX idx_users_email
ON users (email);У цьому прикладі:
idx_users_email — назва індексу;
users — таблиця;
email — стовпець, значення якого індексуються.
Після створення індексу PostgreSQL може використати його для запитів на кшталт:
SELECT *
FROM users
WHERE email = 'anna@example.com';Індекс не змінює результат запиту. Він лише може змінити спосіб, у який PostgreSQL знаходить дані.
Наведений приклад можна виконати в PostgreSQL:
-- Створюємо тимчасову таблицю, яка існуватиме лише протягом поточного з'єднання
CREATE TEMP TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
category text NOT NULL,
price numeric(10, 2) NOT NULL
);
-- Додаємо тестові дані
INSERT INTO products (id, name, category, price)
SELECT
number,
'Товар ' || number,
CASE
WHEN number % 3 = 0 THEN 'books'
WHEN number % 3 = 1 THEN 'games'
ELSE 'music'
END,
(number % 1000) + 0.99
FROM generate_series(1, 100000) AS number;
-- Перевіряємо план пошуку до створення індексу
EXPLAIN
SELECT *
FROM products
WHERE category = 'books';
-- Створюємо індекс для стовпця category
CREATE INDEX idx_products_category
ON products (category);
-- Перевіряємо план пошуку після створення індексу
EXPLAIN
SELECT *
FROM products
WHERE category = 'books';Команда EXPLAIN показує план виконання запиту. PostgreSQL може вибрати різні способи пошуку:
Seq Scan — послідовно переглянути таблицю;
Index Scan — використати індекс для пошуку;
інші типи планів, залежно від запиту та статистики.
Наявність індексу не гарантує, що PostgreSQL використає саме його. Планувальник оцінює вартість різних варіантів і вибирає той, який вважає ефективнішим.
Наприклад, якщо запит повертає значну частину таблиці, повний послідовний перегляд може бути швидшим, ніж використання індексу.
Індекс зберігає значення одного або кількох стовпців у спеціальній структурі. У ній також міститься інформація, яка допомагає знайти відповідні рядки таблиці.
За замовчуванням PostgreSQL створює індекси типу B-tree:
CREATE INDEX idx_products_price
ON products (price);B-tree добре підходить для:
точного порівняння:
WHERE price = 100.99пошуку за діапазоном:
WHERE price >= 100 AND price < 200сортування за індексованим стовпцем:
ORDER BY priceпорівнянь текстових значень, наприклад:
WHERE name = 'Товар 500'Індекс не є копією всієї таблиці. Зазвичай він містить значення індексованих стовпців і посилання на відповідні рядки таблиці.
Можна створити індекс одразу для кількох стовпців:
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);Такий індекс може бути корисним для запитів, які використовують customer_id, а також для запитів, які використовують customer_id разом із created_at:
SELECT *
FROM orders
WHERE customer_id = 42
AND created_at >= '2026-01-01';Порядок стовпців важливий. Індекс (customer_id, created_at) не є повністю еквівалентним індексу (created_at, customer_id).
Для початку варто створювати індекси відповідно до реальних запитів, а не індексувати всі стовпці без аналізу.
PRIMARY KEYКоли таблиця має первинний ключ, PostgreSQL автоматично створює унікальний індекс для цього ключа:
CREATE TABLE users (
id bigint PRIMARY KEY,
name text NOT NULL
);Індекс потрібен не лише для швидкого пошуку за id. Він також допомагає PostgreSQL гарантувати, що два рядки не матимуть однакового значення первинного ключа.
Унікальне обмеження також зазвичай створює унікальний індекс:
CREATE TABLE accounts (
id bigint PRIMARY KEY,
email text UNIQUE NOT NULL
);У цьому прикладі значення email не можуть повторюватися.
Індекс прискорює не всі операції. Він має власну вартість.
Індекси можуть:
пришвидшити пошук за умовою WHERE;
допомогти під час об’єднання таблиць через JOIN;
пришвидшити деякі операції сортування;
забезпечити унікальність значень;
зменшити кількість рядків, які потрібно прочитати з таблиці.
Індекси:
займають додатковий простір на диску;
сповільнюють INSERT;
можуть сповільнювати UPDATE, якщо змінюється індексований стовпець;
можуть сповільнювати DELETE;
потребують обслуговування під час змін даних.
Коли PostgreSQL додає новий рядок, він має оновити не лише таблицю, а й кожен відповідний індекс. Тому велика кількість індексів може погіршити швидкість запису.
Індекс не завжди дає відчутне прискорення.
Для маленької таблиці PostgreSQL часто швидше прочитати всі рядки, ніж звертатися до індексу.
Якщо умова відповідає більшій частині таблиці, індекс може не допомогти. Перехід від індексу до багатьох рядків може бути дорожчим за послідовне читання таблиці.
Наприклад, у стовпці is_active можуть бути лише два значення: true і false. Такий індекс може бути корисним для деяких запитів, але не обов’язково для всіх.
Якщо стовпець регулярно оновлюється, PostgreSQL часто змінюватиме і його індекс. Це збільшує витрати на запис.
Для аналізу запиту використовують EXPLAIN:
EXPLAIN
SELECT *
FROM products
WHERE price >= 500;Щоб побачити фактичний час виконання і кількість оброблених рядків, використовують EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price >= 500;EXPLAIN ANALYZE справді виконує запит. Тому з операціями, які змінюють дані, потрібно бути обережним:
EXPLAIN ANALYZE
DELETE FROM products
WHERE id = 1;Такий запит видалить рядок. Для навчання й перевірки планів безпечніше починати із запитів SELECT.
Якщо індекс більше не потрібен, його можна видалити:
DROP INDEX idx_products_category;Видалення індексу не видаляє дані з таблиці. Воно лише прибирає допоміжну структуру.
Щоб не отримати помилку, якщо індекс уже видалений, можна використати:
DROP INDEX IF EXISTS idx_products_category;Не потрібно створювати індекс для всіх стовпців таблиці. Це збільшує використання диска і може сповільнити зміни даних.
Індекси створюють для стовпців, які справді використовуються в запитах і для яких індекс дає користь.
PostgreSQL самостійно вибирає план виконання. Навіть за наявності відповідного індексу він може обрати послідовне читання таблиці.
Для складеного індексу порядок стовпців має значення. Його потрібно узгоджувати з умовами та порядком виконання реальних запитів.
Зміни продуктивності варто перевіряти за допомогою EXPLAIN і EXPLAIN ANALYZE на даних, близьких до реальних.
Індекс не містить незалежну повну копію таблиці. Це допоміжна структура, яка прискорює окремі способи доступу до даних.
Індекс — це допоміжна структура для швидшого пошуку даних.
За замовчуванням PostgreSQL створює індекси типу B-tree.
Індекси корисні для пошуку, діапазонів, деяких операцій сортування та обмежень унікальності.
Первинний ключ автоматично отримує унікальний індекс.
Індекси займають місце і можуть сповільнювати INSERT, UPDATE та DELETE.
PostgreSQL не зобов’язаний використовувати індекс у кожному запиті.
Користь індексу потрібно перевіряти на реальних запитах за допомогою EXPLAIN та EXPLAIN ANALYZE.