Індекси та базова продуктивність: допомагаємо PostgreSQL швидше знаходити дані

Повертаємось до PostgreSQL.

У попередній лекції ти вивчив агрегатні функції.

Ти навчився рахувати рядки.

Підсумовувати значення.

Обчислювати середні значення.

Знаходити мінімальні та максимальні значення.

Групувати дані.

Фільтрувати групи за допомогою HAVING.

Дуже добре.

Тепер PostgreSQL може відповідати на питання типу:

Скільки продуктів у нас є?
Яка загальна вартість складу?
Скільки замовлень має кожен клієнт?
Яка категорія має найбільше продуктів?

Чудово.

Але тепер з’являється інше питання.

Дуже серйозне питання.

Що станеться, коли таблиця стане величезною?

Коли таблиця має десять рядків, усе швидке.

Коли таблиця має десять мільйонів рядків, PostgreSQL починає дивитися на тебе по-іншому.

Приблизно так:

Ти впевнений у цьому запиті?

Ось тут допомагають індекси.

Індекси допомагають PostgreSQL швидше знаходити дані.

Це не магія.

Але це потужно.

Як хороша карта.

Без індексу PostgreSQL може бути змушений читати всю таблицю.

З індексом PostgreSQL може швидше перейти до потрібних даних.

Дуже корисно.

Дуже база даних.

Дуже “будь ласка, не змушуй мій сервер плакати”.

Що ти вивчиш

У цій лекції ти вивчиш:

• чому запити можуть ставати повільними;
• що таке індекс;
• як індекси допомагають PostgreSQL швидше знаходити рядки;
• що таке послідовне сканування;
• що таке сканування через індекс;
• як використовувати EXPLAIN;
• як використовувати EXPLAIN ANALYZE;
• як створити індекс;
• коли індекси корисні;
• коли індекси не корисні;
• чому індекси не безкоштовні;
• як індекси впливають на вставку та оновлення даних;
• як працюють унікальні індекси;
• як працюють індекси на кілька колонок;
• типові помилки з індексами.

До кінця цієї лекції ти зрозумієш базову ідею продуктивності в базах даних.

Не ракетобудування.

Ще ні.

Просто достатньо, щоб перестати писати запити, від яких PostgreSQL виглядає втомленим.

Шляхетна мета.

Підготуй базу даних

Відкрий PostgreSQL:

sudo -iu postgres psql

Підключись до своєї бази даних:

\c learning_postgresql

Якщо бази ще немає, створи її:

CREATE DATABASE learning_postgresql;

Потім підключись:

\c learning_postgresql

Тепер видали стару таблицю, якщо вона існує:

DROP TABLE IF EXISTS products;

Створи нову таблицю products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  quantity INTEGER CHECK (quantity >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ця таблиця проста.

Але її достатньо, щоб вивчати індекси.

Маємо:

• назву продукту;
• категорію;
• ціну;
• кількість;
• дату створення.

Звичайна маленька таблиця.

Поки що.

Скоро вона стане маленькою армією рядків.

PostgreSQL готовий.

Можливо.

Додаємо кілька рядків

Додай кілька продуктів:

INSERT INTO products (name, category, price, quantity)
VALUES
  ('Laptop', 'Electronics', 900.00, 5),
  ('Mouse', 'Electronics', 25.00, 30),
  ('Keyboard', 'Electronics', 70.00, 15),
  ('Desk Chair', 'Furniture', 150.00, 8),
  ('Bookshelf', 'Furniture', 120.00, 4),
  ('Notebook', 'Office', 5.00, 100),
  ('Pen', 'Office', 2.00, 200),
  ('Whiteboard', 'Office', 80.00, 3);

Перевір дані:

SELECT * FROM products;

Ця таблиця крихітна.

Крихітна таблиця швидка навіть без індексів.

PostgreSQL може моргнути і прочитати її.

Але реальні застосунки не залишаються маленькими назавжди.

Дані ростуть.

Логи ростуть.

Замовлення ростуть.

Користувачі ростуть.

База даних росте.

А потім одного дня якийсь запит стає повільним, і всі дивляться на розробника.

Дуже несправедливо.

Але іноді правильно.

Додаємо більше рядків для тесту

Щоб побачити ідеї продуктивності, нам потрібно більше рядків.

PostgreSQL має корисну функцію generate_series.

Вона може генерувати багато рядків.

Виконай це:

INSERT INTO products (name, category, price, quantity)
SELECT
  'Product ' || number,
  CASE
    WHEN number % 4 = 0 THEN 'Electronics'
    WHEN number % 4 = 1 THEN 'Furniture'
    WHEN number % 4 = 2 THEN 'Office'
    ELSE 'Kitchen'
  END,
  (number % 100) + 1,
  (number % 50) + 1
FROM generate_series(1, 100000) AS number;

Це вставить сто тисяч продуктів.

Не реальних продуктів.

Фейкових продуктів.

Але корисних фейкових продуктів.

Як тренувальні гантелі для PostgreSQL.

Тепер перевір кількість рядків:

SELECT COUNT(*) AS total_products
FROM products;

Ти маєш побачити більше ніж сто тисяч рядків.

Тепер таблиця достатньо велика, щоб приклади продуктивності стали цікавішими.

Ще не величезна.

Але вже не маленька іграшка.

Чому запити стають повільними

Запит може бути повільним з багатьох причин.

Для початківця найчастіша причина проста:

PostgreSQL має перевірити занадто багато рядків.

Приклад:

SELECT *
FROM products
WHERE category = 'Electronics';

Якщо немає індексу на category, PostgreSQL може бути змушений перевірити кожен рядок.

Один за одним.

Як шукати один папірець у безладному гаражі.

Можливо.

Але не елегантно.

Це називається послідовне сканування.

Послідовне сканування

Послідовне сканування означає, що PostgreSQL читає таблицю рядок за рядком.

Він перевіряє кожен рядок і питає:

Цей рядок відповідає умові?

Для маленьких таблиць це нормально.

Для великих таблиць це може бути повільно.

Приклад:

SELECT *
FROM products
WHERE category = 'Electronics';

Без індексу на category, PostgreSQL може прочитати всю таблицю.

Це не завжди погано.

Іноді послідовне сканування — найкращий вибір.

Але якщо ти часто шукаєш по певній колонці, індекс може допомогти.

Ключове слово — часто.

Не створюй індекси випадково.

Індекси — це інструменти.

Не прикраси.

Що таке індекс?

Індекс — це спеціальна структура бази даних, яка допомагає PostgreSQL швидше знаходити рядки.

Уяви книгу.

Якщо книга не має індексу, тобі доведеться перегортати сторінку за сторінкою.

Якщо книга має індекс, ти можеш знайти тему і перейти до потрібної сторінки.

Індекс у базі даних працює за схожою ідеєю.

Без індексу:

Читати багато рядків і перевіряти їх.

З індексом:

Використати індекс, щоб швидше знайти відповідні рядки.

Індекси в PostgreSQL зазвичай зберігаються окремо від таблиці.

Таблиця містить реальні дані.

Індекс містить організовані посилання на ці дані.

Дуже спрощено.

Але для початку достатньо.

Як GPS для рядків.

Без дратівливого голосу.

Перевіряємо запит через EXPLAIN

PostgreSQL може показати, як він планує виконати запит.

Використовуй EXPLAIN.

EXPLAIN
SELECT *
FROM products
WHERE category = 'Electronics';

Ти можеш побачити щось таке:

Seq Scan on products
  Filter: ((category)::text = 'Electronics'::text)

Точний вивід може бути іншим.

Але важлива частина:

Seq Scan

Це означає, що PostgreSQL планує сканувати таблицю.

Він буде читати рядки і фільтрувати їх.

Це дуже корисна інформація.

PostgreSQL показує тобі свій план.

Як GPS бази даних.

Але з меншою кількістю кольорів.

EXPLAIN ANALYZE

EXPLAIN показує план.

EXPLAIN ANALYZE реально виконує запит і показує, що сталося.

Виконай:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Ти можеш побачити:

Seq Scan on products
  Filter: ((category)::text = 'Electronics'::text)
  Rows Removed by Filter: ...
Planning Time: ...
Execution Time: ...

Точні числа залежать від твого комп’ютера.

Важливі частини:

Seq Scan
Execution Time
Rows Removed by Filter

Rows Removed by Filter означає, що PostgreSQL перевірив рядки і відкинув багато з них.

Це робота.

Іноді необхідна робота.

Іноді робота, якої можна уникнути.

EXPLAIN ANALYZE дуже корисний.

Але пам’ятай:

Він реально виконує запит.

Тому будь обережний із запитами, які змінюють дані.

Не запускай його легковажно на небезпечному DELETE.

PostgreSQL не скаже:

Ти емоційно готовий?

Він просто виконає.

Створюємо індекс

Тепер створи індекс на category:

CREATE INDEX idx_products_category
ON products(category);

Назва idx_products_category — це просто назва.

Поширений стиль іменування:

idx_table_column

Тепер виконай запит ще раз:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Ти можеш побачити, що використовується індекс.

Наприклад:

Bitmap Index Scan
Bitmap Heap Scan

або:

Index Scan

Точний план залежить від PostgreSQL і твоїх даних.

Головна ідея:

PostgreSQL тепер має ще один варіант.

Він може використати індекс.

Без індексу він мусив читати таблицю.

З індексом він може швидше знайти потрібні рядки.

Може.

Не завжди.

PostgreSQL вибирає те, що вважає найкращим.

Він розумний.

Зазвичай.

Чому PostgreSQL все одно може використати послідовне сканування

Іноді ти створюєш індекс, а PostgreSQL все одно використовує послідовне сканування.

Не панікуй.

Це може бути нормально.

Чому?

Бо якщо багато рядків відповідають умові, читати всю таблицю може бути швидше.

Приклад:

SELECT *
FROM products
WHERE category = 'Electronics';

Якщо двадцять п’ять відсотків таблиці — це Electronics, PostgreSQL може вирішити:

Багато рядків підходить. Прочитаю таблицю.

Індекс найкорисніший тоді, коли запит повертає маленьку частину таблиці.

Наприклад:

SELECT *
FROM products
WHERE id = 50000;

Це дуже вибірково.

Один рядок.

Ідеально для індексу.

Пошук продукту по id швидкий, бо id уже індексований первинним ключем.

Первинні ключі автоматично створюють індекси.

PostgreSQL зробив це за тебе.

Дуже мило.

Дуже професійно.

Індекс первинного ключа

Коли ти створюєш це:

id SERIAL PRIMARY KEY

PostgreSQL автоматично створює унікальний індекс для id.

Саме тому цей запит швидкий:

SELECT *
FROM products
WHERE id = 50000;

Перевір:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE id = 50000;

Ти маєш побачити щось на кшталт:

Index Scan using products_pkey

products_pkey — це індекс первинного ключа.

Ти не створював його вручну.

PostgreSQL створив його, бо первинні ключі мають бути унікальними і придатними для пошуку.

Це одна з причин, чому первинні ключі важливі.

Вони не прикраси.

Вони посвідчення особи бази даних.

Індекс на price

Створимо індекс на price.

CREATE INDEX idx_products_price
ON products(price);

Тепер виконай:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price = 50.00;

PostgreSQL може використати індекс.

Це залежить від даних.

Тепер спробуй:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price > 90.00;

Індекс може допомогти і з пошуком по діапазону.

Приклади:

WHERE price = 50.00
WHERE price > 90.00
WHERE price BETWEEN 10.00 AND 20.00

Індекси особливо корисні, коли ти шукаєш, фільтруєш, сортуєш або робиш JOIN по певній колонці.

Але знову:

Не створюй індекси на кожну колонку.

Це не оптимізація.

Це конфеті для бази даних.

А конфеті потім важко прибирати.

Індекс і ORDER BY

Індекси також можуть допомагати з сортуванням.

Приклад:

EXPLAIN ANALYZE
SELECT *
FROM products
ORDER BY price ASC
LIMIT 10;

Оскільки ми створили індекс на price, PostgreSQL може використати його, щоб швидше знайти найдешевші продукти.

Це корисно для запитів типу:

Показати найдешевші продукти.
Показати найдорожчі продукти.
Показати найновіші пости.
Показати останні замовлення.

Поширений приклад — індекс на created_at.

CREATE INDEX idx_products_created_at
ON products(created_at);

Тоді цей запит може стати швидшим:

SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 10;

Це дуже часто зустрічається в реальних застосунках.

Блоги.

Замовлення.

Повідомлення.

Логи.

Усі хочуть останні десять речей.

Бо, здається, люди дуже люблять слово “останнє”.

Індекс і JOIN

Індекси можуть допомагати і з JOIN.

Уяви дві таблиці:

orders
customers

Зазвичай:

orders.customer_id references customers.id

Коли ти робиш JOIN:

SELECT
  o.id,
  c.name,
  o.total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id;

PostgreSQL має зіставити рядки.

Індекси на колонках, які використовуються в JOIN, можуть допомогти.

Первинні ключі індексуються автоматично.

Тому customers.id уже індексований, якщо це первинний ключ.

Але зовнішні ключі не завжди індексуються автоматично.

Це означає, що orders.customer_id може потребувати індексу, якщо ти часто робиш JOIN або фільтруєш по цій колонці.

Приклад:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Це дуже поширений індекс у реальних проєктах.

Колонки зовнішніх ключів часто хороші кандидати на індекс.

Не завжди.

Але часто.

Відповідь номер один у базах даних:

Залежить.

Дратує.

Але правда.

Унікальний індекс

Унікальний індекс забороняє дублікати значень.

Приклад:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL
);

Створи унікальний індекс:

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Додай користувача:

INSERT INTO users (email, name)
VALUES ('anna@example.com', 'Anna');

Спробуй додати іншого користувача з таким самим email:

INSERT INTO users (email, name)
VALUES ('anna@example.com', 'Another Anna');

PostgreSQL відхилить це.

Добре.

Email зазвичай має бути унікальним.

Унікальний індекс робить дві речі:

• допомагає швидше шукати;
• захищає дані від дублікатів.

Дуже корисно.

Дуже відповідально.

Як охоронець для твоєї таблиці.

Але без темних окулярів.

UNIQUE constraint чи унікальний індекс?

Також можна написати:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

Це створює унікальне обмеження.

PostgreSQL також створює унікальний індекс за кулісами.

Для початківця просте правило:

Використовуй UNIQUE у визначенні таблиці, коли це правило даних.

Наприклад:

Email має бути унікальним.
Username має бути унікальним.
Код продукту має бути унікальним.

Використовуй явні індекси, коли хочеш покращити швидкість пошуку.

Просте правило:

Правило даних: constraint.
Допомога продуктивності: index.

Не ідеально.

Але дуже практично.

Індекси на кілька колонок

Індекс може містити більше ніж одну колонку.

Приклад:

CREATE INDEX idx_products_category_price
ON products(category, price);

Це може допомогти запитам типу:

SELECT *
FROM products
WHERE category = 'Electronics'
ORDER BY price ASC;

Індекс упорядкований спочатку за category, потім за price.

Порядок колонок має значення.

Цей індекс корисний для:

WHERE category = 'Electronics'

і:

WHERE category = 'Electronics'
ORDER BY price ASC

Але він може бути менш корисним для:

WHERE price = 50.00

Чому?

Бо price — друга колонка в індексі.

Перша колонка — category.

Індекси на кілька колонок потужні.

Але їх потрібно створювати на основі реальних запитів.

Не на основі вайбу.

Бази даних не оптимізують вайб.

На жаль.

Індекси не безкоштовні

Індекси роблять деякі читання швидшими.

Але вони мають ціну.

Індекс:

• використовує місце на диску;
• має оновлюватися, коли змінюються дані;
• може сповільнювати INSERT;
• може сповільнювати UPDATE;
• може сповільнювати DELETE;
• додає складність.

Коли ти вставляєш рядок, PostgreSQL має вставити дані в таблицю.

Але він також має оновити індекси.

Якщо таблиця має багато індексів, запис стає важчим.

Тому не створюй індекси всюди.

Таблиця з надто багатьма індексами — як людина з десятьма рюкзаками.

Можливо, підготовлена.

Але точно повільніша.

Коли треба створювати індекс?

Створюй індекс, коли:

• ти часто шукаєш по колонці;
• ти часто фільтруєш по колонці через WHERE;
• ти часто сортуєш по колонці через ORDER BY;
• ти часто робиш JOIN через колонку;
• таблиця достатньо велика, щоб продуктивність мала значення;
• запит повертає маленьку частину таблиці;
• EXPLAIN ANALYZE показує повільне сканування.

Приклади:

WHERE email = 'anna@example.com'

WHERE customer_id = 10

WHERE created_at >= '2026-01-01'

ORDER BY created_at DESC LIMIT 10

JOIN orders ON orders.customer_id = customers.id

Це типові місця, де індекси можуть допомогти.

Але не вгадуй наосліп.

Виміряй.

Потім створи індекс.

Потім виміряй знову.

Це дорослий спосіб.

Трохи нудний.

Дуже ефективний.

Коли індекс може не допомогти

Індекс може не допомогти, коли:

• таблиця дуже маленька;
• запит повертає майже всю таблицю;
• колонка має дуже мало різних значень;
• ти використовуєш колонку так, що індекс не може допомогти;
• індекс не відповідає запиту;
• дані дуже часто змінюються, а читання рідкісне.

Приклад:

WHERE category = 'Electronics'

Якщо існує тільки чотири категорії і кожна категорія має багато рядків, індекс може не дуже допомогти.

Інший приклад:

WHERE quantity > 0

Якщо майже кожен продукт має quantity > 0, PostgreSQL може вибрати послідовне сканування.

Бо майже все підходить.

Індекс корисний, коли може уникнути роботи.

Якщо запиту потрібне майже все, уникати майже нічого.

Сумно, але логічно.

Функції можуть ховати індекси

Будь обережний з функціями у WHERE.

Припустимо, у тебе є індекс на email.

CREATE INDEX idx_users_email
ON users(email);

Цей запит може використати індекс:

SELECT *
FROM users
WHERE email = 'anna@example.com';

Але цей запит може не використати звичайний індекс:

SELECT *
FROM users
WHERE LOWER(email) = 'anna@example.com';

Чому?

Бо PostgreSQL шукає не сире значення email.

Він шукає результат LOWER(email).

Це інше.

Для такого запиту може знадобитися індекс на вираз:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

Тепер PostgreSQL може використати індекс для:

SELECT *
FROM users
WHERE LOWER(email) = 'anna@example.com';

Це трохи складніше.

Але важливо.

Якщо запит трансформує колонку, звичайний індекс може не допомогти.

База даних розумна.

Але думки не читає.

На щастя.

LIKE та індекси

Індекси іноді можуть допомогти з LIKE.

Приклад:

SELECT *
FROM products
WHERE name LIKE 'Product 12%';

Це шукає назви, які починаються з Product 12.

У деяких випадках це може використати індекс.

Але цей запит складніший:

SELECT *
FROM products
WHERE name LIKE '%12';

Чому?

Бо шаблон починається з wildcard.

PostgreSQL не може легко перейти до початку значення.

Йому доводиться шукати більше.

Просте правило:

LIKE 'abc%' може дружити з індексами.
LIKE '%abc' зазвичай не дружить з індексами.
LIKE '%abc%' зазвичай не дружить з індексами.

Існують спеціальні типи індексів для просунутого текстового пошуку.

Але поки що запам’ятай:

Шукати з початку легше.

Шукати з середини складніше.

Як знайти назву книги, коли пам’ятаєш тільки одне слово з середини.

Успіхів, хоробрий бібліотекарю.

Видалення індексу

Іноді ти створюєш індекс, а потім розумієш, що він не корисний.

Його можна видалити.

Приклад:

DROP INDEX IF EXISTS idx_products_price;

Це видаляє індекс.

Не таблицю.

Не дані.

Тільки індекс.

Але в реальних проєктах будь обережний.

Індекси можуть використовуватися важливими запитами.

Видалення неправильного індексу може зробити все повільним.

І тоді сервер починає свою драматичну оперу.

Типові помилки

Створювати індекси на кожну колонку

Погана ідея:

Кожній колонці індекс!

Ні.

Зупинись.

Це не новорічна ялинка.

Індекси мають ціну.

Створюй індекси на основі реальних запитів.

Не використовувати EXPLAIN ANALYZE

Вгадування — це не робота з продуктивністю.

Використовуй:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Вимірюй до і після.

Без вимірювання ти займаєшся астрологією баз даних.

Очікувати, що індекси виправлять погані запити

Індекси допомагають.

Але вони не виправляють усе.

Жахливий запит може залишитися жахливим навіть з індексом.

Якщо ти вибираєш занадто багато даних, погано робиш багато JOIN, або дивно фільтруєш, індекси можуть не врятувати.

Індекси — це інструменти.

Не чудеса.

Забувати, що індекси сповільнюють записи

Якщо таблиця отримує багато вставок і оновлень, надто багато індексів можуть погіршити продуктивність.

Читання можуть стати швидшими.

Записи можуть стати повільнішими.

Завжди є компроміс.

База даних дає.

База даних забирає.

Дуже філософсько.

Ігнорувати індекси на зовнішніх ключах

Зовнішні ключі важливі для зв’язків.

Але якщо ти часто робиш JOIN або фільтруєш по колонці зовнішнього ключа, подумай про індекс.

Приклад:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Це поширено в реальних застосунках.

Не завжди потрібно.

Але дуже часто корисно.

Практика

Створи індекс на category:

CREATE INDEX idx_products_category
ON products(category);

Перевір запит:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Створи індекс на price:

CREATE INDEX idx_products_price
ON products(price);

Перевір запит по ціні:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price BETWEEN 10.00 AND 20.00;

Перевір сортування:

EXPLAIN ANALYZE
SELECT *
FROM products
ORDER BY price ASC
LIMIT 10;

Створи індекс на created_at:

CREATE INDEX idx_products_created_at
ON products(created_at);

Перевір останні продукти:

EXPLAIN ANALYZE
SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 10;

Створи індекс на кілька колонок:

CREATE INDEX idx_products_category_price
ON products(category, price);

Перевір цей запит:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Office'
ORDER BY price ASC
LIMIT 10;

Запускай ці запити.

Дивись на результат.

Не хвилюйся, якщо план відрізняється від прикладів.

PostgreSQL вибирає плани на основі твоїх даних, індексів, налаштувань і комп’ютера.

Ціль не в тому, щоб запам’ятати кожен план.

Ціль у тому, щоб зрозуміти, що PostgreSQL намагається зробити.

Поки цього достатньо.

Міні-завдання

Створи дві таблиці:

customers
orders

Правила:

• один клієнт може мати багато замовлень;
• кожне замовлення належить одному клієнту;
• email клієнта має бути унікальним;
• замовлення мають загальну суму;
• замовлення мають дату створення.

Створи таблиці:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total NUMERIC(10, 2) CHECK (total >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Додай клієнтів:

INSERT INTO customers (email, name)
VALUES
  ('anna@example.com', 'Anna'),
  ('marco@example.com', 'Marco'),
  ('sofia@example.com', 'Sofia');

Додай замовлення:

INSERT INTO orders (customer_id, total)
SELECT
  (number % 3) + 1,
  (number % 200) + 10
FROM generate_series(1, 50000) AS number;

Тепер створи корисні індекси:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

CREATE INDEX idx_orders_created_at
ON orders(created_at);

CREATE INDEX idx_orders_total
ON orders(total);

Протестуй ці запити через EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT *
FROM customers
WHERE email = 'anna@example.com';

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1;

EXPLAIN ANALYZE
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE total > 150.00;

Потім запитай себе:

Які індекси були використані?
Які запити все ще використали послідовне сканування?
Чому?
Запит повернув багато рядків чи мало рядків?

Так починається справжня робота з продуктивністю.

Не вгадуванням.

А спостереженням.

PostgreSQL залишає підказки.

Треба тільки їх прочитати.

Як детектив.

Але з більшою кількістю крапок з комою.

Підсумок

Сьогодні ти вивчив:

• запити можуть ставати повільними, коли таблиці ростуть;
• послідовне сканування читає рядки один за одним;
• індекс допомагає PostgreSQL швидше знаходити рядки;
• первинні ключі автоматично створюють індекси;
• EXPLAIN показує план запиту;
• EXPLAIN ANALYZE виконує запит і показує реальні деталі виконання;
• індекси можуть допомагати з WHERE;
• індекси можуть допомагати з ORDER BY;
• індекси можуть допомагати з JOIN;
• унікальні індекси забороняють дублікати;
• індекси на кілька колонок залежать від порядку колонок;
• індекси використовують місце на диску;
• індекси можуть сповільнювати вставку, оновлення і видалення;
• не кожній колонці потрібен індекс;
• продуктивність треба вимірювати, а не вгадувати.

Це дуже важливий крок.

Ти більше не просто пишеш SQL.

Ти починаєш думати про те, як PostgreSQL виконує SQL.

Це інший рівень.

Кращий рівень.

Рівень, де база даних усе ще трохи лякає.

Але тепер у тебе є інструменти.

Наступна лекція

У наступній лекції ми створимо невеликий практичний проєкт на PostgreSQL.

Ми спроєктуємо таблиці.

Створимо зв’язки.

Додамо дані.

Виконаємо запити.

Використаємо JOIN.

Використаємо агрегатні функції.

І подумаємо про індекси.

Іншими словами, ми зберемо частини курсу разом.

Як збирати меблі.

Але з меншою кількістю загублених гвинтів.

Сподіваюсь.