Краткий ответ

Это может быть быстрее, если некластеризованный индекс содержит запись для каждой строки и значительно уже таблицы. Тогда СУБД читает меньше страниц с диска или из буфера, а число строк получает последовательным обходом компактной структуры.

Исторический контекст

Индексы появились как способ избежать полного чтения больших таблиц при поиске данных. Позднее оптимизаторы стали использовать их не только для поиска, но и как более компактный источник данных для агрегатных операций, когда значения самих строк не нужны.

Постановка проблемы

При подсчёте всех строк запросу не требуется читать столбцы таблицы. Если таблица широкая, а индекс содержит один небольшой ключ, чтение таблицы может потребовать значительно больше страниц, чем последовательное чтение индекса.

Однако индекс не гарантированно будет быстрее. Если таблица почти такая же компактная, индекс фрагментирован, плохо помещается в буфер или требует дополнительных проверок видимости, его использование может не дать выигрыша.

Подробное решение

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

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

Принципиально важно, что индекс должен действительно представлять все строки. В большинстве СУБД обычный индекс с индексируемым ключом позволяет это, включая записи с NULL, но конкретные правила зависят от реализации. Например, некоторые типы индексов могут не хранить полностью пустые ключи, поэтому выбор индекса нужно подтверждать планом и документацией конкретной СУБД.

Минимальный пример механизма:

CREATE TABLE orders (
    id bigint PRIMARY KEY,
    description text,
    created_at timestamp
);

CREATE INDEX ix_orders_created_at ON orders (created_at);

SELECT COUNT(*) FROM orders;

Индекс по created_at может оказаться существенно уже таблицы, поэтому оптимизатор способен выбрать его полный просмотр. Это не означает, что строки упорядочены по нужному условию: индекс используется как компактный источник записей, а не обязательно для поиска диапазона.

В SQL Server при наличии кластеризованного индекса некластеризованный индекс также содержит ссылку на кластеризованный ключ, что увеличивает его размер. В PostgreSQL обычный индексный просмотр не всегда избавляет от обращения к таблице из-за проверки видимости строк по MVCC; индекс-only scan возможен, когда карта видимости позволяет пропустить эти проверки.

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

Ситуация из практики

В таблице заказов хранились текстовое описание, JSON-документ и несколько служебных полей. Запрос подсчитывал все заказы, но план выполнял полное сканирование таблицы, хотя существовал индекс по дате создания.

Рассматривались три варианта. Можно было принудительно заставить запрос читать индекс, но это создавало зависимость от конкретного плана и могло ухудшиться после изменения объёма данных. Можно было создать отдельный узкий индекс по неизменяемому идентификатору, но это увеличивало стоимость вставок и обновлений.

Выбрали узкий индекс только после проверки фактических планов, размеров структур и нагрузки на запись. Он уменьшил объём чтения для агрегатного запроса, но решение оставили только при условии, что дополнительная стоимость поддержки индекса приемлема. Для PostgreSQL отдельно проверили долю страниц, на которых установлена карта видимости, поскольку без неё индексный план мог всё равно обращаться к таблице.

Что кандидаты часто упускают

1. Всегда ли COUNT(*) по индексу означает отсутствие чтения таблицы?

Нет. В СУБД с MVCC нужно удостовериться, что каждая индексная запись видима текущей транзакции. Если индекс не содержит достаточной информации о видимости, СУБД может обращаться к табличным страницам. В PostgreSQL это зависит, в частности, от состояния visibility map; поэтому в плане следует отличать обычный index scan от index-only scan.

2. Почему самый узкий индекс не всегда выбирается?

Оптимизатор учитывает не только размер индекса, но и стоимость его чтения, фрагментацию, доступность страниц в памяти, параллельное выполнение и особенности хранения. Если таблица мала или уже полностью находится в буферном кэше, последовательное сканирование таблицы может оказаться дешевле. На выбор также влияют статистика и модель стоимости конкретной СУБД.

3. Можно ли создать отдельный индекс исключительно ради ускорения подсчёта строк?

Технически можно, но это редко оправдано само по себе. Индекс занимает место, замедляет вставки, удаления и некоторые обновления, а его обслуживание создаёт дополнительную нагрузку. Решение имеет смысл только после измерений, если подсчёт выполняется часто, таблица широкая, существующие индексы недостаточно компактны и выигрыш чтения превышает постоянную стоимость поддержки.