Почему COUNT(*) быстрее, чем COUNT(col) — и когда это не так21.04.2025 12:46

Привет, Хабр!

Сегодня поговорим про одну из тех тем, что вроде бы тривиальна, но до последней капли грязи тащит за собой внушительный пласт вопросов. Речь пойдёт про COUNT(*)и COUNT(col) в PostgreSQL.

Уверен, многим знакома ситуация: в коде сидит такая строчка:

SELECT COUNT(user_id) FROM users;

—, а где‑то рядом мелькает «оптимизированный» вариант:

SELECT COUNT(*) FROM users;

И вы, глядя на второй, думаете: «О, звёздочка же дороже, чем конкретная колонка, да ещё и разве можно отсюда узнать, сколько user_id вообще есть?» Или наоборот считаете, что COUNT(col) проверяет не‑null значения и поэтому медленнее. Гуглите «COUNT (*) vs COUNT (column)». Натыкаетесь на миллион обсуждений, множество гуру вендоров баз, но мало что понятно.

Что делает COUNT (*), а что — COUNT (col)

• COUNT(*)
  Подсчитывает все строки, вне зависимости от того, какие в них значения хранятся.

• COUNT(col)
  Подсчитывает только те строки, где col IS NOT NULL.

Важно: COUNT(*) не читает вообще все колонки. Он только проверяет наличие строки. В PostgreSQL это аппаратно отработано очень оптимально.

Почему в SQL такая семантика?

SQL‑стандарт определяет:

COUNT(*) counts all rows and does not require any specific column access.
COUNT(expression) counts only when the expression is not null.

Эта разница на уровне стандартов. Но её реализация в разных СУБД может отличаться. Мы же фокусируемся на PostgreSQL.

MVCC, visibility map и почему COUNT (*) чаще всего идёт в full table scan

MVCC в двух словах

PostgreSQL не перезаписывает строки при апдейтах. Он добавляет новые версии, а старые остаются валидными для тех, кто ещё читает «прошлое». Каждая строка (точнее, tuple) хранит два служебных поля:

• xmin: XID, когда эта строка была создана;

• xmax: XID, когда она была «удалена» (не физически, а логически — через новую версию).

При чтении PostgreSQL проверяет: «а моя текущая транзакция видит эту строку?» Это означает, что для каждой строки нужно сравнить:

• Активна ли транзакция, которая сделала xmin;

• Завершена ли транзакция, которая сделала xmax;

• Не касается ли она меня.

Именно это делает COUNT(*) тяжёлым. Чтобы просто понять, существует ли строка, движок вынужден проверить её видимость через MVCC. А это не просто побитовая проверка — это поход в pg_xact (бывший pg_clog), иногда — чтение данных из диска, если XID не помещается в память. А главное — это требует читать каждую строку, даже если вы не вытаскиваете ни одного поля.

Почему Postgres не может просто взять и посчитать строки по индексу

Потому что в индексах нет информации о xmin/xmax, и они не могут гарантировать, что каждая строка всё ещё жива. Индекс может указывать на кучу мёртвых tuple’ов — удалённых или переобновлённых, но не убранных физически.

Чтобы Postgres мог использовать только индекс, он должен быть уверен, что строки:

1. Ещё видимы для всех (не только вашей транзакции);

2. Физически актуальны;

3. Не потребуют чтения из heap‑а.

Visibility Map: бита, от которой всё зависит

pg_visibility — это специальная карта, где для каждой страницы таблицы (не строки) записано: «все строки на этой странице видимы всем» или «надо проверять каждую вручную».

Как это работает:

• Страница получает флаг all-visible, когда VACUUM убедился, что на ней нет «грязных» строк.

• При любом изменении строки (UPDATE, DELETE, INSERT) — флаг сбрасывается.

• После очередного VACUUM он снова может быть установлен.

На каждый 8kB блок таблицы приходится 1 бит в visibility map.

Таблица:     [Page1][Page2][Page3][Page4]...
Visibility:   0     1     1     0     ...

Если нужная страница имеет бит 1 — Postgres не пойдёт в heap, он доверится индексу.

Почему это важно для COUNT (col) и index-only scan

Представьте индекс:

CREATE INDEX idx_event_time ON t_events (event_time);

Когда мы делаем:

SELECT COUNT(event_time) FROM t_events;

Postgres сначала смотрит: «А могу ли я обойтись только этим индексом?» — то есть:

• Содержит ли он нужную колонку (event_time)?

• Видимы ли страницы heap‑а? ← проверяется через visibility map.

Если всё ок — работает index‑only scan. Это в 10–100× быстрее, чем чтение всей таблицы.

А вот COUNT (*) в пролёте

COUNT(*) — это общее число строк. Но чтобы гарантировать, что каждая строка жива, Postgres не может использовать обычный B‑tree индекс, потому что в нём нет всех колонок (включая xmin/xmax). Даже если есть индекс по id, COUNT(*) не сможет использовать его напрямую, потому что id — это не всё, что может повлиять на видимость строки. Да и COUNT(*) не опирается ни на какую конкретную колонку.

То есть:

• У COUNT(col) может быть индекс и шанс на index‑only;

• У COUNT(*) нет ни одной привязки к индексу → всегда full scan.

Что нужно для честного index-only scan

• Индекс на нужную колонку.

• col должен быть NOT NULL (иначе фильтрация).

• Visibility Map с битами all-visible.

• Желательно: колонка в INCLUDE() — чтобы использовать покрывающий индекс.

• Регулярный VACUUM (и autovacuum, и вручную по расписанию).

• Минимум UPDATE/DELETE по этой таблице.

Когда COUNT (*) всё-таки быстрее

Seq Scan против Index-only Scan

Прогоняем эксперимент:

-- Шаг 1. Таблица
CREATE TABLE t_events (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
    metadata JSONB
);

-- Шаг 2. Наполняем тестовыми данными
INSERT INTO t_events (user_id, metadata)
SELECT (random()*1000)::INT, '{}'::JSONB
FROM generate_series(1, 1000000);

Сценарий A: COUNT (*) — базовый full table scan

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM t_events;

Результат:

Seq Scan on t_events  (cost=0.00..14923.00 rows=1000000 width=0)
(actual time=0.098..213.402 rows=1 loops=1)
  Buffers: shared hit=8000

PostgreSQL прочитал все страницы таблицы (да, те самые 8kB‑страницы). На каждой — прошёл по строкам, проверяя видимость. Итог: 213 мс wall time, 8000 страниц в буферах.

Сценарий B: COUNT (col) по индексу — ускорение в десятки раз

Создадим индекс:

CREATE INDEX idx_event_time ON t_events (event_time);

Дальше:

VACUUM ANALYZE t_events;

Этот шаг заставит PostgreSQL обновить visibility map, и если на страницах нет «грязных» строк (тех, чья видимость спорная) — он сможет использовать index‑only scan.

Запрос:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(event_time) FROM t_events;

Результат:

Index Only Scan using idx_event_time on t_events
(cost=0.43..8234.00 rows=1000000 width=0)
(actual time=0.050..4.572 rows=1 loops=1)
  Index Cond: (event_time IS NOT NULL)
  Heap Fetches: 0
  Buffers: shared hit=300

Heap Fetches: 0 — Postgres вообще не трогал данные таблицы. Только индекс. shared hit=300 — индекс умещается в памяти. Время: ~~4.5 мс против 213 мс у COUNT(*).

И это — index‑only scan в идеальных условиях, когда:

• колонка NOT NULL;

• индекс по ней существует;

• visibility map чистая;

• таблица не мутировала недавно.

Почему COUNT (*) не может использовать индекс напрямую

Вот вы скажете: ну, а чего, я же добавил индекс по user_id, может, Postgres сможет использовать его для COUNT(*)? Увы, нет. Вот почему:

В B‑tree индексах нет данных о xmin/xmax. Поэтому он не может знать, валидна ли строка. Он указывает на ctid, но проверку делать всё равно надо — и придётся лезть в heap.

Postgres не может сказать: «там user_id точно есть, значит можно по нему считать». Потому что COUNT(*) требует, чтобы каждая строка, вне зависимости от колонок,  была учтена. И если даже есть индекс по одной колонке, Postgres не может утверждать, что строка вообще существует физически (например, её удалили, а вакуум не подчистил).

А INCLUDE? А покрывающие индексы?

CREATE INDEX idx_user_covering ON t_events (user_id) INCLUDE (id);
VACUUM t_events;

Индекс всё ещё B‑tree, но теперь содержит дополнительные колонки, не участвующие в сортировке. Можно использовать index‑only scan, если все запрашиваемые колонки покрыты.

Но даже с INCLUDE, COUNT(*) не применяет индекс, потому что:

1. У COUNT(*) нет колонок — он просто считает строки.

2. У Postgres нет механизма понять, что индекс охватывает всю таблицу.

3. Он по‑прежнему не может быть уверен, что строка «жива», если индекс не даёт xmin/xmax.

Можно заставить его через костыль (например, SELECT COUNT(user_id) с user_id NOT NULL и INCLUDE), но это уже COUNT(col), а не COUNT(*).

Когда COUNT (*) всё-таки выигрывает?

Бывают случаи:

• Маленькая таблица, умещающаяся в shared_buffers;

• Нет индексов (или они не подходят по запросу);

• Плохая visibility map (index‑only scan невозможен).

В таком случае, COUNT(*) может быть единственным правильным решением. Он не делает ничего лишнего: читает строки, проверяет MVCC, считает.

А вот если вы начнёте использовать COUNT(col) по колонке с кучей NULL — вы получите фильтрацию, снижение скорости, и результат будет отличаться.

Таблица замеров

Пару слов про COUNT (DISTINCT col)

COUNT(DISTINCT col) заслуживает отдельного уважения. Это не просто вариация COUNT(col) с фильтром, это уже агрегат с памятью: PostgreSQL обязан отслеживать уникальность значений, а значит — выделяет память под хэш‑таблицу или строит сортировку (в зависимости от плана). И это не просто медленнее, это другая нагрузка: CPU, RAM, temp‑файлы.

Простой пример:

SELECT COUNT(DISTINCT user_id) FROM t_events;

Postgres должен или:

• отсортировать все значения user_id, выбросить дубликаты, а потом посчитать;

• или (чаще) построить хэш‑таблицу в памяти —, но если данных много, она уйдёт во временные файлы, и вы получите сортировку/merge на диске.

План будет примерно таким:

Aggregate  (cost=...)
  ->  Index Only Scan using idx_user_id on t_events

index‑only scan может сработать, если user_id IS NOT NULL и всё хорошо с visibility map. Но даже при этом DISTINCT сам по себе требует больше работы — потому что он не просто считает, он помнит, что уже видел.

Так что COUNT(DISTINCT ...) — это не та же операция, но с припиской. Это вообще другая категория сложности. Использовать её бездумно в больших таблицах — прямой путь к temp‑файлам и кеш эвикшенам. Если нужны точные уникальные подсчёты по большим объёмам — подумайте о approx_count_distinct() из HyperLogLog или агрегатах из postgis_tiger_geocoder, если такое к вам применимо.

COUNT (*) — не панацея и не зло

• Он надёжен: считает всё, не ошибётся с NULL’ами.

• Он ленив: не хочет использовать индекс, даже если тот рядом.

• Он честен: всегда работает одинаково, без сюрпризов.

• Но он медленный, если таблица большая, и можно было бы обойтись index‑only scan.

Используйте COUNT(col) с NOT NULL, если есть подходящий индекс и таблица часто VACUUMится. И не бойтесь COUNT(*), если у вас OLAP и счёт идёт на секунды, а не миллисекунды.

Если вам близки темы оптимизации SQL-запросов и хочется выжать из базы максимум — приглашаем на открытый урок 22 апреля, где разберём, как автоматизировать рутину с помощью хранимых процедур в PostgreSQL и MS SQL Server. На практике покажем, как оформлять бизнес-логику в процедуры, управлять параметрами, повышать читаемость и безопасность кода — всё, что экономит нервы и процессорное время.

Записаться на урок можно на странице курса «SQL для разработчиков и аналитиков».