В модуле 2 мы видели EXPLAIN мельком и говорили: «оптимизатор сам выберет, как идти за данными». В этом модуле мы наконец-то поймём, из чего он выбирает. И первая развилка, самая базовая, — между Seq Scan и Index Scan. Чтобы её обсуждать, надо разобраться, что такое индекс и почему без него СУБД делает то, что делает.

Этот модуль закрывает курс на финальной нотой: ты уже умеешь писать корректный SQL. Теперь — будем учиться писать быстрый SQL. Это разные навыки, и большая часть разработчиков спотыкается именно на втором — они пишут запросы, которые работают на тестовых 100 строках и кладут базу на боевых 100 миллионах. Различие между ними часто упирается в один-единственный вопрос: «какой план у этого запроса?». Ответить на него учит EXPLAIN, который мы детально разберём в уроке 5. Но прежде, чем читать планы, надо понимать, какие операции у БД вообще есть в арсенале.

Глубокие internals — страницы B-tree, MVCC, costing — для следующего курса. Здесь — интуиция, которой хватит, чтобы написать осмысленный CREATE INDEX и не закопать таблицу.

Что делает СУБД без индекса

Представь таблицу customers на миллион строк. Ты пишешь:

SELECT * FROM customers WHERE email = '[email protected]';

У СУБД нет волшебного способа узнать, в какой строке лежит Кира. У неё есть только сама таблица — последовательность строк, лежащих в файле на диске. Поэтому она открывает файл и читает каждую строку, сравнивая email с искомой. Это

Сложность такой операции — O(N): миллион строк = миллион сравнений. На маленькой таблице (100 строк) это незаметно. На миллионе — десятки миллисекунд. На миллиарде — десятки секунд. Это и есть «база лагает», когда команда вспоминает про индексы.

Разница между «миллион» и «двадцать» — это и есть та самая магия индексов, на которой держится вся быстрая БД.

Аналогия: алфавитный указатель в книге

Если читать всю книгу подряд в поисках слова «реляция» — это Seq Scan. Если перевернуть на алфавитный указатель в конце, найти «реляция: с. 47, 152, 198» и сразу открыть нужные страницы — это Index Scan. Указатель и есть индекс: отдельная структура данных, упорядоченная по ключу, в которой рядом с ключом лежит «адрес» (страница в книге, физическая позиция строки в таблице).

Ключевые свойства этой аналогии работают и для БД:

• Указатель — отдельная сущность. В БД индекс — это отдельный объект, со своим местом на диске. Таблица и индекс хранятся раздельно.
• Указатель упорядочен. Слова в алфавитном порядке, иначе бинарный поиск по указателю невозможен. В B-tree ключи тоже отсортированы.
• Указатель надо поддерживать. Если в книгу добавили главу — указатель надо переиздать. Если в таблицу добавили строку — индекс надо обновить.

Эти три свойства дают и плюсы, и минусы. Поговорим о минусах честно.

Аналогия работает даже глубже. Возможно, ты видел в учебниках по программированию тематический указатель — отдельно от алфавитного. Это второй индекс на тот же текст, но с другим ключом. Точно так же в БД на одну таблицу можно создать сколько угодно индексов — по разным колонкам, по разным выражениям, по разным комбинациям. Каждый покрывает свой класс запросов. И каждый, разумеется, требует места и обновлений.

Trade-off: индексы стоят денег

Индекс — это не бесплатный «делайте всё быстрее». Это пакт: на чтение становится в сотни раз быстрее, на запись становится дороже. Конкретно:

• INSERT теперь требует вставки и в таблицу, и в каждый индекс на ней. Если на таблице 5 индексов — каждая вставка стоит 6 операций вместо 1.
• UPDATE колонки, которая участвует в индексе, — это удаление старого ключа из индекса и вставка нового. Если индекса нет — это просто перезапись строки.
• DELETE аналогично — надо вычистить ссылки на удалённую строку из всех индексов.
• Дисковое пространство. Индекс на колонку email (текст) может занимать 10-30% от размера самой таблицы. Пять индексов = таблица потолстела вдвое.

Поэтому правило, к которому мы будем возвращаться весь модуль:

Смотрим руками: создаём индекс и видим разницу

Наша вселенная небольшая — customers всего 12 строк, разница между Seq Scan и Index Scan будет физически незаметна. Но PostgreSQL в выводе EXPLAIN всё равно покажет, что именно он выбрал. На такой маленькой таблице оптимизатор почти всегда предпочтёт Seq Scan — даже с индексом! — потому что прочитать 12 строк дешевле, чем спуститься по дереву. И это правильно: для маленьких таблиц индекс не нужен.

В выводе ты увидишь что-то вроде Seq Scan on customers ... Filter: (email = '[email protected]'). Это значит: «открыл таблицу, прочитал все строки, отдал только подходящие». Обрати внимание на оценочные rows и cost — мы вернёмся к ним в уроке про EXPLAIN.

А теперь создадим индекс — стандартный B-tree, по умолчанию PostgreSQL использует именно его — и посмотрим, изменилось ли что-нибудь:

На таблице в 12 строк план, скорее всего, останется Seq Scan — и это разумно. На таблице в миллион строк PostgreSQL точно переключится на Index Scan. В реальной работе ты увидишь оба варианта; в обоих случаях оптимизатор делает то, что считает быстрее.

Ещё одна важная деталь: уже существующий индекс под уникальное ограничение — это тот же B-tree. PostgreSQL автоматически создаёт индекс под каждый UNIQUE и PRIMARY KEY. Проверь:

Ты увидишь индексы вида customers_pkey (по PRIMARY KEY) и customers_email_key (по UNIQUE). На колонки id и email индексы уже есть — это не наша работа, это побочный эффект ограничений целостности. Поэтому, когда говорят «не индексируй колонки, на которых уже есть UNIQUE» — это потому, что индекс уже есть, дублировать его бессмысленно.

Когда индекс действительно ускоряет

Дальше в модуле мы будем много говорить про случаи, когда индекс не помогает. Чтобы сразу выработать интуицию, вот короткий каталог сценариев, на которых индекс работает блестяще:

• Селективный фильтр. «Найди клиента с конкретным email-ом» — выбирает 1 строку из миллиона. Индекс читает 1 страницу heap; Seq Scan читает миллион. Разница в шесть порядков.
• Точечный JOIN. LEFT JOIN orders o ON c.id = o.customer_id — на каждой строке customers спускаемся в индекс по o.customer_id. Без индекса — Hash Join или Nested Loop с Seq Scan, что на больших таблицах катастрофа.
• Top-N запрос. ORDER BY created_at DESC LIMIT 10. С индексом по created_at — спускаемся к самому большому ключу, читаем 10 листов, отдаём. Без индекса — сортируем весь миллион.
• EXISTS / NOT EXISTS. «Есть ли у клиента хоть один заказ» — индексу достаточно найти первое совпадение и остановиться. Это semi-join — он редко доходит до конца индекса.

И наоборот, индекс обычно не нужен, если:

• Таблица маленькая (десятки-сотни строк).
• Фильтр оставляет большую долю таблицы (больше 10%).
• Колонка в фильтре часто меняется или часто содержит NULL.
• Таблица write-heavy, а соответствующее чтение происходит редко.

В обоих списках слово «обычно» — потому что у любого правила есть исключения, и EXPLAIN ANALYZE всегда последнее слово.

Что находится «между строк»: indexkey vs heap

Чуть-чуть про физику. Когда мы говорим «индекс ссылается на строку», что именно лежит в листе индекса? Пара (ключ, TID), где TID —

Когда план — Index Scan, PG делает два чтения: одно в индексе (чтобы найти TID), второе в heap (чтобы прочитать саму строку по TID). Когда план — Index Only Scan, второе чтение пропускается, потому что все нужные данные уже есть в индексе. Это очень быстрый план, и мы научимся его добиваться в уроке 4 (через INCLUDE) и уроке 5 (про EXPLAIN).

Удаление и пересоздание индекса

Раз уж разобрались, как индекс создаётся — стоит сказать про обратную операцию. Удалить индекс — это DROP INDEX:

DROP INDEX idx_customers_country;

Это безопасная команда: данные таблицы не трогаются, только удаляется отдельная структура. Запросы продолжат работать — просто медленнее, через Seq Scan. После такой команды стоит запустить EXPLAIN на тех же запросах: если план остался прежним, значит, индекс и так не использовался, и удаление было полезной экономией.

Иногда индекс надо пересоздать (когда подозреваешь bloat — рост индекса из-за UPDATE/DELETE, который ускоряет, если индекс «раздулся»). Делать это надо аккуратно: обычная команда REINDEX блокирует таблицу на чтение/запись. В проде используют REINDEX CONCURRENTLY, который пересоздаёт индекс без блокировок (но дороже по CPU).

Тонкости пересоздания и bloat — это уже территория следующего курса. Сейчас знай главное: создавать и удалять индексы — обычные DDL-операции, которые можно делать в любое время. Главное — не на пиковой нагрузке без CONCURRENTLY.

Карта типов индексов в PostgreSQL

PostgreSQL — необычно щедрый в выборе индексов. Кроме B-tree он умеет:

В этом курсе мы говорим только про B-tree — он покрывает 90% случаев. Но запомни: если в проде наткнёшься на JSONB-фильтры или полнотекстовый поиск, ответ — не «забей и пиши Seq Scan», а «возьми правильный тип индекса».

Нюансы, которые сразу стоит знать

Несколько вещей, которые мы детально разберём в следующих уроках, но знать про них надо уже сейчас:

• Индекс не обязан быть только по одной колонке. Можно индексировать сразу несколько колонок (composite index, урок 4), а также выражения вроде LOWER(email) (expression index) — об этом в уроке 3.
• Оптимизатор не обязан использовать индекс. Даже если индекс есть и колонка в WHERE, PostgreSQL может выбрать Seq Scan, если посчитает его дешевле. Часто это правильное решение.
• Удалить индекс — это DROP INDEX. Не путать с DROP TABLE. Удаление индекса безопасно — данные таблицы целы, запросы продолжают работать, просто медленнее.

Где живёт статистика «использован ли индекс»

После создания индекса хочется понять — а реально ли его кто-то использует? PostgreSQL ведёт счётчики на каждый индекс в системном view pg_stat_user_indexes:

• idx_scan — сколько раз индекс был использован для Index Scan (или Bitmap Index Scan).
• idx_tup_read — сколько раз PG достал ключ из индекса.
• idx_tup_fetch — сколько раз PG потом достал строку из heap по TID.

В команде такая практика: раз в месяц смотреть idx_scan = 0 индексы и обсуждать — нужны они или их пора DROP INDEX. На большой таблице каждый лишний индекс — это процент производительности INSERT, который можно вернуть.

Что мы изучим в следующих уроках

Каркас модуля:

• Урок 2: B-tree на пальцах — почему log(N), как устроено дерево, что такое диапазонный поиск.
• Урок 3: Где индекс работает (равенство, диапазон, prefix-LIKE, ORDER BY), где игнорируется (функция на колонке, суффикс-LIKE, type mismatch).
• Урок 4: Composite индексы — индекс по нескольким колонкам, правило leftmost prefix, Bitmap Heap Scan.
• Урок 5: EXPLAIN — главный инструмент перфоманс-разбора. Виды узлов, оценки vs факты, чтение плана.
• Урок 6: Антипаттерны — каталог типичных ошибок и их рефакторинг.

Параллельно с этими темами мы будем писать EXPLAIN снова и снова, чтобы зарегистрировать в моторной памяти выводы PostgreSQL.

Чек-лист

• Без индекса СУБД делает Seq Scan — читает таблицу целиком, O(N).
• С B-tree индексом поиск становится логарифмическим — O(log N).
• Аналогия: индекс = алфавитный указатель в книге; упорядочен по ключу, рядом с ключом — адрес строки.
• На маленьких таблицах оптимизатор может игнорировать индекс — это нормально, Seq Scan дешевле.
• Индексы — это инвестиция: ускоряют чтение, замедляют запись, занимают место.
• PostgreSQL автоматически создаёт индексы под PRIMARY KEY и UNIQUE.
• В курсе говорим про B-tree (90% случаев); GIN/GiST/BRIN/Hash — для специальных задач.
• pg_stat_user_indexes.idx_scan — счётчик использования индекса. idx_scan = 0 за месяц — кандидат на удаление.