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

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

Тема кажется на первый взгляд банальной: зачем COUNT(col), если есть COUNT(*)? Но за ней скрывается куча нюансов: MVCC‑проверки, visibility map, index‑only scan, параллелизм… И на разных версиях PostgreSQL COUNT(*) может вести себя по‑разному. Разберём всё досконально.

Семантика по стандарту SQL

COUNT(*) считает все строки результирующего набора, независимо от содержимого колонок.

COUNT(expression) считает только те строки, где expression IS NOT NULL.

SELECT COUNT(*) FROM users;       -- считает все строки SELECT COUNT(user_id) FROM users; -- считает только не-NULL значения user_id

MVCC и visibility map

MVCC‑модель и поля xmin/xmax

PostgreSQL хранит все версии строк в куче. Каждая версия содержит:

• xmin: XID транзакции, которая создала tuple;

• xmax: XID транзакции, которая «удалила» или обновила его (логически).

При любом чтении движок проверяет, видна ли эта версия:

1. Активна ли транзакция с xmin?

2. Завершена ли транзакция с xmax?

3. Находится ли она в пределах «видимости» текущей транзакции?

Чтобы ответить «жива ли строка», PostgreSQL читает заголовок tuple и может обращаться к каталогу транзакций.

Visibility map

Проверка видимости для каждой строки — дорого. Чтобы избежать чтения heap при index‑only scan, есть специальная структура:

Visibility map: битовая карта, где каждый бит соответствует целой странице (8 kB) таблицы и показывает, что все строки на странице видимы ВСЕМ транзакциям.

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

• После VACUUM страница помечается all‑visible, если на ней нет «грязных» версий.

• Любые обновления/удаления/вставки по этой таблице сбрасывают бит у соответствующей страницы.

• Дальнейший VACUUM может вернуть бит.

Если бит=1, индексный скан доверяет visibility map и не ходит в heap.

Классический сценарий: COUNT(*) → Seq Scan

Нет «привязки» к какой‑либо колонке, планировщик не может выбрать индекс. Даже при наличии индекса по id нет данных о xmin/xmax, придётся проверять видимость через heap → Seq Scan.

-- Сценарий A: базовый full table scan для COUNT(*) 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

Время: ~213 ms. Страниц прочитано: 8000 (все страницы таблицы).

Когда COUNT(col) летает: index‑only scan

Если есть индекс по колонке col и:

1. col NOT NULL;

2. Индекс покрывает именно эту колонку;

3. Все страницы помечены all‑visible в visibility map;

то PostgreSQL может выполнить Index‑only Scan:

CREATE INDEX idx_event_time ON t_events (event_time); VACUUM ANALYZE t_events;  EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(event_time) FROM t_events;

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

Время: ~4.5 ms. Страниц (индекс): 300. Heap Fetches: 0

Выигрыш: ≈50× по сравнению с full table scan.

Counter‑intuitive: COUNT(*) через индекс

Parallel Index‑only Scan (PostgreSQL 12+)

Начиная с PostgreSQL 12 движок умеет:

• Планировать Parallel Index‑only Scan для COUNT(*),

• Если есть любой индекс по NOT NULL полю (даже без INCLUDE),

• Таблица давно не менялась (visibility map чиста),

• Параллелизм включён (max_parallel_workers_per_gather > 0).

Пример из комментариев (таблица tmp_test):

CREATE TABLE tmp_test (   num integer NULL,   val varchar );  -- Наполняем 1 000 000 строк: INSERT INTO tmp_test (num, val) SELECT CASE WHEN i%2=0 THEN NULL ELSE 12345 END,        repeat(G.i::text, 256) FROM generate_series(1, 1000000) G(i);  -- Создаём индекс по nullable-полю num: CREATE INDEX tmp_test_some_idx ON tmp_test(num);  -- Принудительный VACUUM для visibility map VACUUM tmp_test;  -- Считаем строки EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM tmp_test;

Finalize Aggregate  (cost=10966.90..10966.91 rows=1 width=8)   (actual time=29.826..32.236 rows=1 loops=1)   ->  Gather         Workers Launched: 3         ->  Partial Aggregate               ->  Parallel Index Only Scan using tmp_test_some_idx                  Heap Fetches: 0                  (actual time=0.127..17.083 rows=250000 loops=4)

Время: ~32 ms (вместо ~206 ms без параллелизма и index‑only).Heap Fetches: 0 — индекс покрывает всё, visibility map чист

Вывод: для COUNT(*) достаточно

1. индекса по NOT NULL-полю,

2. чистой visibility map,

3. параллелизма — и движок пойдёт по индексу.

Все четыре варианта

COUNT(DISTINCT) — совсем другое дело

COUNT(DISTINCT col) никак не оптимизируется индексом: движку придётся:

1. Собрать все значения col (индексный или обычный скан);

2. Отсортировать и сгруппировать или построить хэш‑таблицу в памяти;

3. При большом объёме — spill в temp‑файлы, merge‑сортировка на диске.

EXPLAIN ANALYZE SELECT COUNT(DISTINCT user_id) FROM t_events;

План обычно:

Aggregate   ->  Index Only Scan using idx_user_id on t_events

Но в Aggregate лежит сам DISTINCT, и он до или после сбора данных держит хэш/сортировку — нагрузка на CPU/RAM/Disk.

Альтернатива:

• approx_count_distinct(col) из модуля hyperloglog,

• Другие подобные агрегаты (PostGIS‑Tiger‑Geocoder тоже даёт агрегаты),

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

Итоговые рекомендации

Для OLTP‑сценариев выбирайте COUNT(col) по полю с NOT NULL вместе с соответствующим индексом и свежим VACUUM. Если всё‑таки нужен COUNT(*), убедитесь, что у вас есть индекс по любому NOT NULL полю и включён параллелизм, чтобы движок мог пойти в Index‑only Scan.

В OLAP‑нагрузках классический COUNT(*) через Seq Scan задействует все воркеры равномерно, но лучше фильтровать данные по дате date‐partitioning — так вы уменьшите объём сканируемой кучи. А для уникальных подсчётов вместо COUNT(DISTINCT) переходите на приближённые агрегаты типа HyperLogLog approx_count_distinct(), чтобы не вываливаться в temp‑файлы и не бить по памяти.

Не забывайте про автоматизацию: настройте autovacuum параметры vacuum_scale_factor, vacuum_freeze_table_age и регулярно мониторьте состояние visibility map в pg_visibility — это ключ к стабильной производительности ваших index‐only планов.

---

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

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