Индексы, EXPLAIN и VACUUM

В прошлом уроке мы научились менять данные безопасно. В учебной базе магазина девять книг, и любой запрос к ней мгновенный. Но настоящий магазин копит миллионы строк: просмотры, заказы, события. Этот урок вводный: что такое индекс, как спросить PostgreSQL, каким путём он выполнит запрос, и почему таблицам нужна уборка. Подробно каждую тему разберём позже в этом разделе: индексам и планам посвящены уроки 25-28.

Предметный указатель

Тебе нужно найти в толстом учебнике всё про JOIN. Можно листать все 900 страниц подряд. А можно открыть предметный указатель в конце: там слова идут по алфавиту, и рядом с каждым номера страниц. Нашёл слово за секунды, открыл нужные страницы.

Индекс в базе устроен так же. Это отдельная структура рядом с таблицей, где значения колонки разложены по порядку, и у каждого стоит ссылка на строку. Как индекс устроен внутри, рассказано в уроке теории про B-дерево. Здесь смотрим на него снаружи.

Таблица побольше

Девяти книг мало, чтобы увидеть разницу. Сгенерируем таблицу просмотров на сто тысяч строк:

CREATE TABLE visits AS
SELECT g AS id,
       (g % 9) + 1 AS book_id,
       (g % 4) + 1 AS customer_id,
       date '2026-01-01' + (g % 280) AS visited_on
FROM generate_series(1, 100000) AS g;

ANALYZE visits;

generate_series выдаёт числа от 1 до 100000, а мы превращаем каждое в строку просмотра. ANALYZE собирает статистику: сколько строк в таблице и как распределены значения. По ней PostgreSQL решает, как выполнять запросы.

EXPLAIN: как ты это сделаешь?

Перед любым запросом можно написать EXPLAIN. Тогда сервер не выполнит запрос, а расскажет, как собирается его выполнять:

EXPLAIN SELECT * FROM visits WHERE id = 500;

                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on visits  (cost=0.00..1826.00 rows=1 width=16)
   Filter: (id = 500)
(2 rows)

Читаем:

• Seq Scan on visits: сервер прочитает таблицу целиком, строку за строкой. Это и есть листание учебника.
• Filter: (id = 500): каждую строку он проверит на условие и выбросит неподходящие.
• cost=0.00..1826.00 это оценка стоимости в условных единицах: сколько стоит получить первую строку и все строки. Сравнивать её имеет смысл только с другими планами.
• rows=1 это ожидание: сервер думает, что под условие подойдёт одна строка.

Чтобы найти одну строку, сервер перебирает сто тысяч. Добавим индекс:

CREATE INDEX visits_id_idx ON visits (id);

EXPLAIN SELECT * FROM visits WHERE id = 500;

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using visits_id_idx on visits  (cost=0.29..8.31 rows=1 width=16)
   Index Cond: (id = 500)
(2 rows)

Index Scan: сервер нашёл 500 в индексе и забрал из таблицы одну строку. Стоимость упала с 1826 до 8. Точные числа у тебя могут немного отличаться, а порядок будет тот же.

EXPLAIN только оценивает. Есть вариант EXPLAIN ANALYZE, который выполняет запрос и показывает реальное время каждого шага. Ему и чтению больших планов посвящён отдельный урок. Как отвечать про EXPLAIN на собеседовании, разобрано в уроке про N+1 и EXPLAIN.