Индексы и EXPLAIN

Если в таблице сто строк, база просмотрит их мгновенно. Если миллион — читать все строки ради каждого запроса медленно. Эту проблему решает индекс.

Индекс — как указатель в конце книги

Чтобы найти в книге слово «транзакция», вы не листаете все страницы — вы смотрите алфавитный указатель в конце и сразу открываете нужную страницу. Индекс устроен так же: значения столбца хранятся отдельно в отсортированном виде, и каждое указывает на строку.

CREATE INDEX idx_students_city ON students (city);

Теперь WHERE city = 'Samarqand' читает не всю таблицу, а только индекс.

Как база выполняет запрос: EXPLAIN

EXPLAIN не выполняет запрос, а показывает его план:

EXPLAIN SELECT * FROM students WHERE id = 10;

В плане PostgreSQL Index Scan using students_pkey — использован индекс первичного ключа. А Seq Scan означает, что таблица читалась целиком по порядку.

Поиск по столбцу без индекса:

EXPLAIN SELECT * FROM students WHERE city = 'Samarqand';

Здесь база читает всю таблицу — индекса на city нет.

Когда нужен индекс

• столбцы, которые часто стоят в WHERE, JOIN … ON, ORDER BY;
• столбцы внешних ключей (grades.student_id) — ускоряются JOIN;
• столбцы, которые не должны повторяться, — CREATE UNIQUE INDEX.

Для первичного ключа и UNIQUE индекс создаётся автоматически.

PostgreSQL сам индекс на столбце внешнего ключа не создаёт — его нужно добавить вручную: CREATE INDEX ON grades (student_id);

Цена индекса

Индекс не бесплатен:

• занимает место на диске;
• каждый INSERT, UPDATE, DELETE обновляет и индекс — запись замедляется.

Поэтому «индекс на каждый столбец» — плохая идея. Добавляйте индекс под конкретный медленный запрос и проверяйте результат через EXPLAIN.

Когда индекс не срабатывает

• столбец внутри функции: WHERE UPPER(city) = 'TOSHKENT' — обычный индекс не используется;
• LIKE '%bek' — шаблон с неизвестным началом;
• очень маленькая таблица — базе всё равно проще прочитать её целиком.

Индекс по нескольким столбцам

CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_at);

Он работает для WHERE customer_id = 5 и для WHERE customer_id = 5 AND ordered_at > …, но не для одного WHERE ordered_at > … — порядок важен (телефонный справочник отсортирован сначала по фамилии, потом по имени).

Запомните

• Индекс — отсортированный указатель, ускоряющий поиск.
• EXPLAIN показывает план запроса; с его помощью ищут полное сканирование.
• Индекс ускоряет чтение и замедляет запись — добавляйте обдуманно.