Пошук уроків, статей та іншого контенту
Створите B-tree індекси й розберете їх використання для пошуку, сортування та діапазонних умов.
B-tree — стандартний тип індексу в PostgreSQL. Якщо під час створення індексу не вказати тип, PostgreSQL використає саме B-tree.
Індекс зберігає значення стовпця у впорядкованому вигляді та допомагає швидше знаходити відповідні рядки. Це схоже на покажчик у книжці: замість послідовного перегляду всіх сторінок база переходить до потрібного місця.
B-tree добре підходить для:
пошуку за точною відповідністю (=);
порівнянь (<, <=, >, >=);
пошуку діапазону (BETWEEN);
сортування через ORDER BY;
перевірки значень на IS NULL та IS NOT NULL.
Загальний синтаксис:
CREATE INDEX назва_індексу
ON назва_таблиці (назва_стовпця);Наприклад:
CREATE INDEX idx_users_email
ON users (email);Після цього PostgreSQL матиме індекс idx_users_email для стовпця email.
Назва індексу не впливає на його роботу, але зрозумілий формат полегшує обслуговування. Часто використовують шаблон:
idx_таблиця_стовпецьЗа замовчуванням індекс створюється саме як B-tree. Тип можна вказати явно:
CREATE INDEX idx_users_email
ON users USING btree (email);Обидва варіанти створюють B-tree індекс.
Нижче наведено повний приклад, який можна виконати в PostgreSQL:
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
category text NOT NULL,
price numeric(10, 2) NOT NULL,
created_at date NOT NULL
);
INSERT INTO products (name, category, price, created_at)
VALUES
('Keyboard', 'accessories', 79.99, '2025-01-10'),
('Mouse', 'accessories', 29.99, '2025-01-12'),
('Monitor', 'display', 249.00, '2025-02-01'),
('Laptop', 'computers', 1299.00, '2025-02-15'),
('Headphones', 'audio', 149.50, '2025-03-03'),
('Webcam', 'accessories', 89.00, '2025-03-10');
CREATE INDEX idx_products_price
ON products (price);
CREATE INDEX idx_products_created_at
ON products (created_at);
CREATE INDEX idx_products_category
ON products (category);
-- Оновлюємо статистику для планувальника запитів
ANALYZE products;Тепер таблиця має окремі індекси для price, created_at і category.
B-tree індекс може прискорити пошук рядків за оператором =:
SELECT *
FROM products
WHERE category = 'accessories';PostgreSQL може скористатися індексом idx_products_category, щоб знайти потрібні записи без повного перегляду таблиці.
Однак на маленьких таблицях планувальник часто обирає послідовне читання таблиці. Це нормально: прочитати всю маленьку таблицю може бути дешевше, ніж спочатку прочитати індекс, а потім звернутися до самої таблиці.
Для перегляду плану запиту використовується EXPLAIN:
EXPLAIN
SELECT *
FROM products
WHERE price = 29.99;Для отримання фактичної статистики виконання можна використати EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price = 29.99;У результаті можна побачити, чи PostgreSQL використав:
Index Scan — читання через індекс;
Bitmap Index Scan і Bitmap Heap Scan — ефективний варіант для отримання багатьох рядків;
Seq Scan — послідовне читання всієї таблиці.
Назва плану не гарантує однакової поведінки для всіх даних. Планувальник оцінює кількість рядків, вартість операцій і статистику таблиці.
B-tree особливо корисний для діапазонних умов:
SELECT *
FROM products
WHERE price BETWEEN 50 AND 300;BETWEEN включає обидві межі. Наведений запит еквівалентний такому:
SELECT *
FROM products
WHERE price >= 50
AND price <= 300;Також можна використовувати одну межу:
SELECT *
FROM products
WHERE created_at >= DATE '2025-02-01';Або дві різні умови:
SELECT *
FROM products
WHERE created_at >= DATE '2025-02-01'
AND created_at < DATE '2025-04-01';Такий запис зручно використовувати для вибору записів за певний період.
B-tree зберігає ключі у впорядкованому вигляді, тому PostgreSQL може використати його для ORDER BY.
SELECT *
FROM products
ORDER BY price;Для зворотного порядку індекс можна прочитати у зворотному напрямку:
SELECT *
FROM products
ORDER BY price DESC;Індекс також може бути корисним, коли запит обмежує кількість результатів:
SELECT *
FROM products
ORDER BY price DESC
LIMIT 2;У такому випадку PostgreSQL може швидко знайти найдорожчі товари, не сортуючи всі рядки окремо.
Індекс не є копією таблиці, яку потрібно читати замість таблиці. Він містить структуру для пошуку, але самі значення та інші стовпці рядка зазвичай потрібно отримати з таблиці.
Наприклад, індекс на price допомагає знайти товари за ціною, але для отримання name, category та інших стовпців PostgreSQL може додатково звернутися до таблиці.
Індекс може прискорити читання, але має вартість:
займає місце на диску;
уповільнює INSERT;
може уповільнювати UPDATE і DELETE, якщо змінюються індексовані стовпці;
потребує обслуговування разом із таблицею.
Тому не варто створювати індекс для кожного стовпця без потреби.
Непотрібний індекс можна видалити:
DROP INDEX idx_products_category;Якщо індекс може бути відсутнім, використовуйте IF EXISTS:
DROP INDEX IF EXISTS idx_products_category;Видалення індексу не видаляє стовпець і не змінює дані в таблиці.
Створення індексу для вже заповненої таблиці може вимагати часу та ресурсів. Для невеликих таблиць це зазвичай непомітно, але для великих таблиць операцію потрібно планувати.
Для звичайного створення індексу використовується:
CREATE INDEX idx_products_price
ON products (price);Якщо індекс із такою назвою вже існує, команда завершиться помилкою. Варіант IF NOT EXISTS дозволяє уникнути помилки:
CREATE INDEX IF NOT EXISTS idx_products_price
ON products (price);Ця команда не перевіряє, чи відповідає наявний індекс потрібному визначенню. Вона лише не створює новий індекс, якщо індекс із такою назвою вже існує.
Наявність B-tree індексу не означає, що він буде використаний у кожному запиті.
Планувальник може вибрати Seq Scan, якщо:
таблиця дуже маленька;
умова повертає значну частину таблиці;
статистика таблиці застаріла;
послідовне читання дешевше за звернення до індексу та таблиці;
запит не відповідає можливостям індексу.
Після значних змін у таблиці статистику можна оновити:
ANALYZE products;Перевіряйте рішення планувальника через EXPLAIN або EXPLAIN ANALYZE, а не припускайте використання індексу лише за його наявністю.
Велика кількість індексів не завжди покращує продуктивність. Кожен індекс збільшує витрати на зміну даних.
Створюйте індекс для стовпців, які регулярно використовуються в умовах пошуку, сортуванні або діапазонних запитах.
Для запиту, який повертає більшість рядків таблиці, послідовне читання може бути ефективнішим за індекс.
EXPLAINБез плану виконання складно зрозуміти, як PostgreSQL обробляє запит. Використовуйте:
EXPLAIN ANALYZE
SELECT *
FROM products
WHERE created_at >= DATE '2025-02-01';ANALYZEЯкщо статистика застаріла, планувальник може неточно оцінити кількість рядків і вибрати менш ефективний план.
B-tree може допомогти отримати рядки у потрібному порядку, але це не означає, що результат будь-якого запиту автоматично буде відсортований. Якщо порядок важливий, явно вказуйте ORDER BY.
B-tree — типовий індекс PostgreSQL.
Він підходить для =, порівнянь, діапазонів і ORDER BY.
Індекс створюється командою CREATE INDEX.
Перевірити план запиту можна через EXPLAIN та EXPLAIN ANALYZE.
PostgreSQL сам вирішує, чи використовувати індекс.
Індекси прискорюють читання, але збільшують витрати на зміну даних.
Створювати індекси потрібно на основі реальних запитів і перевіряти їхню користь планом виконання.